DB File Sequential Reads and Data Block Waits

Hi All,

                                                                                       
             
                                                                                       
             
                                                                   I have been asked 
to diagnose    
                                                                   performance issues 
with a        
                                                                   database. The users 
are          
                                                                   experiencing 
problems while      
                                                                   executing a query. 
I tried to    
                                                                   have a look at the 
wait events.  
                                                                   There are lot of 
waits in        
                                                                   v$waitstat for 


'data block' (2   

                                                                   Million) and wait 
events in      
                                                                   v$system_event for 


'buffer busy  

                                                                   waits' (2 million) 
and mainly    
                                                                   'db file sequential 
reads' (9    
                                                                   million). 75% of 
the wait time   
                                                                   is for db file 
sequential reads. 
                                                                   Looking at 
v$session_wait, I     
                                                                   drilled down the 
sequential read 
                                                                   wait to a single 
table. I have   
                                                                   deduced this to be 
an I/O issue. 
                                                                   To confirm the 
same, I need to   
                                                                   put some doubts to 
rest. My      
                                                                   questions are:      
             
                                                                                       
             
                                                                   1. The "seconds in 
wait" in      
                                                                   v$session_wait is 
0, with the    
                                                                   state as waiting, 
for the wait   
                                                                   events. Is it 0 
because          
                                                                   timed_statistics is 
set to       
                                                                   FALSE? Or is it 
difficult to     
                                                                   catch one with a 
non zero time?  
                                                                   Or is the wait too 
small and can 
                                                                   be ignored?         
             
                                                                   2. The SID number 
remains the    
                                                                   same, but the seq# 
keeps on      
                                                                   updating as the 
wait commences   
                                                                   for another block. 
What is the   
                                                                   seq#? when does it 
get           
                                                                   incremented? With 
each wait???   
                                                                   3. Are 'data block' 
waits in     
                                                                   v$waitstat, and 'db 
file         
                                                                   sequential reads' 
in             
                                                                   v$session_wait 
mutually          
                                                                   exclusive? Could 
one wait event  
                                                                   also cause the 
other wait?       
                                                                   4. The data block 
waits and the  
                                                                   buffer busy waits 
are related,   
                                                                   right? I mean, a 


'data block'    

                                                                   wait could also 
bump up the      
                                                                   'buffer busy wait', 
right?       
                                                                   5. Since the users 
say that they 
                                                                   are only 
experiencing            
                                                                   performance issues 
with a select 
                                                                   statement and not 
an             
                                                                   insert/update, am I 
right in     
                                                                   assuming that the 


'data block'   

                                                                   wait is not because 
of           
                                                                   contention for free 
lists. But   
                                                                   more than one 
session trying to  
                                                                   read the same data 
block. If     
                                                                   indeed so, would 
striping the    
                                                                   table along 
datafiles on         
                                                                   different drives 
help the cause? 
                                                                   6. My assumptions 
from what I    
                                                                   have gathered so 
far, also tells 
                                                                   me this is an I/O 
issue. To      
                                                                   further confirm, 
the six tables  
                                                                   all accessed in the 
query with a 
                                                                   million row each 
all reside on   
                                                                   the same disk. And 
v$filestat    
                                                                   shows a lot of 
physical reads    
                                                                   and writes on the 
concerned      
                                                                   datafiles, compared 
to the other 
                                                                   datafiles.          
             
                                                                                       
             
                                                                                       
             

Am I right?

Thanks
Raj

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).