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 -- //www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
