RE: high db hit ratio and a lot of waits on db sequential reads
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <genegurevich@xxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Sep 2007 22:11:07 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
