OWI Components


OWI Components

Oracle Wait Interface is a collection of a few dynamic performance views and an extended SQL trace file.

Oracle Wait Interface provides statistics for wait events to tell you how many times and how long the session waited for an event to complete. To get the ‚“how long ‚½ part, however, you must set the initialization parameter, TIMED_STATISTICS, to TRUE. Old documentation associated high overhead with this parameter and scared a lot of DBAs away. But take comfort : these days, setting this parameter to TRUE does not add any appreciable overhead to the database performance on most platforms because most machines provide hardware support for fast timers. It provides critical and required timing information for all performance improvement efforts.

Since its introduction in Oracle Release 7.0.12, Oracle Wait Interface has had the following four V$ views:

  • V$EVENT_NAME

  • V$SESSION_WAIT

  • V$SESSION_EVENT

  • V$SYSTEM_EVENT

In addition to these wait event views, Oracle Database 10 g Release 1 introduces the following new views to display wait information from several perspectives:

  • V$SYSTEM_WAIT_CLASS

  • V$SESSION_WAIT_CLASS

  • V$SESSION_WAIT_HISTORY

  • V$EVENT_HISTOGRAM

  • V$ACTIVE_SESSION_HISTORY

However, V$SYSTEM_EVENT, V$SESSION_EVENT, and V$SESSION_WAIT remain the three prominent dynamic performance views that provide wait event statistics and timing information at various levels of granularity. The lowest level of granularity is provided by the V$SESSION_WAIT view. Information from this view is summarized to the V$SESSION_EVENT and then to the V$SYSTEM_EVENT view. You can represent this relationship as follows :

You may find yourself repeatedly querying these wait event views in short successions when investigating performance bottlenecks. However, there will be times when this is not feasible or practical. In that case, the wait event information can be captured into a trace file using the extended SQL trace facility and analyzed using the tkprof (Transient Kernel Profiler) utility.

Note ‚  

By default, the tkprof utility in the Oracle9i Database reports summarized wait event information from the extended SQL trace files, including those that are generated from earlier Oracle versions.

Let ‚ s now explore these Oracle Wait Interface components in detail.

V$EVENT_NAME View

Contrary to its naming convention, V$EVENT_NAME is not a dynamic V$ view. The information it provides does not change over time. It is a reference view that contains all the wait events defined for your database instance. The number of wait events varies from version to version and is also dependent on the Oracle options that are installed. Upon its introduction in Oracle Release 7.0.12, there were less than 100 wait events. Oracle Release 7.3.4 contained a little more than 100 wait events. Oracle8 i Database had over 200 wait events, while Oracle9 i Database has about 400. There are over 800 wait events in Oracle Database 10 g Release 1. But don ‚ t let these numbers overwhelm you. You need to familiarize yourself with only a handful of common wait events, and they normally cover 80 to 90 percent of your troubleshooting needs. You are not expected to know all of them, but you do need to know where to find them.

V$EVENT_NAME view has the following columns :

 Name                 Type            Notes 
-------------------- --------------- ---------------
EVENT# NUMBER
EVENT_ID NUMBER Starting Oracle10 g
NAME VARCHAR2(64)
PARAMETER1 VARCHAR2(64)
PARAMETER2 VARCHAR2(64)
PARAMETER3 VARCHAR2(64)
WAIT_CLASS_ID NUMBER Starting Oracle10 g
WAIT_CLASS# NUMBER Starting Oracle10 g
WAIT_CLASS VARCHAR2(64) Starting Oracle10 g

How to Use V$EVENT_NAME View

This is where you will discover the number of events that Oracle has defined for your database. If you do not yet know, we suggest you run a quick COUNT (*) against this view.

If you ever forget the exact spelling of an event name, you will find the answer in this view. An event name can change between versions. An example is the null event, which is spelled as ‚“Null event ‚½ in versions up to Oracle8 i Database and spelled as ‚“null event ‚½ thereafter.

The column EVENT# is a unique number for the event. This number can change from one Oracle version to another for the same wait event because of the way it is built by the compile time macros in the Oracle code. For better stability, Oracle Database 10 g Release 1 includes the EVENT_ID column, which contains a hash number that is based on the event name. The hash value will remain the same across versions for as long as the event name does not change. The column NAME contains the wait event name. Each wait event can have up to three attributes that are recorded in the PARAMETER1, PARAMETER2, and PARAMETER3 columns, respectively. These attributes give specific information about the wait event each time it occurs. You can list all the events and their attributes from V$EVENT_NAME view using the following query:

 select event#, name, parameter1, parameter2, parameter3 
from v$event_name
order by name;

The text of PARAMETER1, PARAMETER2, and PARAMETER3 of each event are also displayed in the P1TEXT, P2TEXT, and P3TEXT columns in the V$SESSION_WAIT view whenever a session waits on the event. The actual values for these parameters are shown in P1, P2, and P3 columns of the V$SESSION_WAIT view.

The WAIT_CLASS_ID, WAIT_CLASS#, and WAIT_CLASS columns are added to the V$EVENT_NAME view in Oracle Database 10 g Release 1 to group wait events by class or category, such as User I/O, Network, Concurrency, etc. The WAIT_CLASS_ID contains the hash value of the wait class name; it will remain the same from version to version as long as the name of the wait class does not change. The column WAIT_CLASS# contains a unique number for the WAIT_CLASS. Just like the EVENT#, it may change from version to version. The column WAIT_CLASS contains the actual name of the wait event class. There are 12 classes of wait events in Oracle Database 10 g Release 1 as shown next :

 select wait_class#, wait_class_id, wait_class 
from v$event_name
group by wait_class#, wait_class_id, wait_class;

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS
----------- ------------- ---------------------------------------------
0 1893977003 Other
1 4217450380 Application
2 3290255840 Configuration
3 4166625743 Administrative
4 3875070507 Concurrency
5 3386400367 Commit
6 2723168908 Idle
7 2000153315 Network
8 1740759767 User I/O
9 4108307767 System I/O
10 2396326234 Scheduler
11 3871361733 Cluster

Those of you who are not yet using Oracle Database 10 g Release 1 don ‚ t have to be envious. You can write a simple query using the DECODE function to classify the events in the same manner as Oracle Database 10 g Release 1.

V$SYSTEM_EVENT View

The V$SYSTEM_EVENT displays aggregated statistics of all wait events encountered by all Oracle sessions since the instance startup. It keeps track of the total number of waits, total timeouts, and time waited for any wait event ever encountered by any of the sessions.

The V$SYSTEM_EVENT view has the following columns:

 Name                 Type            Notes 
-------------------- --------------- ---------------
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
TIME_WAITED_MICRO NUMBER Starting Oracle9 i
EVENT_ID NUMBER Starting Oracle10 g

The column EVENT contains the name of the wait event, and the column TOTAL_WAITS contains the number of times the sessions waited on this event. If applicable to the event, the TOTAL_TIMEOUTS column records the number of times a session failed to get the requested resource after the initial wait. The column TIME_WAITED reports the total amount of time spent waiting on the event. The column AVERAGE_WAIT gives the average time for each wait and is derived from the TOTAL_WAITS and TIME_WAITED columns.

Before Oracle9 i Database, the unit of measure for wait event timing was in centiseconds, that is, 1/100 th of a second.

Starting with Oracle9 i Database, wait time has been tracked in microseconds, that is, 1/1,000,000 th of a second, and has been reported in the TIME_WAITED_MICRO column. The TIME_WAITED and AVERAGE_WAIT columns are derived by dividing TIME_WAITED_MICRO by 10000.

Oracle Database 10 g Release 1 added a new column, EVENT_ID, which represents a version-independent unique number for the event.

Some wait events have a wait time (or timeout time) attribute, which is the maximum slice of time that a session will wait on a particular event before renewing the wait. For example, the wait event free buffer waits has 100 centiseconds wait time. This means every session that waits on the free buffer waits event will wait for a maximum of 100 centiseconds. If free buffers are not available at the end of this wait time, the wait times out and the session renews its wait for another 100 centiseconds. The TOTAL_TIMEOUTS column keeps track of the number of times the wait was timed out. For some wait events, the wait time increases exponentially in subsequent attempts. Some wait events, particularly those related to I/O operations, do not have a timeout attribute. In this case, a session will wait indefinitely until the resource becomes available.

How to Use V$SYSTEM_EVENT View

The V$SYSTEM_EVENT view is a good place to start if you want to perform a quick health check on the database instance. This quick health check may be helpful when you must diagnose a poorly performing database instance, especially if you are unfamiliar with it and the application. You will quickly discover the top n bottlenecks that plague the database instance since startup. However, don ‚ t judge performance or make any tuning decisions based on these bottlenecks just yet. The information is cumulative since the instance startup. You must keep the following three things in mind when querying this view:

  • The V$SYSTEM_EVENT view provides instance-level wait event statistics. At this level, it does not offer details such as what sessions suffered the major bottlenecks and when they occurred, rendering it unsuitable for root cause analysis. You cannot always associate the worst event reported by this view with a session that is currently running very slowly.

  • You should always order the data from this view by the TIME_WAITED column, as shown in the following example. The values in the TOTAL_WAITS column can easily misguide you. In Chapter 1, we said you should never base your tuning decisions on quantity; rather, you should focus on time. Certain events such as the latch free event may show high TOTAL_WAITS, but each wait is very short and therefore, the TIME_WAITED may be insignificant. Other events, such as enqueue , may have low TOTAL_WAITS, but each wait might be long.

  • You need to evaluate the wait time with respect to the instance startup time. Don ‚ t be alarmed by high TIME_WAITED values. Chances are, the longer the instance uptime, the more you will see wait events having high TOTAL_WAITS and TIME_WAITED values.

     Prior to Oracle10  g  : 

    set lines 130
    set numwidth 18
    col event for a30
    col total_waits for 999,999,999
    col total_timeouts for 999,999,999
    col time_waited for 999,999,999,999
    col average_wait for 999,999,999,999
    select a.*, b.startup_time
    from v$system_event a,
    v$instance b
    order by a.time_waited;

    Starting Oracle10g:

    set lines 160
    set numwidth 18
    col class for a15
    col event for a30
    col total_waits for 999,999,999
    col total_timeouts for 999,999,999
    col time_waited for 999,999,999,999
    col average_wait for 999,999,999,999
    select b.class, a.*, c.startup_time
    from v$system_event a,
    v$event_name b,
    v$instance c
    where a.event = b.name
    order by b.class, a.time_waited;

Pay attention to the AVERAGE_WAIT column of I/O related events (the User I/O and System I/O classes in Oracle Database 10 g Release 1), which are reported in centiseconds. It can shed some light on the speed of your I/O subsystem. More importantly, it represents your average I/O cost.

As shown in the following partial example output from an Oracle8 i Database instance, on average it costs a session 0.103029259 centiseconds (10ms) for each single block read and 0.061187607 centiseconds (6ms) for each multiblock read. So you may be able to tolerate higher I/O calls if the average costs are low. (There is more discussion on this subject in Chapter 5.)

 EVENT                     TOTAL_WAITS TIME_WAITED AVERAGE_WAIT 
------------------------- ----------- ----------- ------------
db file sequential read 63094601 6500590 .103029259
db file scattered read 103809207 6351837 .061187607
Note ‚  

Starting from Oracle9i Database, the AVERAGE_WAIT column always reports integer numbers. This is because Oracle applies the ROUND function when dividing the TIME_WAITED by the TOTAL_WAITS to compute AVERAGE_WAIT.

The data in the V$SYSTEM_EVENT view persists throughout the life of the instance. Oracle doesn ‚ t store information from this view for historical analysis as it does for the V$LOG_HISTORY view.

You may want to know what bottlenecks exist between a certain time frame, which may encompass a load or processing cycle. To get this information, you must sample this view periodically and compute the differences, commonly known as delta . After reviewing the delta information, you can concentrate on the areas of concern. You can investigate the wait events that were waited on the most and categorize them. Are they I/O-based events such as db file scattered read and db file sequential read ? Or are they memory-based events such as latch free ? If so, you can zero in on the system resources that may need augmentation .

The Oracle-supplied Statspack utility simplifies this task. You can take level-0 snapshots over time and produce a Statspack report showing the delta.

If you do not have Statspack installed, you may use the following SQL scripts to produce similar results. This method was used before Statspack was introduced in Oracle8 i Database. We encourage you to install and use Statspack instead.

 -- Assumption is that you have TOOLS tablespace in your database. 

-- Create Begin and End tables to store V$SYSTEM_EVENT contents for
-- time T1 and T2 to compute delta.
-- ===================================
-- You only need to create these tables once.
-- ===================================
create table begin_system_event tablespace tools
as
select *
from v$system_event
where 1=2;

create table end_system_event tablespace tools
as
select *
from v$system_event
where 1=2;

-- Take a snapshot of V$SYSTEM_EVENT information at time T1
truncate table begin_system_event;
insert into begin_system_event
select *
from v$system_event;

-- Wait n seconds or n minutes, and then take another snapshot
-- of V$SYSTEM_EVENT at time T2
truncate table end_system_event;
insert into end_system_event
select *
from v$system_event;

-- Report the delta numbers for wait events between times T2 and T1
select t1.event,
(t2.total_waits - nvl(t1.total_waits,0)) "Delta_Waits",
(t2.total_timeouts - nvl(t1.total_timeouts,0)) "Delta_Timeouts",
(t2.time_waited - nvl(t1.time_waited,0)) "Delta_Time_Waited"
from begin_system_event t1,
end_system_event t2
where t2.event = t1.event(+)
order by (t2.time_waited - nvl(t1.time_waited,0)) desc;

V$SESSION_EVENT View

The V$SESSION_EVENT view contains aggregated wait event statistics by session for all sessions that are currently connected to the instance. This view contains all the columns present in the V$SYSTEM_EVENT view and has the same meaning, but the context is session-level. It keeps track of the total waits, time waited, and maximum wait time of each wait event by session. The SID column identifies individual sessions. The maximum wait time per event per session is tracked in MAX_WAIT column. You can get more information about the session and user by joining V$SESSION_EVENT with V$SESSION using the SID column.

The V$SESSION_EVENT view has the following columns:

 Name                 Type            Notes 
-------------------- --------------- ---------------
SID NUMBER
EVENT VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED NUMBER
AVERAGE_WAIT NUMBER
MAX_WAIT NUMBER Starting Oracle8
TIME_WAITED_MICRO NUMBER Starting Oracle9 i
EVENT_ID NUMBER Starting Oracle10 g

How to Use V$SESSION_EVENT View

The V$SESSION_EVENT view is useful when you know the SID of the session that is currently connected to the instance. Let ‚ s say you get a call about a job that is running very slowly. You ask for the USERNAME and find the SID from the V$SESSION view. (Don ‚ t ask your user for the SID; you should be thankful if they can give you the USERNAME. Finding the exact SID can still be a challenge if your application connects with a common USERNAME.) You then query the V$SESSION_EVENT view for the particular SID and order the result by the TIME_WAITED column. You can easily pick out the bottlenecks that may be contributing to poor performance.

Finding the major bottlenecks that are slowing a session down is only the first step in OWI performance tuning. The next step is to determine the root cause, which can be rather difficult using data from the V$SESSION_EVENT view for two main reasons:

  • The V$SESSION_EVENT view does not keep track of the SQL statements that experience the bottlenecks. Without correlating bottlenecks to the SQL statements, you may not be able to minimize the bottlenecks even if you discover the worst bottleneck for a session. For example, you may discover that the db file scattered read wait event is the major performance bottleneck, but without capturing the SQL statement that performs the full table scans , you ‚ ll have very little to offer to your unhappy customers.

  • It is hard to determine the root cause of a performance problem if you don ‚ t have enough information about its symptom. For example, all latch waits are rolled up into the latch free wait event, and you cannot tell which one of the several hundred latches the session waited on the most. However, in Oracle Database 10 g Release 1 a handful of the latches are broken out into their individual wait events.

Due to the preceding reasons, the V$SESSION_EVENT view is not suitable for root cause analysis, but it is a good place to get an initial idea of the kind of problems you are dealing with when you have the SID of the running session.

Another column that you may find useful is MAX_WAIT, which records the maximum wait time for each event as experienced by each session in centiseconds. For example, you can find out the longest time a session had to wait for a single block read ( db file sequential read ). It is like a high-water mark of the wait time for the wait event. However, there is no timestamp associated with MAX_WAIT, which means you will not know when the MAX_WAIT was reached. However, Oracle has an undocumented procedure, dbms_system.kcfrms() , that resets this column to zero. You can then monitor how MAX_WAIT gets populated . Let ‚ s say you are about to execute another module or another step within a job, and you want to know the maximum wait time of each wait event encountered during this execution. You can execute the following procedure while connected as sysdba :

 execute dbms_system.kcfrms; 
Note ‚  

This procedure will reset the MAX_WAIT column for all sessions. In addition, it will reset the MAXIORTM and MAXIOWTM columns in the V$FILESTAT view.

If you use earlier releases of Oracle9 i Database, you should be aware of a bug (#2429929) that causes misalignment of the SID number between the V$SESSION_EVENT and V$SESSION views. The SID number in the V$SESSION_EVENT view is off by 1, and it does not have statistics for the PMON process, which has the SID value of 1 in the V$SESSION view. The following query helps you get the correct result in versions that are affected by this bug. According to Oracle, this problem is corrected in release 9.2.0.3.

 break on sid skip 1 dup 
col sid format 999
col event format a39
col username format a6 trunc
select b.sid,
decode(b.username,null,
substr(b.program,18),b.username) username
a.event,
a.total_waits,
a.total_timeouts,
a.time_waited,
a.average_wait,
a.max_wait,
a.time_waited_micro
from v$session_event a, v$session b
where b.sid = a.sid + 1
order by 1, 6;

As in all dynamic performance views, the session-level data of each session persists through the life of the session. Oracle does not keep a history for session-level wait event information. However, the V$ACTIVE_SESSION_HISTORY view in Oracle Database10 g Release 1 keeps about half an hour of session-level wait event history in memory that was sampled every second. We discuss this view in detail in Chapter 9.

V$SESSION_WAIT View

The V$SESSION_WAIT view provides detailed information about the event or resource that each session is waiting for. This view contains only one row of information per session, active or inactive, at any given time. Unlike the other views, this view displays session-level wait information in real time. Because of
this, it may very well show different results each time you query the view.

 Name                 Type            Notes 
-------------------- --------------- ---------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER Starting Oracle10 g
WAIT_CLASS# NUMBER Starting Oracle10 g
WAIT_CLASS VARCHAR2(64) Starting Oracle10 g
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)

As you already know, the column SID shows the session identifier, and EVENT contains the name of the event. The columns P1, P2, and P3 contain specific information about the event and identify a specific resource the session is waiting for. The interpretation of these values is event dependent. The columns P1RAW, P2RAW, and P3RAW contain the hexadecimal representation of the values in P1, P2 and P3, respectively. To help you reference what these values pertain to, the columns P1TEXT, P2TEXT, and P3TEXT provide the description for columns P1, P2 and P3, respectively. The column SEQ# is an internal sequence number for the event related to the session. It increments each time the session waits on the event. The WAIT_TIME column records the amount of time the session has waited for. A negative value denotes unknown wait time, a value equal to zero means that the session is still waiting, and a value greater than zero is the actual wait time. The column SECONDS_IN_WAIT shows wait time in seconds while the session is waiting on the event. The STATE column shows the current wait status. The trio of STATE, WAIT_TIME, and SECONDS_IN_WAIT should be evaluated together, and the next section shows you how to do that.

The WAIT_CLASS_ID, WAIT_CLASS#, and WAIT_CLASS columns are introduced in Oracle Database 10 g Release 1 to support the wait event classification feature.

Note ‚  

In Oracle Database 10 g Release 1 the V$SESSION_WAIT view is wholly incorporated into the V$SESSION view so that you can get pertinent information about the user session without having to join the two views.

How to Use V$SESSION_WAIT View

A good time to query the V$SESSION_WAIT view is when you get a call about a slow running application and you want to investigate what the application is doing at that particular moment. Due to its real-time nature, you have to query this view repeatedly, most likely in quick successions. You may see different wait events cycle through quickly, which means the application is actively working, or you may see the application continue to wait on the same event, which means it could be idle or actively waiting on a particular resource such as a lock, a latch or, in case of a resumable session (from Oracle9 i Database), a disk space ‚ related issue.

Note ‚  

Resumable sessions will post event statement su spended, wait error to be cleared when they encounter disk space problems; they will wait till the error is cleared or the resumable session times out.

Obviously, this view is useful only for current waits and does not provide a history of past wait events. However, that is changed in Oracle Database 10 g Release 1. See the ‚“V$SESSION_WAIT_HISTORY View ‚½ section for more information.

As explained in the previous section, P1, P2, and P3 are attributes that describe the wait event they are associated with. P1RAW, P2RAW, and P3RAW are the hexadecimal equivalents of P1, P2, and P3. You should familiarize yourself with the attributes of common wait events, but in case you forget, you can get their descriptions from the P1TEXT, P2TEXT, and P3TEXT columns. For root-cause analysis, you must translate the attributes into meaningful information. For example, you can obtain the object name that the db file sequential read and db file scattered read events are referencing by translating their P1 and P2 values. Likewise, you can find out the name of the latch that the session is waiting on by translating the P2 value. No doubt this can be a very tedious task if you try to do it manually. By the time you successfully translate one event, many other events could go by unnoticed. (Chapter 4 discusses wait event monitoring and data collection in great detail.) Oracle Database 10 g Release 1 simplifies this task for latch and enqueue wait events. Common latches have their own wait events, for example, latch: redo copy and latch: cache buffers chains . All enqueues have their own wait events, for example, enq: TX - row lock contention and enq: ST - contention .

The STATE column has four possible values: WAITED UNKNOWN TIME, WAITED SHORT TIME, WAITING, and WAITED KNOWN TIME, and their meanings are as follows:

  • WAITED UNKNOWN TIME simply means that the TIMED_STATISTICS initialization parameter is set to FALSE and Oracle is unable to determine the wait time. In this case, the WAIT_TIME column shows ‚ 2.

  • WAITED SHORT TIME means the previous wait was less than one centisecond. In this case, the WAIT_TIME column shows ‚ 1.

  • WAITING means that the session is currently waiting and the WAIT_TIME column shows 0, but you can determine the time spent on this current wait from the SECONDS_IN_WAIT column. ( Please note that SECONDS_IN_WAIT is in seconds, but WAIT_TIME is in centiseconds.)

  • WAITED KNOWN TIME means Oracle is able to determine the duration of the last wait and the time is posted in the WAIT_TIME column.

When monitoring the V$SESSION_WAIT view, pay particular attention to the events with STATE=WAITING. This indicates the sessions are actively waiting on the event. The SECONDS_IN_WAIT column shows how long the sessions have been waiting. Also, bear in mind that you should not judge performance based on just one wait occasion. If you want to know how much time a session has waited on a particular wait event, you should consult the V$SESSION_EVENT view.

Note ‚  

A bug (#2803772) in Oracle Release 9.2.0.2 resets the SECONDS_IN_WAIT column to zero each time the SEQ# changes for the event. There is a patch to correct this issue, or you can upgrade your database to Oracle Release 9.2.0.4.

Trace Event 10046 ‚ The Extended SQL Trace

As briefly mentioned earlier, there will be times when it is not feasible to monitor wait events interactively by querying the wait event views. Many times the best way to diagnose a performance problem is by recording the wait events in a trace file for further analysis. This is equivalent to setting SQL_TRACE=TRUE for the session. However, the trace event 10046 can provide a lot more information, depending on the trace level, because it is a superset of SQL trace.

Following are the valid trace levels for this event:

  • Level 0 Tracing is disabled. This is the same as setting SQL_TRACE = FALSE.

  • Level 1 Standard SQL trace information (SQL_TRACE = TRUE). This is the default level.

  • Level 4 SQL trace information plus bind variable values.

  • Level 8 SQL trace information plus wait event information.

  • Level 12 SQL trace information, wait event information, and bind variable values.

You can use the trace event 10046 to trace user sessions or Oracle background processes. When tracing a user session, the trace file is written to the directory specified by the parameter USER_DUMP_DEST (UDUMP). When tracing background processes, the trace file is written to the directory specified by the parameter BACKGROUND_DUMP_DEST (BDUMP). The size of the trace file depends on four factors: the trace level, the duration of the trace, the session ‚ s activity level, and
the value of the MAX_DUMP_FILE_SIZE parameter. The higher the trace level, the larger the trace file, as trace event 10046 is very aggressive at higher debug levels, such as 8 and 12. Due to this aggressive nature, the trace event 10046 is nicknamed ‚“SQL trace on steroids. ‚½

How to Use Trace Event 10046

There are various ways to enable the trace event 10046 at the instance or session level. You can enable trace event 10046 at instance level by using the EVENT initialization parameter and restarting the instance so the parameter takes effect, but please don ‚ t rush and try it out just yet! At this level, Oracle will begin tracing all sessions and can quickly fill up the UDUMP and BDUMP directory. It is not a good idea to set trace event 10046 at an instance level. We discuss this only for academic interest and show the following syntax for completeness, as this is one of the ways to trap wait event information in a trace file:

 # This enables the trace event 10046 at level 8 for the instance. 
# Restart the instance after this change is made to the init.ora file.
EVENT = "10046 trace name context forever, level 8"

# To disable the trace event 10046 at instance level simply delete or
# comment out the EVENT parameter in the init.ora file and restart the
# instance.
## EVENT = "10046 trace name context forever, level 8"

The session level is the practical level for enabling the trace event 10046, and the trace level 8 is normally sufficient. You can trace your own or someone else ‚ s session. You can enable and disable the trace at any point during the life of the session. Once the trace is disabled, Oracle stops writing to the trace file.

How to Trace Your Own Session

Enabling trace event 10046 for your current session is easy. You can enter the command interactively or embed it in your SQL script or program. Before you enable the trace, it is a good idea to make sure the parameter TIMED_STATISTICS is set to TRUE and that the trace file size governor , the MAX_DUMP_FILE_SIZE parameter, is set sufficiently large. If TIMED_STATISTICS is not set to TRUE, Oracle will not report any timing information. If the MAX_DUMP_FILE_SIZE is not large enough, Oracle will stop writing trace information to the file after it reaches this value. Following example shows the steps to enable trace for your session:

 alter session set timed_statistics = true; 
alter session set max_dump_file_size = unlimited;
-- To enable the trace event 10046 in Oracle 7.3 onwards
alter session set events 10046 trace name context forever, level 8;
-- Run your SQL script or program to trace wait event information
-- To turn off the tracing:
alter session set events 10046 trace name context off;

If you have the DBMS_SUPPORT package installed, you can use the following procedure to enable and disable tracing:

 -- To include Wait Event data with SQL trace (default option) 
exec sys.dbms_support.start_trace;
-- To include Bind variable values, Wait Event data with SQL trace
exec sys.dbms_support.start_trace(waits => TRUE, binds=> TRUE)
-- Run your SQL script or program to trace wait event information
-- To turn off the tracing:
exec sys.dbms_support.stop_trace;
Note ‚  

Per MetaLink Note #62294.1, you will need to run dbmssupp.sql to create DBMS_SUPPORT package. The script is located in the rdbms/admin directory under ORACLE_HOME.

How to Trace Someone Else ‚ s Session

Tracing someone else ‚ s session or program is a bit more of an involved process, and there are several ways to do it.

If you are not sure whether the TIMED_STATISTICS and MAX_DUMP_FILE_SIZE parameters are set appropriately for the session you want to trace, you should get the SID and its serial number (SERIAL#) from the V$SESSION view. You can then make the following procedure calls to set these parameters appropriately before enabling the trace.

 -- Set TIME_STATISTICS to TRUE for SID 1234, Serial# 56789 

exec sys.dbms_system.set_bool_param_in_session(-
sid => 1234, -
serial# => 56789, -
parnam => TIMED_STATISTICS, -
bval => true);
-- Set MAX_DUMP_FILE_SIZE to 2147483647
-- for SID 1234, Serial# 56789
exec sys.dbms_system.set_int_param_in_session(-
sid => 1234, -
serial# => 56789, -
parnam => MAX_DUMP_FILE_SIZE, -
intval => 2147483647);

You can use ALTER SYSTEM SET <parameter> command to set these parameters if these procedures are not available in your Oracle version (Oracle Release 8.1.5 and below).

The next step is to enable the trace in the other session and then disable it after you have gathered enough trace information. You can use one of the following ways to do that:

  • Use the DBMS_SUPPORT package procedures:

     -- Enable level 12 trace in session 1234 with serial# 56789 
    exec dbms_support.start_trace_in_session(-
    sid => 1234, -
    serial# => 56789, -
    waits => true, -
    binds => true);

    -- Let the session execute SQL script or
    -- program for some amount of time

    -- To turn off the tracing:
    exec dbms_support.stop_trace_in_session(-
    sid => 1234, -
    serial# => 56789);
  • Use the DBMS_SYSTEM package procedure:

     -- Enable trace at level 8 for session 1234 with serial# 56789 
    exec dbms_system.set_ev(1234, 56789, 10046, 8,);

    -- Let the session execute SQL script or
    -- program for some amount of time

    -- To turn off the tracing:
    exec dbms_system.set_ev(1234, 56789, 10046, 0,);
    Note ‚  

    Oracle does not officially support the use of DBMS_SYSTEM.SET_EV procedure.

  • Use the oradebug facility. You need to know the session's OS process ID (SPID) or Oracle process ID (PID). You can look them up in the V$PROCESS view. Assuming you know the name of the user you want to trace:

     select s.username, 
    p.spid os_process_id,
    p.pid oracle_process_id
    from v$session s, v$process p
    where s.paddr = p.addr
    and s.username = upper(&user_name);

    Now use SQL*Plus to connect as sysdba and issue following commands:

     alter system set timed_statistics = true; 
    oradebug setospid 12345;
    -- 12345 is the OS process id for the session
    oradebug unlimit;
    oradebug event 10046 trace name context forever, level 8;
    -- Let the session execute SQL script
    -- or program for some amount of time

    -- To turn off the tracing:
    oradebug event 10046 trace name context off;

    In Oracle Database 10 g Release 1 you can use DBMS_MONITOR package procedures to enable tracing based on the SID, service name, module, or action. The action-based tracing empowers a DBA to trace a specific business function. There is a little catch to this: the procedure requires that the DBA know the module and action names .

  • Use the DBMS_MONITOR package to enable tracing for session 1234 and serial# 56789 as shown below:

     exec dbms_monitor.session_trace_enable(- 
    session_id => 1234, -
    serial_num => 56789, -
    waits => true, -
    binds => true);
    -- Let the session execute SQL script or
    -- program for some amount of time

    -- To turn off the tracing:
    exec dbms_monitor.session_trace_disable(-
    session_id => 1234, -
    serial_num => 56789);

    These procedures look exactly like the ones from DBMS_SUPPORT package. We recommend that you use DBMS_MONITOR package procedures in Oracle Database 10 g Release 1.

  • Use the DBMS_MONITOR package for service, module, and action-based tracing:

     -- Enable Level 12 trace for known Service, 
    -- Module and 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);

    -- Let the session execute SQL script or
    -- program for some amount of time

    -- To turn off the tracing:
    exec dbms_monitor.serv_mod_act_trace_disable(-
    service_name => APPS1, -
    module_name => GLEDGER, -
    action_name => DEBIT_ENTRY);
    Note ‚  

    The module and action name must be set by the application using either OCI calls or procedures in the DBMS_APPLICATION_INFO package.

How to Find Your Trace File

When you enable the trace for your session or for someone else ‚ s session, the trace information may get written to a single trace file or multiple trace files. If you do not use Oracle ‚ s multithreaded server (MTS) or shared server setup, only one trace file will be generated for one SQL*Plus session. In case of MTS or a shared server, the trace information may be written to multiple trace files.

As mentioned earlier, the trace files for user sessions will be written to the USER_DUMP_DEST directory, and for background processes they will be written to the BACKGROUND_DUMP_DEST directory. Trace file names contain the .trc or .TRC extension on most platforms. The easiest way to find your trace file is to list the directory contents in the descending order of timestamp as soon as the tracing is completed. Chances are the newest file is the trace file you are looking for. You can confirm it by examining the contents of the file.

Finding your trace file is a bit easier with the oradebug trace facility because the SPID number of the dedicated server process is also written to the trace file. In addition, you can also get the actual trace file name as follows:

 SQL> oradebug setmypid 
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 8
Statement processed.
SQL> oradebug tracefile_name
d:\oracle\admin\or92\udump\or92_ora_171.trc

Starting in Oracle Release 8.1.7 you can set TRACEFILE_IDENTIFIER parameter for your session using ALTER SESSION command, as shown here:

 alter session set tracefile_identifier = MyTrace; 

The generated trace file will contain ‚“MyTrace ‚½ in its name, making it much easier for you to find the file.

What Does the Trace File Show?

Once you have located your trace file, the next step is to analyze it for wait events information. The file contains lines starting with WAIT, as shown in the following output from an Oracle9 i Database trace file. These lines show the name (nam) of the event Oracle waited on and the elapsed time (ela) of the wait. Beginning in Oracle9 i Database, this time is in microseconds, but prior to Oracle9 i Database, it is in centiseconds. You will also find the p1, p2, and p3 parameters that are associated with the wait event. This is equivalent to the P1, P2, and P3 of the V$SESSION_WAIT view. Metalink article #39817.1 discusses how to interpret this raw output.

 PARSE#1:c=710000,e=1061985,p=4,cr=706,cu=0,mis=1,r=0,dep=0,og=4,tim=183749167771 
EXEC #1:c=0,e=399,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=183749170861
WAIT #1: nam='SQL*Net message to client' ela= 12 p1=1650815232 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 21578 p1=6 p2=130570 p3=1
WAIT #1: nam='db file sequential read' ela= 17009 p1=6 p2=160988 p3=1
. . . . . . .
. . . . . . .
WAIT #1: nam='SQL*Net message from client' ela= 4700 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 190 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=1408,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=183768775984
WAIT #1: nam='SQL*Net message to client' ela= 7 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=1221,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=183768909326
. . . . . . .

As you can see, it is rather difficult to manually group the events and sum up their wait times to determine the primary bottleneck. As previously mentioned, the Oracle9 i Database tkprof utility summarizes wait event information that is in the trace file. It produces a report, as shown below, making it much easier to focus on the wait events of interest.

 Elapsed times include waiting on following events: 
Event waited on Times Max. Wait Total Waited
------------------------------ Waited ---------- ------------
SQL*Net message to client 1184 0.00 0.00
db file sequential read 2441 0.08 8.50
direct path write 38 0.00 0.00
direct path write temp 38 0.00 0.00
direct path read 143 0.00 0.00
direct path read temp 143 0.00 0.03
SQL*Net message from client 1184 23.87 226.31
*********************************************************************



Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net