Re: high db hit ratio and a lot of waits on db sequential reads

   Hi,
Being signed for BAAG, I'm not in position to provide Any Guesses ;-). However (IMHO) there is still a room for investigation hypothesis.
I'd look for the "slow-by-slow" (Tom Kyte), which is row by row access. Usually this happens when the coder avoids the joins in the SQL and implements the same functionality in PL/SQL. Say by fetching a rows in a main cursor loop and then fetching the rows from the other table a row by row by its PK, for example. As a result you may have the worst possible "plan" (usually) with a lot of throwaway rows, which is masked by PL/SQL. In the usual situation you'll see just a bad SQL, thus there would be no question where those extra LIO comes from.
Look for the PL/SQL procedure with a lot of LIO, then check its code for the presence of SQL with the access to the single row in the loop. Well, I presume that you've meant something like UNIQUE access path and NOT the RANGE SCAN etc on your PK. 

-- Andrey

genegurevich@xxxxxxxxxxxx пишет:

Hi all:

I am working on tuning an app running against oracle 10.2.0.3 We have 48G
on the server; my db_cache is 18G. When I
look at the awr reports, I see db hit ratio being over 99% and a lot of
waits for db sequential reads. Based on the SQL
there are a lot of table reads based on the primary keys so that kind of
waits is reasonable. But the question is
if the hit ratio is that high , if we read mostly for the cache, why do we
do that many reads. Is there an explanation for that?

thank you

Gene Gurevich


--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Other related posts: