通过top 5等待事件查看sql语句

设计的动态性能视图有：v$session_event,v$session,v$sqlarea，首先在v$session_event中可以找到event，然后通过其动态性能视图找到sid，可以在v$session中找到相应的sql_id,然后再通过sql_id在v$sqlarea中找到sql_text

 查看所有的事件的sql语句

select distinct s.sql_id,s.sid,se.event,se.TOTAL_WAITS,sa.sql_text
from v$session s  left join v$session_event se 
on  s.sid=se.sid
left join v$sqlarea sa
on s.sql_id=sa.sql_id
where sa.SQL_TEXT is not null
order by se.total_waits desc;

对单独的event事件的语句的查询

select s.sql_id,se.event,sa.sql_text
from v$session s, v$session_event se,v$sqlarea sa
where s.sid=se.sid and s.sql_id=sa.sql_id
and se.event=&event;

SQL> select s.sql_id,se.event,sa.sql_text
from v$session s, v$session_event se,v$sqlarea sa
where s.sid=se.sid and s.sql_id=sa.sql_id
and se.event=&event;  2    3    4 
Enter value for event: 'db file sequential read'
old   4: and se.event=&event
new   4: and se.event='db file sequential read'

SQL_ID       EVENT
------------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
65vuzhm491wk9 db file sequential read
    DECLARE  reason_id    dbms_server_alert.REASON_ID_T := NULL;   resour
ce_id  NUMBER;     db_name  recent_resource_incarnations$.db_unique_name%TY
PE :=   :db_unique_name;      inst_name    recent_resource_incar
nations$.instance_name%TYPE :=    :instance_name;      event_id
   NUMBER := :event_id;      event_time   TIMESTAMP WITH TIME ZONE :=
      TO_TIMESTAMP_TZ(:event_time,        'YYY
Y-MM-DD HH24:MI:SS.FF TZH:TZM',       'NLS_CALENDAR=
''Gregorian''');    BEGIN      CASE :reason_name WHEN 'DATABASE_UP' THEN

SQL_ID       EVENT
------------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
  reason_id := dbms_server_alert.RSN_FAN_DATABASE_UP;     WHEN 'DATABA
SE_DOWN' THEN        reason_id := dbms_server_alert.RSN_FAN_DATABASE_DOWN;
    WHEN 'INSTANCE_UP' THEN       reason_id := dbms_server_alert.RSN_FAN_INS
TANCE_UP;   WHEN 'INSTANCE_DOWN' THEN      reason_id := dbms_server_al
ert.RSN_FAN_INSTANCE_DOWN;   WHEN '

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27013009/viewspace-1219666/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27013009/viewspace-1219666/