HotSos_2005_OWI_10g by rocker12

Oracle10g: Wait Interface Improvements

Kirtikumar Deshpande

Hotsos Symposium 2005 1

Agenda 

Brief history of Oracle Wait Interface (OWI)



What’s new in Oracle10g Release 1



OWI views in Oracle10g Release 1



Extended SQL Tracing



Q&A 2

Brief History of OWI 

Introduced in Oracle 7.0.12



Four V$ views: – – – –



V$SYSTEM_EVENT V$SESSION_EVENT V$SESSION_WAIT V$EVENT_NAME

Extended SQL Trace / Debug Event 10046 – Trace level 8 – Trace level 12

Brief History of OWI 

Number of events tracked – – – –



Oracle 7.0.12 Oracle 7.3.4 Oracle8i 8.1.7.4 Oracle9i 9.2.0.4

less then 100 little over 100 over 200 around 400

Changes to V$SESSION_EVENT View – Oracle8: MAX_WAIT – Oracle9i: TIME_WAITED_MICRO 4

What’s new in Oracle10g Release 1 

Automatic Data Gathering



Introduction of Wait Classes



Enhanced existing wait event views



Total Number of events tracked - 811



Over a dozen views report wait event information



Improved E2E Extended SQL Tracing

(10g R1 HP-UX 11.11)

Automatic Data Gathering 

AWR (Automatic Workload Repository) – Collects cumulative and differential (delta) statistics at the database level – Collects metric statistics: rate of change in the cumulative statistics – Default: Freq - 1 Hour Retention - 7 Day



ASH (Active Session History) – Active sessions are sampled every second



Time Model – Tracks time spent in internal operations (parse, execute, I/O) – Used in Self Tuning decisions 6

Introduction of Wait Classes 

Enables high-level analysis of Waits



Classification based on solution to correct problematic wait events



12 Wait Classes



Defined in V$EVENT_NAME view 7

Enhanced V$EVENT_NAME View Name -------------------EVENT# EVENT_I D NAME PARAMETER1 PARAMETER2 PARAMETER3 WAI T_CLASS_I D WAI T_CLASS# WAI T_CLASS

Ty pe --------------NUMBER NUMBER VARCHAR2( 64) VARCHAR2( 64) VARCHAR2( 64) VARCHAR2( 64) NUMBER NUMBER VARCHAR2( 64)

Not es --------------<<<< Or acl e10g

<<<< Or acl e10g <<<< Or acl e10g <<<< Or acl e10g

V$EVENT_NAME EVENT# -----273 274 290 291 292 293 294

EVENT_I D ---------549236675 215477332 2652584166 506183215 1307477558 1620694733 834992820

NAME WAI T_CLASS# ------------------------ ----------l og f i l e sequent i al r ead 9 l og f i l e si ngl e wr i t e 9 db f i l e sequent i al r ead 8 db f i l e scat t er ed r ead 8 db f i l e si ngl e wr i t e 8 db f i l e par al l el wr i t e 9 db f i l e par al l el r ead 8

WAI T_CLASS -----------Syst em I / O Syst em I / O User I / O User I / O User I / O Syst em I / O User I / O

Wait Classes & Nbr of Events CLASS NBR_EVENTS --------------- ---------Admi ni st r at i ve 42 Appl i cat i on 11 Cl ust er 45 Commi t 1 Concur r ency 17 Conf i gur at i on 21 I dl e 59 Net wor k 25 Ot her 557 Sc hedul er 2 Sy st em I / O 19 Us er I / O 12

10 10g R1 HP- UX 11. 11

Wait Classes - Examples 

Administrative (DBA tasks affecting others) – Buffer pool resize – Offline rollback segment – Index rebuilds



Application – Row level locks or explicit locks



Commit – Redo log write confirmation



Concurrency – Buffer busy waits – Library cache latch 11

Wait Classes - Examples 

Configuration – Sizing for log buffer, log files, SGA components – Contention for ST enqueue



Network – More data to/from database link/client – Remote archive destination



User I/O – Database file reads, single writes, – Direct path reads and writes – BFILE reads 12

Wait Classes - Examples 

System I/O – Redo log writes – Archiving redo logs – Control file writes



Idle – Indicates that session is inactive, waiting for more work

Enhanced OWI Views 

New Columns: – V$SYSTEM_EVENT 

EVENT_ID

NUMBER

– V$SESSION_EVENT 

EVENT_ID

NUMBER

– V$SESSION_WAIT   

WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS

NUMBER NUMBER VARCHAR2(64)

Enhanced V$SESSION View 

Includes following columns from V$SESSION_WAIT SEQ# EVENT# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAI T_CLASS_I D WAI T_CLASS# WAI T_CLASS WAI T_TI ME SECONDS_I N_WAI T STATE

NUMBER NUMBER VARCHAR2( 64) VARCHAR2( 64) NUMBER RAW( 8) VARCHAR2( 64) NUMBER RAW( 8) VARCHAR2( 64) NUMBER RAW( 8) NUMBER NUMBER VARCHAR2( 64) NUMBER NUMBER VARCHAR2( 19)

Enhanced V$SESSION View 

No need to join V$SESSION_WAIT to V$SESSION



No need to query V$SESSION_WAIT view

SQL> sel ec t s i d, us er name, event , wai t _c l as s 2

seconds_ i n_ wai t " SI W" , st at e,

r ow_wai t _obj # " RWO#" ,

r ow_wai t _f i l e# " RWF#"

5 6 7

f r om v$ses si on wher e seconds_ i n_ wai t ! =0 and us er name i s not nul l ;

Enhanced V$SESSION View SI D USERNAME EVENT WAI T_CLASS SI W STATE RWO# RWF# --- -------- ----------------------------- ----------- --- ----------------- ---- ---43 SYSTEM db f i l e scat t er ed r ead User I / O 2 WAI TED SHORT TI ME 8980 5 38 KI RTI

enq: TX - r ow l ock cont ent i on Appl i cat i on

41 KI RTI

SQL* Net message f r om cl i ent

I dl e

30 WAI TI NG

8965

128 WAI TI NG

-1

In this example: – RWO# shows the object number the session is accessing – RWF# shows the absolute file number the session is accessing – Event for SID 38 shows the type of Enqueue the session is waiting for

Number of Wait Events 

Over 800 wait events



enqueue broken into 184 wait events – No need to decipher P1, P2 values



latch free broken into 28 wait events – No need to decipher P1, P2 values

10g R1 HP-UX 11.11

New V$ Views with Wait data             

V$ACTIVE_SESSION_HISTORY V$EVENTMETRIC V$EVENT_HISTOGRAM V$SERVICEMETRIC_HISTORY V$SERVICE_EVENT V$SERVICE_WAIT_CLASS V$SESSION_WAIT_CLASS V$SESSION_WAIT_HISTORY V$SYSMETRIC_HISTORY V$SYSTEM_EVENT V$SYSTEM_WAIT_CLASS V$WAITCLASSMETRIC V$WAITCLASSMETRIC_HISTORY

New DBA Views with Wait Data DBA_HIST_EVENT_NAME  DBA_HIST_SYSTEM_EVENT  DBA_HIST_BG_EVENT_SUMMARY  DBA_HIST_WAITCLASSMET_SUMMARY  DBA_HIST_SERVICE_WAIT_CLASS  DBA_HIST_ACTIVE_SESS_HISTORY 

V$SESSION_WAIT_HISTORY  

Shows 10 most recent waits for the session No need to repetitively query V$SESSION_WAIT view

SI D SEQ# EVENT# EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 WAI T_TI ME

NUMBER NUMBER  1 t o 10 ( l at est  ol dest ) NUMBER VARCHAR2( 64) VARCHAR2( 64) NUMBER VARCHAR2( 64) NUMBER VARCHAR2( 64) NUMBER NUMBER  0 = wai t i ng @snapshot t i me

WAI T_COUNT

NUMBER

V$SESSION_WAIT_HISTORY SI D SEQ# EVENT --- ---- --------------------------40 1 l og f i l e sync 40 2 SQL* Net message f r om cl i ent 40 3 SQL* Net message t o cl i ent 40 4 db f i l e scat t er ed r ead 40 5 db f i l e scat t er ed r ead 40 6 db f i l e scat t er ed r ead 40 7 db f i l e scat t er ed r ead 40 8 db f i l e scat t er ed r ead 40 9 db f i l e scat t er ed r ead 40 10 SQL* Net message t o cl i ent

P1 P2 P3 W_TM W_COUNT ---------- ---- ---- ----- ------962 0 0 0 1 1650815232 1 0 8721 1 1650815232 1 0 0 1 5 225 4 0 1 5 217 8 0 1 5 209 8 0 1 5 201 8 0 1 5 193 8 1 1 5 185 8 0 1 1650815232 1 0 0 1

10 r ows sel ect ed.

V$EVENT_HISTOGRAM Histogram of



–

Number of waits, maximum wait, wait time for each event

Predefined buckets, or time intervals



– – 

< 1 ms, < 2 ms, < 4 ms, < 8 ms, < 16 ms …. >= 2²² ms

TIMED_STATISTICS must be TRUE 23

V$EVENT_HISTOGRAM Name ----------------EVENT# EVENT WAI T_TI ME_MI LLI WAI T_COUNT

Type -----------NUMBER VARCHAR2( 64) NUMBER NUMBER

What’s missing? 24

V$EVENT_HISTOGRAM EVENT# EVENT WAI T_TI ME_MI LLI WAI T_COUNT ------- ----------------------- --------------- ---------294 db f i l e sequent i al r ead 1 29453 294 db f i l e sequent i al r ead 2 7319 294 db f i l e sequent i al r ead 4 749 294 db f i l e sequent i al r ead 8 904 294 db f i l e sequent i al r ead 16 3395 294 db f i l e sequent i al r ead 32 2167 294 db f i l e sequent i al r ead 64 270 294 db f i l e sequent i al r ead 128 56 294 db f i l e sequent i al r ead 256 16 294 db f i l e sequent i al r ead 512 9 295 db f i l e scat t er ed r ead 1 13397 295 db f i l e scat t er ed r ead 2 2202 295 db f i l e scat t er ed r ead 4 881 295 db f i l e scat t er ed r ead 8 822 295 db f i l e scat t er ed r ead 16 672 295 db f i l e scat t er ed r ead 32 523 295 db f i l e scat t er ed r ead 64 22825

V$FILE_HISTOGRAM FI LE# SI NGLEBLKRDTI M_MI LLI SI NGLEBLKRDS ---------- -------------------- -----------5 1 5608 5 2 401 5 4 96 5 8 41 5 16 53 5 32 23 5 64 10 5 128 3 5 256 5 5 512 1 10 r ows sel ect ed.

V$TEMP_HISTOGRAM FI LE# SI NGLEBLKRDTI M_MI LLI SI NGLEBLKRDS ---------- -------------------- -----------1 1 26 1 2 1 1 4 0 1 8 0 1 16 0 1 32 2 6 r ows sel ect ed.

V$SYSTEM_WAIT_CLASS 

Instance level waits by Wait Class



High-level wait information



Sample information over time to track Wait Classes of interest

V$SYSTEM_WAIT_CLASS Name -------------------WAI T_CLASS_I D WAI T_CLASS# WAI T_CLASS TOTAL_WAI TS TI ME_WAI TED

Type ----------NUMBER NUMBER VARCHAR2( 64) NUMBER NUMBER

V$SYSTEM_WAIT_CLASS WAI T_CLASS_I D WC# ------------- ---3875070507 4 3290255840 2 4217450380 1 1893977003 0 3386400367 5 1740759767 8 2000153315 7 4108307767 9 2723168908 6

WAI T_CLASS TOTAL_WAI TS TI ME_WAI TED ---------------- ----------- ----------Concur r ency 270 1596 Conf i gur at i on 378 1222 Appl i cat i on 3680 321401 Ot her 10394 65063 Commi t 28164 17657 User I / O 72340 22764 Net wor k 79141 200 Syst em I / O 206748 174050 I dl e 1156193 364114196

9 r ows sel ect ed.

What Cl asses ar e mi ssi ng? Why?

V$SESSION_WAIT_CLASS 

Session level waits by Wait Class



Somewhat High-level wait information



Helps identify area(s) of concern – Query by SID – Query by WAIT_CLASS# or WAIT_CLASS_ID – Use V$SESSION_EVENT to drill down 31

V$SESSION_WAIT_CLASS Name ------------------SI D SERI AL# WAI T_CLASS_I D WAI T_CLASS# WAI T_CLASS TOTAL_WAI TS TI ME_WAI TED

Type ------------NUMBER NUMBER NUMBER NUMBER VARCHAR2( 64) NUMBER NUMBER 32

V$SESSION_WAIT_CLASS 

Sample output for WAIT_CLASS# = 8

SI D SERI AL# WAI T_CLASS_I D WC# WAI T_CLASS TOTAL_WAI TS TI ME_WAI TED ----- ------- ------------- --- ------------ ----------- ----------42 3399 1740759767 8 User I / O 5605 397 43 643 1740759767 8 User I / O 35 70 47 1 1740759767 8 User I / O 1 0 49 1 1740759767 8 User I / O 333 297 53 1 1740759767 8 User I / O 98 126 54 1 1740759767 8 User I / O 5 5 55 1 1740759767 8 User I / O 809 816 56 1 1740759767 8 User I / O 190 25 57 1 1740759767 8 User I / O 10 1 58 1 1740759767 8 User I / O 6 3 10 r ows sel ect ed.

V$SESSION_WAIT_CLASS 

Sample output for SID = 42

SI D SERI AL# WAI T_CLASS_I D WC# WAI T_CLASS TOTAL_WAI TS TI ME_WAI TED ----- ------- ------------- --- ------------ ----------- ----------42 3399 1893977003 0 Ot her 1 0 42 3399 4217450380 1 Appl i cat i on 5 0 42 3399 3386400367 5 Commi t 1 0 42 3399 2723168908 6 I dl e 46116 234575 42 3399 2000153315 7 Net wor k 46118 39 42 3399 1740759767 8 User I / O 5605 397 42 3399 4108307767 9 Syst em I / O 3 0 7 r ows sel ect ed.

V$ACTIVE_SESSION_HISTORY 

Shows Sampled Active Session Activity, including waits



Sample frequency typically 1 second



Active Session : Session w/ Waits not in Idle Wait Class



Data stored in a circular buffer for 30 minutes



Busy system may not show all the data



AWR flushes the data to disk – Viewable via DBA_HIST_ACTIVE_SESS_HISTORY

V$ACTIVE_SESSION_HISTORY SAMPLE_I D SAMPLE_TI ME * SESSI ON_I D * SESSI ON_SERI AL# * USER_I D SQL_I D SQL_CHI LD_NUMBER SQL_PLAN_ HASH_VALUE SQL_OPCODE SERVI CE_HASH SESSI ON_TYPE SESSI ON_STATE QC_ SESSI ON_I D QC_ I NSTANCE_ I D EVENT EVENT_ I D

NUMBER TI MESTAMP( 3) NUMBER NUMBER NUMBER VARCHAR2( 13) NUMBER NUMBER NUMBER NUMBER VARCHAR2( 10) VARCHAR2( 7) NUMBER NUMBER VARCHAR2( 64) NUMBER

EVENT#

NUMBER

SEQ#

NUMBER

WAI T_TI ME

NUMBER

TI ME_WAI TED

NUMBER

CURRENT_OBJ#

NUMBER

CURRENT_FI LE#

NUMBER

CURRENT_BLOCK#

NUMBER

PROGRAM

VARCHAR2( 48)

MODULE

VARCHAR2( 48)

ACTI ON

VARCHAR2( 32)

CLI ENT_I D

VARCHAR2( 64)

V$SESS_TIME_MODEL 

Shows accumulated time model statistics at session level

Name ----------SI D STAT_I D STAT_NAME VALUE

Type ----------------------NUMBER NUMBER VARCHAR2( 64) NUMBER 37

V$SESS_TIME_MODEL SI D --40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40 40

STAT_NAME VALUE ---------------------------------------- ---------DB t i me 19376451 DB CPU 11492220 backgr ound el apsed t i me 0 backgr ound cpu t i me 0 sequence l oad el apsed t i me 0 par se t i me el apsed 62228 har d par se el apsed t i me 51538 sql execut e el apsed t i me 19291646 connect i on management cal l el apsed t i me 36309 f ai l ed par se el apsed t i me 0 f ai l ed par se ( out of shar ed memor y) el ap 0 har d par se ( shar i ng cr i t er i a) el apsed t i 0 har d par se ( bi nd mi smat ch) el apsed t i me 0 PL/ SQL execut i on el apsed t i me 1508 i nbound PL/ SQL r pc el apsed t i me 0 PL/ SQL compi l at i on el apsed t i me 0 Java execut i on el apsed t i me 0

17 r ows sel ect ed

E2E Extended SQL Tracing  

Trace Event 10046 with level 8, 12 End to End Application Tracing by – – – – –

 

SID, SERIAL# Service Name Application Module Application Action Client Identifier

DBMS_MONITOR Package Procedures Utility – trcsess 39

Tracing for SID, SERIAL# 

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE – ( session_id => 40, serial_num => 98, waits => true, binds => true );



EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE – ( session_id => 40, serial_num => 98 );

Tracing for Service, Module, Action 

Service Name must be set – Init.ora parameter: SERVICE_NAMES – TNSNAMES.ora parameter: SERVICE_NAME – V$SESSION shows SERVICE_NAME



Defaults: – SYS$BACKGROUND (for all Oracle BG processes) – SYS$USERS (for all User processes)



DBMS_SERVICE package procedures – CREATE_SERVICE – DELETE_SERVICE – DISCONNECT_SESSION – START_SERVICE – STOP_SERVICE

Tracing for Service, Module, Action 

Module Name must be set in the Application code – OCI Attribute OCI_ATTR_MODULE – DBMS_APPLICATION_INFO.SET_MODULE



Action name must be set in the Application code – OCI Attribute OCI_ATTR_ACTION – DBMS_APPLICATION_INFO.SET_ACTION



Default – Module  

‘SQL*Plus’ for connections via SQL*Plus Client program name

– Action 

None

Tracing for Service, Module, Action 

EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE – ( service_name => ‘APPS1’, module_name => ‘GLEDGER’, action_name => ‘DEBIT_ENTRY’, waits => true, binds => true, instance_name => null );



EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE – ( service_name => ‘APPS1’, module_name => ‘GLEDGER’, action_name => ‘DEBIT_ENTRY’, waits => true, binds => true, instance_name => null );

Tracing for Client Identifier 

Client Identifier must be set by the Application – – – –

OCI attribute OCI_ATTR_CLIENT_INFO DBMS_APPLICATION_INFO.SET_CLIENT_INFO DBMS_SESSION.SET_IDENTIFIER V$SESSION shows CLIENT_IDENTIFIER



EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE ( client_id => ‘KirtiD’, waits => true, binds => true );



EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE ( client_id => ‘KirtiD’);

Utility – trcsess 

Consolidates trace information into one output file from multiple trace files



The output file can be processed by TKPROF



It is a shell script running a Java Application



Options: – Session Id – Client Id – Service – Module – Action

Utility – trcsess 

Syntax: trcsess [output = file_name] [session = SID.SERIAL# ] [client_id = ‘client identifier’] [service = ‘service name’ ] [module = ‘module name’ ] [action = ‘action name’ ] [input_file_1 input_file2 …] 46

Oracle10g: Wait Interface Improvements Q&A