Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Oracle Real-Time Wait Events v$system_event

Oracle Tips by Burleson Consulting

Oracle Real-Time Wait Events v$system_event 

While tuning disk I/O waits is an important task, it should not be considered as a comprehensive approach to Oracle tuning.

The DBA must start by learning about the specific tables and indexes that are associated with the waits.  Since the
v$ views are accumulators, one can only see the sum the total number of waits since the instance started.

The v$session_wait view is a great place to start.  As disk read waits occur within the Oracle database, they appear in the v$session_wait view for a very short period of time.

Prior to the Automated Session History (ASH) table in Oracle10g, it was impossible to catch all of the run time waits because of the transient appearance of read waits.  Regardless, it is possible to take a frequent sample of the v$session_wait view and catch a representative sample of the system-waits details at the exact moment that the events occur.

Determining the exact table or index where the wait occurred, when the file and block number are available, is also credible.  Therefore, the enticing thing about the v$session_wait view is that the exact time the wait occurred can be captured as well as the file and block number that was being waited upon.

This view, shown below, provides the name of the wait event, the total number of waits and timeouts, the total time waited, and the average wait time per event.

 

select *
from
   v$system_event
where
   event like ‘%wait%’;

 

 EVENT                TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

--------------------- ----------- -------------- ----------- ------------

buffer busy waits          636528           1557      549700   .863591232

write complete waits         1193              0       14799   12.4048617

free buffer waits            1601              0         622   .388507183

 

The type of buffer that causes the wait can be queried using the v$waitstat view. This view lists the waits per buffer type for buffer busy waits, where count is the sum of all waits for the class of block, and time is the sum of all wait times for that class:

 

select *
from
 v$waitstat;

 

CLASS                   COUNT       TIME

 ------------------ ---------- ----------

 data block            1961113    1870278

 segment header          34535     159082

 undo header            233632      86239

 undo block               1886       1706

 

Buffer busy waits occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:

  • The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
  • Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.

Since buffer busy waits are due to contention between particular blocks, there's nothing that can be done until the DBA knows which blocks are in conflict and why the conflicts are occurring. Tuning, therefore, involves identifying and eliminating the cause of the block contention.

 

SQL> desc v$session_wait

 

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------

 SID                                                NUMBER

 SEQ#                                               NUMBER

 EVENT                                              VARCHAR2(64)

 P1TEXT                                             VARCHAR2(64)

 P1                                                 NUMBER

 P1RAW                                              RAW(4)

 P2TEXT                                             VARCHAR2(64)

 P2                                                 NUMBER

 P2RAW                                              RAW(4)

 P3TEXT                                             VARCHAR2(64)

 P3                                                 NUMBER

 P3RAW                                              RAW(4)

 WAIT_TIME                                          NUMBER

 SECONDS_IN_WAIT                                    NUMBER

 STATE                                              VARCHAR2(19)

 

The columns of the v$session_wait view that are of particular interest for a buffer busy wait event are:

  • P1: The absolute file number for the data file involved in the wait.
  • P2: The block number within the data file referenced in P1 that is being waited upon.
  • P3: The reason code describing why the wait is occurring.

The following is an Oracle data dictionary query for these values:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where   
event = 'buffer busy waits';

 

If the output from repeatedly running the above query shows that a block or range of blocks is experiencing waits, the following query should be used to show the name and type of the segment:

select
   owner,
   segment_name,
   segment_type
from
   dba_extents
where
   file_id = &P1
and
  &P2 between block_id and block_id + blocks -1;

 

Once the segment is identified, the v$segment_statistics performance view facilitates real time monitoring of segment level statistics. This enables a DBA to identify performance problems associated with individual tables or indexes, as shown below.

select
    object_name,
    statistic_name,
    value
from
    V$SEGMENT_STATISTICS
where
    object_name = 'SOURCE$';

 

The output looks like the following:

 

OBJECT_NAME   STATISTIC_NAME               VALUE

-----------  -------------------------     ----------

SOURCE$       logical reads                     11216

SOURCE$       buffer busy waits                   210

SOURCE$       db block changes                     32

SOURCE$       physical reads                    10365

SOURCE$       physical writes                       0

SOURCE$       physical reads direct                 0

SOURCE$       physical writes direct                0

SOURCE$       ITL waits                             0

SOURCE$       row lock waits

 

<      system_waits.sql

 

select event,

       total_waits,

       round(100 * (total_waits / sum_waits),2) pct_waits,

       time_wait_sec,

       round(100 * (time_wait_sec / greatest(sum_time_waited,1)),2)

       pct_time_waited,

       total_timeouts,

       round(100 * (total_timeouts / greatest(sum_timeouts,1)),2)

       pct_timeouts,

       average_wait_sec

from

(select event,

       total_waits,

       round((time_waited / 100),2) time_wait_sec,

       total_timeouts,

       round((average_wait / 100),2) average_wait_sec

from sys.v_$system_event

where event not in

('lock element cleanup',

 'pmon timer',

 'rdbms ipc message',

 'rdbms ipc reply',

 'smon timer',

 'SQL*Net message from client',

 'SQL*Net break/reset to client',

 'SQL*Net message to client',

 'SQL*Net more data from client',

 'dispatcher timer',

 'Null event',

 'parallel query dequeue wait',

 'parallel query idle wait - Slaves',

 'pipe get',

 'PL/SQL lock timer',

 'slave wait',

 'virtual circuit status',

 'WMON goes to sleep',

 'jobq slave wait',

 'Queue Monitor Wait',

 'wakeup time manager',

 'PX Idle Wait') AND

 event not like 'DFS%' AND

 event not like 'KXFX%'),

(select sum(total_waits) sum_waits,

        sum(total_timeouts) sum_timeouts,

        sum(round((time_waited / 100),2)) sum_time_waited

 from sys.v_$system_event

 where event not in

 ('lock element cleanup',

 'pmon timer',

 'rdbms ipc message',

 'rdbms ipc reply',

 'smon timer',

 'SQL*Net message from client',

 'SQL*Net break/reset to client',

 'SQL*Net message to client',

 'SQL*Net more data from client',

 'dispatcher timer',

 'Null event',

 'parallel query dequeue wait',

 'parallel query idle wait - Slaves',

 'pipe get',

 'PL/SQL lock timer',

 'slave wait',

 'virtual circuit status',

 'WMON goes to sleep',

 'jobq slave wait',

 'Queue Monitor Wait',

 'wakeup time manager',

 'PX Idle Wait') AND

 event not like 'DFS%' AND

 event not like 'KXFX%')

order by 4 desc, 1 asc;

 

The output of this script, in the Ion tool, looks like the following:

 

Figure 13.X: Output of system_waits.sql from Ion tool

 

The main wait class within ASH that relates to user I/O can be determined with the following script:

select  A.SID,
        B.USERNAME,
        A.WAIT_CLASS,
        A.TOTAL_WAITS,
        A.TIME_WAITED
from    V$SESSION_WAIT_CLASS A,
        V$SESSION B
where   B.SID = A.SID
and
        B.USERNAME IS NOT NULL
and
        A.WAIT_CLASS like ‘%I/O%’
order by 1,2,3;

Its output looks like this:

 

       SID USERNAME             WAIT_CLASS           TOTAL_WAITS TIME_WAITED

---------- -------------------- -------------------- ----------- -----------

       131 SYSMAN               User I/O                      11           5

       132 SYSMAN               User I/O                       3           2

       133 SYSMAN               User I/O                      13           7

       134 SYSMAN               User I/O                     173          97

       138 SPV                  User I/O                      10           3

       140 SYSMAN               User I/O                     387         413

       141 DBSNMP               User I/O                     201         433

       142 SYSMAN               User I/O                      36          71

       144 SYSMAN               User I/O                       6           7

       146 SYSMAN               User I/O                      35          28

       149 SYSMAN               User I/O                      46          42

       154 DBSNMP               System I/O                    84          68

       160 SYS                  System I/O                     5           3

       162 SYSMAN               User I/O                      16          10

 

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it directly from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:


http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 


”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.