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_at_discover.com пишет:
> 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