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

From: <genegurevich_at_discover.com>
Date: Tue, 25 Sep 2007 17:45:59 -0500
Message-ID: <OFAAC844CC.680C2B42-ON86257361.007CF19B-86257361.007D0EEB@discover.com>

Mark,

The average wait time is between 8 and 11 ms according to oracle's AWR reports taken today. I'll be looking
at the SQL as our next step.

Appreciate your thoughts

thank you

Gene Gurevich

                                                                           
             "Bobak, Mark"                                                 
             <Mark.Bobak_at_il.pr                                             
             oquest.com>                                                To 
             Sent by:                  <genegurevich_at_discover.com>,        
             oracle-l-bounce_at_f         "oracle-l" <oracle-l_at_freelists.org> 
             reelists.org                                               cc 
                                                                           
                                                                   Subject 
             09/25/2007 03:17          RE: high db hit ratio and a lot of  
             PM                        waits on db sequential reads        
                                                                           
                                                                           
             Please respond to                                             
             Mark.Bobak_at_il.pro                                             
                 quest.com

Hi Gene,

My first guess, based on very little information, is that you have an application that has lots of inefficient SQL. That is, the application's SQL could be returning the results with a lot fewer logical I/Os. Since you're doing so many logical I/Os, some of those are bound to turn into physical I/Os. Those are causing the 'db file sequential read' waits. (I wonder, what's your average wait time on a db file sequential read event? With 48GB on the server, and with an 18GB buffer cache, I wonder if you're servicing a lot of those physical reads from the filesystem cache? Assuming you haven't enabled DIO.) So, what I'm saying is, you're doing tons of I/O, and most of it is logical, but, you're doing so much, that even the (relatively) small amount that turns into physical I/O is enough to dominate your response time profile with db file sequential read events.

Cary Millsap says it much better than I can, so, you should definitely read:

"Why a 99%+ Database Buffer Cache Hit Ratio is Not OK" Which is usually available from http://www.hotsos.com/ but unfortunately, is not currently, cause they just suffered a nasty crash and are still piecing things back together.

Hope that helps,

-Mark

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak_at_il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Tuesday, September 25, 2007 2:57 PM To: oracle-l
Subject: high db hit ratio and a lot of waits on db sequential reads

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 Received on Tue Sep 25 2007 - 17:45:59 CDT