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

Hi Gene,

>..Based on the SQL
>there are a lot of table reads based on the primary keys so that kind
of
>waits is reasonable.

imho, not necesarily reasonable, unless the pk index reads are confirmed to be the best access path possible. I've seen circumstances where the optimizer "gives up" (or maybe due to bad parameter settings, statistics, or bugs), and choose a non optimal plan based on enormous pk's index range scans, for example.

>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?

I'm having since years situations like yours, working with OLTP databases, having good hit ratios, but dbfile sequential reads the most prominent wait events. But (in my case), I don't worry very much unless the elapsed individual query time , and/or the individual query logical (or physical reads) exceeds certain limits. Hit ratios for the buffer cache are a bit misleading, since even if you have a well-sized cache, from time to time the table and index blocks are periodically unpinned or aged/flushed out - while at the same time maintaining a good ratio

I recommend to read Julian Dyke's website, and particularly one presentation
(http://julian.dyke.users.btopenworld.com/com/Presentations/Presentation s.html#LogicalIO), which is great to undeestand some buffer cachoe operations, and other issues related.
regards,

alvaro

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