Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: high db hit ratio and a lot of waits on db sequential reads

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

From: Bobak, Mark <>
Date: Tue, 25 Sep 2007 16:17:02 -0400
Message-ID: <>

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 but unfortunately, is not currently, cause they just suffered a nasty crash and are still piecing things back together.

Hope that helps,



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

ProQuest...Start here.

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

From: [] On Behalf Of 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 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


-- Received on Tue Sep 25 2007 - 15:17:02 CDT

Original text of this message