Oracle waiting ---

Oracle Waiting currently ......

Oracle Waits

select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , username, blocking_session,seconds_in_wait, wait_time ,event
from gv$session s 
where state = 'WAITING'
and wait_class != 'Idle';



set linesize 300 pagesize 300 
col SQL_TEXT for a70 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , s.sql_id ,s.prev_sql_id,sql_text from gv$session s, gv$sql q
where sid = &sid
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id )
and q.inst_id=s.inst_id;


select wait_class_id, wait_class,total_waits, time_waited from gv$session_wait_class where sid = &sid;



WAIT_CLASS_ID WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
------------- ---------------------------------------------------------------- ----------- -----------
1893977003 Other                                                                     31        7022
3875070507 Concurrency                                                               19          14
3386400367 Commit                                                                     2           0
2723168908 Idle                                                                      36        3300
2000153315 Network                                                                   36           0
1740759767 User I/O                                                               16344        1074

6 rows selected.


select event, total_waits, time_waited from v$system_event e, v$event_name n
where n.event_id = e.event_id
and e.wait_class_id = &wait_class_id ;


EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
os thread startup                                                      12601      148864
latch: cache buffers chains                                            65878       55103
buffer busy waits                                                       2393        1509
enq: TX - index contention                                                 8         458
latch: In memory undo latch                                                3           2
latch: row cache objects                                                 515         340
row cache lock                                                             4          45
cursor: mutex X                                                            1           0
cursor: pin S                                                            110         230
cursor: pin S wait on X                                                   13          22
latch: shared pool                                                      1219        7622
library cache lock                                                         5         227
library cache load lock                                                   17         260
library cache: mutex X                                                    82         301
resmgr:internal state change                                              54         734

15 rows selected.