1.创建测试用表T

SQL> create table t as select * from dba_objects;
                                                 
Table created.                                   

2.查看T表的Block号和Block数量

SQL> select file_id,block_id,blocks from dba_extents where segment_name='T';
                                                                            
   FILE_ID   BLOCK_ID     BLOCKS                                            
---------- ---------- ----------                                            
         3       5025          8                                            
         3       5033          8                                            
         3       5041          8                                            
         3       5049          8                                            
         3       5057          8                                            
         3       5065          8                                            
         3       5073          8                                            
         3       5081          8                                            
         3       5089          8                                            
         3       5097          8                                            
                                                                            
10 rows selected.                                                           

3.开启10046 event trace跟踪当前session

SQL> alter session set events '10046 trace name context forever,level 12';
                                                                          
Session altered.                                                          

4.执行全表扫描

SQL> select count(*) from t; 
                             
  COUNT(*)                   
----------                   
      6279                   

5.结束10046 event trace

SQL> alter session set events '10046 trace name context off';
                                                             
Session altered.                                             

6.查看跟踪文件路径

SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||  
  2         p.spid || '.trc' trace_file_name                                  
  3    from (select p.spid                                                    
  4            from v$mystat m, v$session s, v$process p                      
  5           where m.statistic# = 1                                          
  6             and s.sid = m.sid                                             
  7             and p.addr = s.paddr) p,                                      
  8         (select t.instance                                                
  9            from v$thread t, v$parameter v                                 
 10           where v.name = 'thread'                                         
 11             and (v.value = 0 or t.thread# = to_number(v.value))) i,       
 12         (select value from v$parameter where name = 'user_dump_dest') d   
 13  /                                                                        
                                                                              
TRACE_FILE_NAME                                                               
------------------------------------------------------------------------------
/disk3/oradata/admin/ora7a/udump/ora7a_ora_6128.trc                           

7.查看跟踪文件

SQL> ! grep scatt /disk3/oradata/admin/ora7a/udump/ora7a_ora_6128.trc      
WAIT #1: nam='db file scattered read' ela= 311 p1=3 p2=5026 p3=7           
WAIT #1: nam='db file scattered read' ela= 211 p1=3 p2=5033 p3=8           
WAIT #1: nam='db file scattered read' ela= 302 p1=3 p2=5041 p3=8           
WAIT #1: nam='db file scattered read' ela= 226 p1=3 p2=5049 p3=8           
WAIT #1: nam='db file scattered read' ela= 219 p1=3 p2=5057 p3=8           
WAIT #1: nam='db file scattered read' ela= 300 p1=3 p2=5065 p3=8           
WAIT #1: nam='db file scattered read' ela= 501 p1=3 p2=5073 p3=8           
WAIT #1: nam='db file scattered read' ela= 466 p1=3 p2=5081 p3=8           
WAIT #1: nam='db file scattered read' ela= 470 p1=3 p2=5089 p3=8           
WAIT #1: nam='db file scattered read' ela= 139 p1=3 p2=5097 p3=6           

8.跟踪文件中P1,P2,P3的字段说明

SQL> select name, parameter1 p1, parameter2 p2, parameter3 p3              
  2    from v$event_name                                                   
  3   where name = 'db file scattered read'                                
  4  /                                                                     
                                                                           
NAME                           P1         P2         P3                    
------------------------------ ---------- ---------- ----------            
db file scattered read         file#      block#     blocks                
Trackback

no comment untill now

Add your comment now

切换到手机版