Sunday, November 4, 2012

V$Session_Wait VS V$Session_event

Both of these tables are view In database , I will talk about what is the difference between them and Why some of record appear in V$Session_wait but not in V$session_event , Simple Topic But useful and good to know , Also Check the document that i post For Oracle that gave you structure for both of these view :

V$SESSION_WAIT 
displays the current or last wait for each session.

V$SESSION_EVENT
lists information on waits for an event by a session

This is simulation for both table appear what is the common between two view :

Conn SPP/SPP
 
select SID, EVENT from v$session_wait where event='DIAG idle wait';
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
 
select SID,EVENT from v$session_event where event ='DIAG idle wait' ;
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
Also its good to know when you are using 10g or later you can use v$seesion which is  gives you real-time information, what is happening right now.


gives you real-time information, what is happening right now :


there's different type of enqueue:wait in Oracle like the following :


    enq: TX - allocate ITL entry
 
    enq: TX - contention
 
    enq: TX - index contention
 
    enq: TX - row lock contention 
 

to check them you can query V$EVENT_NAME view provides a 
complete list of all the enq: wait events. 

But in V$session_wait you can check the following :

     P1: Lock TYPE (or name) and MODE



    P2: Resource identifier ID1 for the lock



    P3: Resource identifier ID2 for the lock

 Which is not found in v$session_event .

So We can say :

V$SESSION_WAIT 
displays the events for which sessions have just completed waiting or are currently waiting.
V$SESSION_EVENT 
is similar to V$SYSTEM_EVENT, but displays all waits for each session.

 Reference Document :
1-V$session_wait
2-V$session_event
3-Events

Thank you
Osama Mustafa

 

No comments:

Post a Comment