SQL> SELECT s.inst_id, 2 NVL(s.username, '(oracle)') AS username, 3 s.sid, 4 s.serial#, 5 sw.event, 6 sw.wait_class, 7 sw.wait_time, 8 sw.seconds_in_wait, 9 sw.state 10 FROM gv$session_wait sw, 11 gv$session s 12 WHERE s.sid = sw.sid 13 AND s.inst_id = sw.inst_id 14 ORDER BY sw.seconds_in_wait DESC; INST_ID USERNAME SID SERIAL# EVENT WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE ---------- -------------------- ---------- ---------- ------------------------------ --------------- ---------- --------------- ------------------- 2 (oracle) 778 1 ASM background timer Idle 0 1267666 WAITING 2 (oracle) 776 3 class slave wait Idle 0 1267666 WAITING 2 (oracle) 769 2 Streams AQ: waiting for time m Idle 0 1267648 WAITING anagement or cleanup tasks 1 (oracle) 779 7 ASM background timer Idle 0 568095 WAITING 2 (oracle) 799 1 DIAG idle wait Idle 0 273024 WAITING 1 (oracle) 799 5 DIAG idle wait Idle 0 272997 WAITING 1 DVBDEVELOP 727 62174 db file sequential read User I/O 0 136416 WAITING 1 DWH 756 18094 read by other session User I/O 0 132226 WAITING 1 DWH 285 42985 read by other session User I/O 0 132170 WAITING 1 REPORT 274 21657 read by other session User I/O 0 68995 WAITING 1 BKP 314 3339 read by other session User I/O 0 53178 WAITING 1 (oracle) 774 51246 class slave wait Idle 0 50929 WAITING 1 DVBDEVELOP 686 24620 read by other session User I/O 0 50072 WAITING 1 (oracle) 777 1 rdbms ipc message Idle 0 18976 WAITING 1 RM 772 38745 read by other session User I/O 0 18346 WAITING 2 (oracle) 766 8 Streams AQ: qmn slave idle wai Idle 0 16837 WAITING t 1 RRS 416 63345 read by other session User I/O 0 15818 WAITING 1 (oracle) 800 1 pmon timer Idle 0 9919 WAITING 2 (oracle) 793 1 rdbms ipc message Idle 0 4050 WAITING 2 (oracle) 785 1 rdbms ipc message Idle 0 2130 WAITING 1 (oracle) 793 1 rdbms ipc message Idle 0 1931 WAITING 1 (oracle) 796 1 ges remote message Idle 0 592 WAITING 2 (oracle) 794 1 gcs remote message Idle 0 423 WAITING 2 (oracle) 795 1 gcs remote message Idle 0 423 WAITING 1 (oracle) 794 4 gcs remote message Idle 0 422 WAITING 1 (oracle) 795 3 gcs remote message Idle 0 422 WAITING 2 (oracle) 800 1 pmon timer Idle 0 411 WAITING 1 (oracle) 785 3 rdbms ipc message Idle 0 238 WAITING 1 (oracle) 798 7 rdbms ipc message Idle 0 238 WAITING 1 (oracle) 673 59220 Streams AQ: qmn slave idle wai Idle 0 238 WAITING t 2 (oracle) 796 1 ges remote message Idle 0 203 WAITING 2 REPORT 730 5217 SQL*Net message from client Idle 0 171 WAITING 2 (oracle) 777 1 rdbms ipc message Idle 0 168 WAITING 1 (oracle) 788 1 rdbms ipc message Idle 0 160 WAITING 1 DBMONITOR 597 35825 SQL*Net message from client Idle 0 151 WAITING 1 (oracle) 565 16660 jobq slave wait Idle 0 129 WAITING 2 (oracle) 788 1 rdbms ipc message Idle 0 111 WAITING 1 (oracle) 755 31 rdbms ipc message Idle 0 109 WAITING 2 SYS 775 5 SQL*Net message from client Idle 0 51 WAITING 1 SYS 663 72 SQL*Net message from client Idle 0 46 WAITING 1 (oracle) 789 1 smon timer Idle 0 43 WAITING 2 (oracle) 798 1 rdbms ipc message Idle 0 42 WAITING 2 (oracle) 787 1 rdbms ipc message Idle 0 42 WAITING 2 (oracle) 756 21842 jobq slave wait Idle 0 42 WAITING 2 (oracle) 774 10 rdbms ipc message Idle 0 42 WAITING 2 DVBDEVELOP 742 35642 SQL*Net message from client Idle 0 40 WAITING 2 DVBDEVELOP 721 57086 SQL*Net message from client Idle 0 40 WAITING 1 (oracle) 735 41 Streams AQ: waiting for time m Idle 0 25 WAITING anagement or cleanup tasks 2 (oracle) 789 1 smon timer Idle 0 22 WAITING 1 (oracle) 787 1 rdbms ipc message Idle 0 21 WAITING 1 REPORT 612 45291 SQL*Net message from client Idle 0 21 WAITING 1 (oracle) 747 23 Streams AQ: qmn coordinator id Idle 0 20 WAITING le wait 2 (oracle) 792 1 rdbms ipc message Idle 0 18 WAITING 2 (oracle) 770 1 Streams AQ: qmn coordinator id Idle 0 12 WAITING le wait 1 (oracle) 770 64 rdbms ipc message Idle 0 10 WAITING 2 (oracle) 773 1 rdbms ipc message Idle 0 9 WAITING 1 SYS 752 11 Streams AQ: waiting for messag Idle 0 7 WAITING es in the queue 2 RMOAMN 720 17112 SQL*Net message from client Idle 0 6 WAITING 1 (oracle) 790 1 rdbms ipc message Idle 0 3 WAITING 2 (oracle) 786 1 rdbms ipc message Idle 0 3 WAITING 1 (oracle) 792 1 rdbms ipc message Idle 0 2 WAITING 1 SYS 678 63 SQL*Net message from client Idle 0 1 WAITING 1 (oracle) 797 1 rdbms ipc message Idle 0 1 WAITING 1 (oracle) 786 1 rdbms ipc message Idle 0 1 WAITING 1 SYS 356 35540 PX Deq: Execute Reply Idle 0 0 WAITING 2 (oracle) 797 1 rdbms ipc message Idle 0 0 WAITING 1 (oracle) 783 42 rdbms ipc message Idle 0 0 WAITING 1 (oracle) 791 1 rdbms ipc message Idle 0 0 WAITING 2 REPORT 716 6511 cr request retry Other 0 0 WAITING 2 REPORT 717 40792 cr request retry Other 0 0 WAITING 2 CMC_SAMBANDH 737 43274 cr request retry Other 0 0 WAITING 2 DVBDEVELOP 738 9685 cr request retry Other 0 0 WAITING 2 SYS 744 23756 PX Deq: reap credit Other -1 0 WAITED SHORT TIME 2 SYS 765 4 SQL*Net message from client Idle 0 0 WAITING 2 REPORT 767 25313 cr request retry Other 0 0 WAITING 2 (oracle) 781 1 rdbms ipc message Idle 0 0 WAITING 2 (oracle) 790 1 rdbms ipc message Idle 0 0 WAITING 2 (oracle) 791 1 rdbms ipc message Idle 0 0 WAITING 1 SYS 466 58922 PX Deq: Execution Msg Idle 2 0 WAITED KNOWN TIME 79 rows selected. SQL> !date Sun Dec 5 13:16:36 IST 2010 ============== SQL> select event, time_waited as time_spent 2 from v$session_event 3 where sid = &sid 4 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', 'WMON goes to sleep') 5 union all 6 select b.name, a.value 7 from v$sesstat a, v$statname b 8 where a.statistic# = b.statistic# 9 and b.name = 'CPU used when call started' 10 and a.sid = &sid; Enter value for sid: 274 old 3: where sid = &sid new 3: where sid = 274 Enter value for sid: 274 old 10: and a.sid = &sid new 10: and a.sid = 274 EVENT TIME_SPENT ------------------------------ ---------- read by other session 6794853 log file sync 0 db file sequential read 263 db file scattered read 2634 db file parallel read 336 gc cr multi block request 3261 gc cr block 2-way 2 gc current block 2-way 65 gc cr grant 2-way 10 library cache lock 0 SQL*Net message to client 0 SQL*Net more data from client 0 CPU used when call started 1