Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 29, 2006

New Dictionary Enhancements with 10g – Part 2

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 9:47 am

In Part 1 I mentioned three dictionary enhancements;

1- OS Statistics and Finding System CPU Utilization with 10g
2- BLOCKING_SESSION_STATUS and BLOCKING_SESSION columns on V$SESSION
3- Identify Tracing Enabled Sessions

Some more coming :)

4- Tuning and monitoring enhancements; latch free and enqueue examples

a) Prior 10g finding details for a latch free wait was as follows;


SELECT event, state, p1, p2, p3 FROM v$session_wait WHERE sid = 162;

EVENT         STATE            P1      P2    P3
————- ——- ———–  —— —–
latch free    WAITING 15113593728      97     5

SELECT * FROM v$event_name WHERE name = 'latch free';

EVENT# NAME       PARAMETER1      PARAMETER2      PARAMETER3
—— ———- ————— ————— —————
3 latch free address         number          tries

SELECT name FROM v$latch WHERE latch# = 97;

NAME
——————–
cache buffers chains

After 10g all these steps are in just one sql;


SELECT event, state
FROM   v$session_wait
WHERE  sid = 162;

EVENT                          STATE
—————————— ——-
latch: cache buffers chains    WAITING

b) Prior 10g finding details for enqueue waits were like this;


SELECT event, state, seconds_in_wait FROM v$session_wait WHERE sid = 96;

EVENT                               STATE               SECONDS_IN_WAIT
———————————– ——————- —————
enqueue                             WAITING                          24

SELECT     sid, CHR (BITAND (p1,-16777216) / 16777215) ||
CHR (BITAND (p1, 16711680) / 65535) enq,
DECODE (CHR (BITAND (p1,-16777216) / 16777215) ||
CHR (BITAND (p1, 16711680) / 65535),
‘TX’, ‘Transaction (RBS)’,
…        :)
CHR (BITAND (p1, 16711680) / 65535)) enqueue_name,
DECODE (BITAND (p1, 65535), 1, ‘Null’, 2, ‘Sub-Share’,
3, ‘Sub-Exclusive’, 4, ‘Share’, 5, ‘Share/Sub-Exclusive’,
6, ‘Exclusive’, ‘Other’) lock_mode
FROM       v$session_wait
WHERE sid = 96;

SID ENQ  ENQUEUE_NAME                   LOCK_MODE
—– —- —————————— ———-
96 TX   Transaction (RBS)              Exclusive

After 10g like latch free example this is also much more easier;


SELECT event, state, seconds_in_wait FROM v$session_wait WHERE sid = 143;

EVENT                               STATE               SECONDS_IN_WAIT
———————————– ——————- —————
enq: TX - row lock contention       WAITING                         495

also after 10g different naming with ‘TX’ events giving more information;


SELECT name, parameter1, parameter2, parameter3
FROM   v$event_name
WHERE  name LIKE 'enq: TX%';

NAME                           PARAMETER1    PARAMETER2      PARAMETER3
—————————— ————- ————— ————-
enq: TX - contention           name|mode     usn<<16 | slot  sequence
enq: TX - row lock contention  name|mode     usn<<16 | slot  sequence
enq: TX - allocate ITL entry   name|mode     usn<<16 | slot  sequence
enq: TX - index contention     name|mode     usn<<16 | slot  sequence

and clearer parameter naming;


SELECT name, parameter1, parameter2, parameter3
FROM   v$event_name
WHERE  name IN (’enq: HW - contention’, ‘enq: SQ - contention’);

NAME                           PARAMETER1    PARAMETER2      PARAMETER3
—————————— ————- ————— ————-
enq: HW - contention           name|mode     table space #   block
enq: SQ - contention           name|mode     object #        0

c) Prior 10g we needed a join to get the wait parameters of a session;


SELECT s.sid, w.state, w.event, w.seconds_in_wait siw,
s.sql_address, s.sql_hash_value hash_value, w.p1, w.p2, w.p3
FROM   v$session s, v$session_wait w
WHERE  s.sid = w.sid
AND    s.sid = 154;

After 10g it is located in v$session;


SELECT     sid, state, event, seconds_in_wait siw,
sql_address, sql_hash_value hash_value, p1, p2, p3
FROM       v$session
WHERE      sid = 154;

d) Prior 10g it is hard to say but after 10g “sessions are waiting for what or whom” query is as follows;


SELECT     sid, blocking_session, blocking_session_status block_status,
username, event, seconds_in_wait siw
FROM   v$session
WHERE  sid = 154;

BLOCKING
SID _SESSION BLOCK_STATUS USERNAME EVENT                          SIW
— ——– ———— ——– —————————— —
154      157 VALID        TSUTTON  enq: TX - row lock contention  318

5- What are the valid values for Oracle parameters?

V$PARAMETER_VALID_VALUES view is introduced on 10g Release 2, returns one row for each valid value for each parameter taking scalar values.


SELECT NAME, VALUE, isdefault
FROM v$parameter_valid_values
ORDER BY NAME, isdefault DESC, VALUE

audit_trail    DB    FALSE
audit_trail    DB_EXTENDED    FALSE
..
workarea_size_policy    AUTO    TRUE
workarea_size_policy    MANUAL    FALSE

6- Oracle Active Session History(ASH) samples

With 10g by the Active Session History(ASH) feature the V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA.

As part of the Automatic Workload Repository(AWR) snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. The data present in ASH can be rolled up on various dimensions that it captures, some samples are as follows;


– Top CPU Session in last 5 minutes
SELECT session_id, COUNT(*)
FROM v$active_session_history
WHERE session_state = 'ON CPU'
AND sample_time > SYSDATE - (5 / (24 * 60))
GROUP BY session_id
ORDER BY COUNT(*) DESC

– Top Waiting Session in last 5 minutes
SELECT session_id, COUNT(*)
FROM v$active_session_history
WHERE session_state = 'WAITING'
AND sample_time > SYSDATE - (5 / (24 * 60))
GROUP BY session_id
ORDER BY COUNT(*) DESC

– Top SQL Queries from ASH
SELECT     ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
FROM     v$active_session_history ash, v$event_name en
WHERE     sql_id IS NOT NULL
AND     en.event# = ash.event#
GROUP BY sql_id
ORDER BY sum(decode(session_state,'ON CPU',1,1)) desc

– Top Sessions
SELECT     ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
FROM v$active_session_history ash, v$event_name en
WHERE en.event# = ash.event#
GROUP BY session_id, user_id, session_serial#, program
ORDER BY sum(decode(session_state,'ON CPU',1,1))

Some additional ASH information

7- New views for usage pattern of the database

DBA_HIGH_WATER_MARK_STATISTICS displays information about database high-watermark statistics;


NAME             HIGHWATER LAST_VALUE DESCRIPTION
————— ———- ———- ———————————————————-
USER_TABLES            401        401 Number of User Tables
SEGMENT_SIZE    1237319680 1237319680 Size of Largest Segment (Bytes)
PART_TABLES             12          0 Maximum Number of Partitions belonging to an User Table
PART_INDEXES            12          0 Maximum Number of Partitions belonging to an User Index
USER_INDEXES           832        832 Number of User Indexes
SESSIONS                19         17 Maximum Number of Concurrent Sessions seen in the database
DB_SIZE         7940079616 7940079616 Maximum Size of the Database (Bytes)
DATAFILES                6          6 Maximum Number of Datafiles
TABLESPACES              7          7 Maximum Number of Tablespaces
CPU_COUNT                4          4 Maximum Number of CPUs
QUERY_LENGTH          1176       1176 Maximum Query Length

DBA_FEATURE_USAGE_STATISTICS displays information about database feature usage statistics.


DBID                          : 4133493568
NAME                          : Partitioning
VERSION                       : 10.1.0.1.0
DETECTED_USAGES               : 12
TOTAL_SAMPLES                 : 12
CURRENTLY_USED                : FALSE
FIRST_USAGE_DATE              : 16-oct-2003 13:27:10
LAST_USAGE_DATE               : 16-dec-2003 21:20:58
AUX_COUNT                     :
FEATURE_INFO                  :
LAST_SAMPLE_DATE              : 23-dec-2003 21:20:58
LAST_SAMPLE_PERIOD            : 615836
SAMPLE_INTERVAL               : 604800
DESCRIPTION                   : Oracle Partitioning option is being used -
there is at least one partitioned object created
..

Continue reading with Part 3

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Refences Used : Wait Event Enhancements in Oracle 10g presentation of Terry Sutton and Roger Schrag, Database Specialists, Inc.
http://Oraperf.sourceforge.net by Kyle Hailey, Embarcadero Technologies

2 Comments »

  1. […] Continue reading with Part 2 […]

    Pingback by New Dictionary Enhancements with 10g - Part I « H.Tonguç YILMAZ Oracle Blog — October 6, 2007 @ 12:17 pm | Reply

  2. […] in In Part 2 I mentioned four more dictionary […]

    Pingback by New Dictionary Enhancements with 10g - Part 3 « H.Tonguç YILMAZ Oracle Blog — October 8, 2007 @ 12:26 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: