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

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_at_usa.                                             
             net>                                                       To 
             Sent by:                  "oracle-l" <oracle-l_at_freelists.org> 
             oracle-l-bounce_at_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_at_usa.n                                             
                    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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

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






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