session wait for session
select SID, event, p1, p2 from v$session_wait;
Top Wait Event
vi wait_event_counting.sqlset line 300
set pagesize 200
SELECT event,
total_waits,
time_waited
FROM v$system_event e,
v$event_name n
WHERE n.event_id = e.event_id
AND n.wait_class !='Idle'
AND n.wait_class =
(SELECT wait_class
FROM v$session_wait_class
WHERE wait_class !='Idle'
GROUP BY wait_class
HAVING SUM(time_waited) =
(SELECT MAX(SUM(time_waited) )
FROM v$session_wait_class
WHERE wait_class !='Idle'
GROUP BY (wait_class)
)
)
ORDER BY 3 DESC;
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
Failed Logon Delay 6 600
oracle thread bootstrap 147 502
control file heartbeat 1 400
os thread creation 147 225
ADR block file write 791 43
LGWR wait for redo copy 17 23
SGA: allocation forcing component growth 23 11
rdbms ipc reply 15 10
ADR block file read 452 8
enq: CU - contention 2 5
enq: CF - contention 2 3
enq: PR - contention 4 2
reliable message 2 1
AQ Background Master: slave start 2 1
ADR file lock 636 1
enq: PV - syncstart 1 1
latch: cache buffers lru chain 1 1
AQPC: new master 2 0
CSS initialization 1 0
BFILE get path object 2 0
instance state change 2 0
Redo Transport Open 1 0
KSV master wait 1 0
asynch descriptor resize 262 0
get branch/thread/sequence enqueue 3 0
25 rows selected.
Showing the Waiting
vi waiting_event_count.sql
select count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM v$session_wait GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/
Showing the Waiting without background processes
vi waiting_event_count_without_BP.sq
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session
WHERE
type = 'USER'
AND status = 'ACTIVE'
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/
