Oracle OWI等待事件视图(v$session_wait/v$session_event/v$system_event)(五)
pts/custom/sql> more wait_event_by_sid.sql
SELECT event, time_waited AS time_spent
FROM v$session_event
WHERE sid = &sid
AND wait_class<>'Idle'
AND event NOT IN
('Null event',
'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal',
'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer',
'Wait for credit - free buffer',
'PX Deq Credit: free buffer',
'parallel query dequeue wait',
'PX Deque wait',
'Parallel Query Idle Wait - Slaves',
'PX Idle Wait',
'slave wait',
'dispatcher timer',
'virtual circuit status',
'pipe get',
'rdbms ipc message',
'rdbms ipc reply',
'pmon timer',
'smon timer',
'PL/SQL lock timer',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'SQL*Net more data to client',
'rdbms ipc message',
'WMON goes to sleep')
UNION ALL
SELECT b.name, a.VALUE
FROM v$sesstat a, v$statname b
WHERE a.statisti
c# = b.statistic# AND b.name = 'CPU used when call started' AND a.sid = &sid;
b、wait_event_inst_lvl.sql
robin@SZDB:~/dba_scripts/custom/sql> more wait_event_inst_lvl.sql
SET LINES 160
SET NUMWIDTH 18
COL class FOR a15
COL event FOR a30
COL total_waits FOR 999,999,999
COL total_timeouts FOR 999,999,999
COL time_waited FOR 999,999,999,999
COL average_wait FOR 999,999,999,999
COL event FORMAT a30 WRAP
COL wait_class FORMAT a20
SELECT b.wait_class,
a.event,
total_waits,
total_timeouts,
time_waited,
average_wait,
c.startup_time
FROM v$system_event a, v$event_name b, v$instance c
WHERE a.event = b.name
AND UPPER (a.event) LIKE
DECODE (UPPER ('&input_event_name'),
'ALL', UPPER (a.event),
UPPER ('%&input_event_name%'))
AND b.wait_class<>'Idle'
AND a.event NOT IN
('Null event',
'client message',
'KXFX: Execution Message Dequeue - Slave',
'PX Deq: Execution Msg',
'KXFQ: kxfqdeq - normal deqeue',
'PX Deq: Table Q Normal',
'Wait for credit - send blocked',
'PX Deq Credit: send blkd',
'Wait for credit - need buffer to send',
'PX Deq Credit: need buffer',
'Wait for credit - free buffer',
'PX Deq Credit: free buffer',
'parallel query dequeue wait',
'PX Deque wait',
'Parallel Query Idle Wait - Slaves',
'PX Idle Wait',
'slave wait',
'dispatcher timer',
'virtual circuit status',
'pipe get',
'rdbms ipc message',
'rdbms ipc reply',
'pmon timer',
'smon timer',
'PL/SQL lock timer',
'SQL*Net m