RE: high db hit ratio and a lot of waits on db sequential reads
- From: genegurevich@xxxxxxxxxxxx
- To: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- Date: Tue, 25 Sep 2007 17:48:28 -0500
Alvaro,
The reason we are looking at these waits is that they are the highest waits
in the databases and we are looking
at the ways to speed up the performance (or to say that the performance is
as good as it can get). Seems like
everyone who replied to my post is pointing to the deficineses in the SQL.
That's what we will be looking at next
Thank you for your thoughts
Gene Gurevich
"Alvaro Jose
Fernandez"
<alvaro.fernandez To
@sivsa.com> <genegurevich@xxxxxxxxxxxx>,
"oracle-l" <oracle-l@xxxxxxxxxxxxx>
09/25/2007 03:11 cc
PM
Subject
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
