Database Tuning‎ > ‎

10g New Functionality of V$SESSION and WAIT MODEL

posted Mar 9, 2011, 11:23 AM by Sachchida Ojha
In prior releases, we joined v$session_wait with the v$session to determine the sessions waits.
From Oracle 10g, all wait event columns from v$session_wait have been added
to v$session thus increasing performance by eliminating the overhead of joins.


1. New columns of V$SESSION related with enhanced wait model

(1) All V$SESSION_WAIT columns

SEQ#, EVENT#, EVENT, P1TEXT, P1, P1RAW, P2TEXT, P2, P2RAW, P3TEXT, P3, P3RAW,WAIT_TIME , SECONDS_IN_WAIT, STATE

(2) Brand new columns
BLOCKING_SESSION_STATUS : VALID/NO HOLDER GLOBAL/UNIMPLEMENTED/UNKNOWN
BLOCKING_SESSION : Session identifier of blocking session
WAIT_CLASS# : Wait class number
WAIT_CLASS : Name of the wait class.


2. How to determine a blocking session using only V$SESSION ?

session 1 : update dept set loc='SEOUL' where deptno=10;
session 2 : update dept set loc='SEOUL' where deptno=10;


(1) Finding Blocking Session

--- blockings.sql
col program format a40
col username format a10

select s.sid blocker, substr(s.program,1,40) program, w.username, w.sid blocked
from v$session s, v$session w
where w.blocking_session = s.sid
and w.blocking_session_status='VALID';
---

BLOCKER PROGRAM USERNAME BLOCKED
---------- ---------------------------------------- ---------- ----------
14 sqlplus@dhcp-samhwa-152-69-41-87 (TNS V1 SCOTT 9

 

(2) Finding Specific Type of WAIT EVENT.

Some enqueues and latches are now broken down in the specific type of event.
You will be able to see EVENT column for the enqueue in particular whether the breakdown
is made in v$session. The expected result is that instead of having a 'enqueue' wait,
you will see several 'enq%' type of waits.

--- waitevent.sql
col hevent format a40
col wevent format a40

select s.sid blocker,
s.event hevent,
w.event wevent,
w.sid blocked
from v$session s, v$session w
where w.blocking_session = s.sid
and w.blocking_session_status='VALID';
---

BLOCKER HEVENT WEVENT BLOCKED
---------- ---------------------------- ---------------------------- ---------
14 SQL*Net message from client enq: TX - row lock contention 9


(3) Finding Blocked OBJECT#, FILE#, BLOCK# and ROW#

--- blockedobj.sql
select sid, event, row_wait_obj# obj, row_wait_file# file,
row_wait_block# block, row_wait_row# row
from v$session s, v$session w
where w.blocking_session = s.sid
---

SID EVENT OBJ FILE BLOCK ROW
---- ------------------------------ ---------- ------ --------- ----
9 enq: TX - row lock contention 41456 1 46818 0


(4) Finding outstanding wait classes of sessions.

In Oracle 10G, wait events are classified into 14 categories of wait classes.
V$SESSION.WAIT_CLASS is used to gain quick insight into contentious areas of the
database.

--- waitclass.sql
col CLASS format a40
select wait_class# CLASS#, wait_class CLASS, count(event#)
from v$event_name
group by wait_class#, wait_class;
---

CLASS# CLASS COUNT(EVENT#)
------- ------------------------------------- --------------
0 Other 400
1 Application 8
2 Configuration 18
3 Administrative 40
4 Concurrency 12
5 Commit 1
6 Idle 49
7 Network 18
8 Archival Process Scheduling 19
9 Managed Recovery Processing Scheduling 10
10 User I/O 13
11 System I/O 17
12 Scheduler 6
13 Cluster 82


--- mostwaitclass.sql
select wait_class, count(username)
from v$session
group BY wait_class;
---

WAIT_CLASS COUNT(USERNAME)
-------------------------------- ---------------
Application 3
Idle 1
Other 1

Comments