***
This article is being delivered in Draft form and may contain errors. Please use the MetaLink “Feedback” button to advise Oracle of any issues related to this article.
***
PURPOSE
——-
This note includes new columns of V$SESSION and how to effectively use V$SESSION during contention in Oracle 10G.
SCOPE & APPLICATION
—————————–
1. New columns of V$SESSION related with enhanced wait model of Oracle 10G.
*BLOCKING_SESSION_STATUS : VALID/NO HOLDER GLOBAL/UNIMPLEMENTED/UNKNOWN
*BLOCKING_SESSION : Session identifier of blocking session
SEQ#
EVENT#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
*WAIT_CLASS# : Wait class number
*WAIT_CLASS : Name of the wait class.
WAIT_TIME : A zero value means the session is currently waiting.
SECONDS_IN_WAIT
STATE : 0 – WAITING (the session is currently waiting)
2 – WAITED UNKNOWN TIME (duration of last wait is unknown)
-1 – WAITED SHORT TIME (last wait <1/100th of a second)
0 – WAITED KNOWN TIME (WAIT_TIME = duration of last wait)
Note(*) : brand new columns for Oracle 10G.
In prior releases to determine the sessions experiencing waits you joined the v$session_wait view with the v$session view. In the Oracle 10G Database, all wait event columns from v$session_wait have been added to v$session thus increasing performance by eliminating the overhead of joins.
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 Information.
— 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
Even after adding waiters on the same block, v$session shows that multiple sessions are waiting for 1 blocking session.
session 3 : update dept set loc=’SEOUL’ where deptno=10;
session 4 : update dept set loc=’SEOUL’ where deptno=10;
BLOCKER PROGRAM USERNAME BLOCKED
———- —————————————- ———- ———-
14 sqlplus@dhcp-samhwa-152-69-41-87 (TNS V1 SCOTT 9
14 sqlplus@dhcp-samhwa-152-69-41-87 (TNS V1 SCOTT 12
14 sqlplus@dhcp-samhwa-152-69-41-87 (TNS V1 SCOTT 15
(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
14 SQL*Net message from client enq: TX – row lock contention 12
14 SQL*Net message from client enq: TX – row lock contention 15
(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
12 enq: TX – row lock contention 41456 1 46818 0
15 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. WAIT_CLASS column in the v$session is used to gain quick insight into contentious areas of the database.
— waitclass.sql
select class#, class, count(event#)
from v$event_name
group by class#, 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
RELATED DOCUMENTS
—————–
Oracle10G Database Reference: Release 1 (10.1) BETA 2, Chapter 4.Dynamic Performance (V$)
Views.
Oracle10G Database Performance Tuning Guide Release 1 (10.1) – BETA 2
Oracle10G Database Release 1: Maximize Performance – General Enhancements Student Guide