通过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 '
