使用OWI诊断Oracle性能问题

oj3w63p57c

　　1.查看常见的等待事件
select *
  from v$system_event
 where event in ('buffer busy waits', 'db file sequential read',
        'db file scattered read', 'enqueue', 'free buffer waits',
        'latch free', 'log file parallel write', 'log file sync')
       
       
       
2.查看具有对以上的内容有贡献的等待事件的--会话
select s.USERNAME,s.PROGRAM,s.STATUS,se.EVENT,se.TOTAL_WAITS,se.TOTAL_TIMEOUTS,se.TIME_WAITED,se.AVERAGE_WAIT
  from v$session s, v$session_event se
 where s.sid = se.SID
   and se.EVENT not like 'SQL*Net%'
   and s.STATUS = 'ACTIVE'
   and s.USERNAME is not null;   
  
  
  
 3.查看一个会话的等待最多的事件是什么，（是动态的，需多次执行！）
 select sw.sid,
       s.USERNAME,
       sw.EVENT,
       sw.WAIT_TIME,
       sw.STATE,
       sw.SECONDS_IN_WAIT sec_in_wait
  from v$session s, v$session_wait sw
 where s.USERNAME is not null
   and sw.sid = s.SID
   and sw.EVENT not like '%SQL*Net%'
 order by sw.WAIT_TIME desc   
 
 

 4.根据以上过滤出多次出现的会话，来查询等待事件的其他信息。
 select sid, event, p1text, p1, p2text, p2, p3text, p3
  from v$session_wait
 where sid between 1 and 300
   and event not like '%SQL%'
   and event not like '%rdbms%'
　　注意P1(是数据文件还是锁存器)决定P2和P3的内容
   
   
   
   
   5.查询以上的表。
 select owner, segment_name, segment_type, tablespace_name
  from dba_extents
 where file_id = &FiledID_In
   and &blockId_in between block_id and block_id + blocks - 1 
  
  
  
  
6.获取SQL文本。  
create or replace function GetSqlText(HashAddr_in in v$sqltext.HASH_VALUE%type,
                                      Addr_in in v$sqltext.ADDRESS%type)
  return Varchar2
is
   Temp_SqlTxt varchar2(32767);
   cursor SqlPiece_Cur
   is
      select PIECE,SQL_TEXT from v$sqltext where HASH_VALUE=HashAddr_in and ADDRESS=Addr_in
      order by piece  ;
   begin
     for SqlPiece_rec in  sqlPiece_Cur
     loop
         Temp_SqlTxt := Temp_SqlTxt || SqlPiece_rec.sql_text ;
     end loop;
     return Temp_SqlTxt;
end  GetSqlText;
　　
select sid, getsqltext(sql_hash_value, sql_address) sql_text
  from v$session
 where sid = &sid_in