Re: high db hit ratio and a lot of waits on db sequential reads
- From: genegurevich@xxxxxxxxxxxx
- To: terrysutton@xxxxxxx
- Date: Fri, 28 Sep 2007 14:53:10 -0500
Terry,
Looking at one of the AWR reports, db file seq reads contributed 44% of
total wait time - 20mil+ waits, 93000 seconds
We are currently looking at the SQL and trying to see if it can be tuned.
The SQL does a lot of table accesses
via a Primary key followed by the table read as I understand.
thank you
Gene Gurevich
"Terry Sutton"
<terrysutton@usa.
net> To
Sent by: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
oracle-l-bounce@f cc
reelists.org
Subject
Re: high db hit ratio and a lot of
09/27/2007 10:34 waits on db sequential reads
AM
Please respond to
terrysutton@xxxxx
et
Gene,
You haven't really given us any meaningful information. What is "a lot" of
db file sequential read waits? How much time is spent on these waits?
Just
because that's your biggest wait event (most waits or most time?) doesn't
mean there's a problem. Give us a bit more data.
If your CHR is 99.5% (for instance), that means that .5% of your logical
reads have associated physical reads. That .5% is what's causing your
sequential read waits. If that .5% is too high, then maybe the 100% is too
high. I.e., you're doing too many logical reads. As others have said,
check your SQL. Find out what queries are doing the most LOGICAL reads and
why. If you reduce the logical reads you'll probably reduce the physical
reads. Your CHR may go down, but CHR is a meaningless number anyway.
--Terry
>
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
