-- 使用下面查询找到与所连接的会话有关的当前等待事件。这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。
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.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
-- 查询会话等待事件的详细信息
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
-- 利用P1、P2的信息，找出等待事件的相关的段
select owner,segment_name,segment_type,tablespace_name
from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks - 1;
-- 获得操作该段的sql语句：
select sid, getsqltxt(sql_hash_value,sql_address)
from v$session
where sid = &sid_in;
-- getsqltxt函数
create or replace
function GetSQLtxt (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 GetSQLtxt;
-- 至此已经找到影响性能的对象和sql语句，可以有针对性地优化