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

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_at_discover.com>,        
                                       "oracle-l" <oracle-l_at_freelists.org> 
             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

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