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 :

displays the current or last wait for each session.

lists information on waits for an event by a session

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

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 :

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

 Reference Document :

Thank you
Osama Mustafa


No comments:

Post a Comment