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

From: <genegurevich_at_discover.com>
Date: Fri, 28 Sep 2007 14:49:10 -0500
Message-ID: <OF9A9AB571.A4AD38E8-ON86257364.006C509A-86257364.006CDF10@discover.com>

Hi, Fred,

I am trying to find out whether there is anything that I can do to reduce the number of the waits. The average time per wait varies befween 8 and 11ms according to Oracle's AWR. I see 1.6mil LIO per sec and about 11 thousand
PIO per sec

thank you

Gene Gurevich

                                                                           
             fmhabash                                                      
             <fmhabash_at_gmail.c                                             
             om>                                                        To 
                                       Mark.Bobak_at_il.proquest.com          
             09/26/2007 11:12                                           cc 
             AM                        genegurevich_at_discover.com, oracle-l 
                                       <oracle-l_at_freelists.org>            
                                                                   Subject 
                                       Re: high db hit ratio and a lot of  
                                       waits on db sequential reads

For my edification, can you please provide these metrics ...
- LIO/sec

PIO/sec
if you look at your sql ordered by PIO, what do you see there. Can you tell from the code what tables are being read from disk and why?

Also, on a finely tuned DB and in the absence of any other significant wait events (e.g. log sync, scattered ...., etc), why wouldn't you expect to see DBFSR as the top one. After all ,that's what the DB should be doing serving user requests. The question, then, becomes how costly it is. If you are doing less than 20ms, then you have no reason to loose sleep over it (not that you are).

Thank you

Fred Habash, Lead Oracle DBA (OCP 8i,9i) Oracle Team Portal http://spc-00sv0d03e3.spc.supc.com/ddcports/oracle/

ACS HealthCare Solutions Dearborn, MI | Off.: 248-226-8778 | fhabash@acs-hcs.com | http://www.acs-hcs.com

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

Received on Fri Sep 28 2007 - 14:49:10 CDT