Introduction to Tuning Oracle7 / Oracle8 / 8i / 9i (Doc ID 61998.1)

Introduction

This is the first in a set of articles which give an introduction to tuning Oracle. There is already much material on this subject but the aim here is to give a path to obtaining quick results by assessing where time is being spent rather than looking at the meanings of lots of different statistics.

Note:

It is assumed you have a reasonable working knowledge of Oracle.
Please read this article before reading any other article in the set as it provides the ground work for the entire article set.
The articles cover Oracle versions 7.1 through to Oracle9i.
Some internal tables are referred to which only exist in Oracle 7.3 onwards. The definitions of such tables can change between releases so minor adjustments to statements may be needed. In particular note that X$ tables are not officially supported but some are referred to.
Be prepared to have to look at the application code - in the majority of cases the largest improvements in performance can be made at application level.

1. Why are you tuning ?

Always determine WHY you need to do some tuning. Usually you are interested in user response times and/or batch job durations. It may be there is a 'problem' at certain times of the day or that performance is always perceived to be bad. It may be only certain user functions perform badly, or all functions seem slow. Most systems exist to serve their users so you will be well prepared if you understand the users view of the system.

Be sure you understand any problem from the users viewpoint - spend time with the users to ensure the real issue is looked into. Eg: If two statements which are used by the most common user transactions have poor execution plans this may be reported as "everything is slow all the time" but closer inspection may reveal that certain operations give perfectly acceptable response times.

2. Think in terms of time

Once you start to think in terms of response time or batch duration you have a sound basis to determine the impact of any changes you can suggest. The basis of tuning in this article is to determine where time 'goes' for tasks which need to run faster. Once you know where the time goes you can decide which bits of that time you can influence and which will give you most potential gain. The approach has 4 steps which can be repeated over and over:

Determine where the time is being spent
Drill down to get some detail on why
Assess if any improvements are possible
Implement any changes and repeat from step 1

3. What can Oracle tell you about time ?

A client application process generally talks to an Oracle shadow process using a half-duplex protocol. Each shadow process is typically in one of three states:

    a. Idle waiting for something to do
    b. Running code - ie: using CPU or on a run queue
    c. Waiting, either:
	  i.  for some resource to become available
       or ii. for an activity to complete that it has requested

To clarify this here are some examples:

    a. Idle
        User shadow process is waiting for a data packet from the user 
        telling them what to do next or providing information to allow 
        them to continue.

    b. Running code
        The process expects to be on a run queue for a CPU.
        Oracle itself does not know if it is on-CPU or just on a run queue.

    c. i.  Waiting for a resource to become available
            Like an enqueue (lock) or a latch

    c. ii. Waiting for an activity to complete
            Like an IO read request, or waiting for LGWR to write redo to disk.

4. Essential Points

The init.ora parameter TIMED_STATISTICS must be enabled (set to TRUE) in order to see how much time is spent in each of the above states.

The times recorded by Oracle only have a resolution of 1/100th of a second (10mS). This is usually sufficient to help determine where time is going. As of Oracle9i some times are available to microsecond accuracy.

5. Wait Events

Just knowing the breakdown of time into the above 3 categories is not very useful so Oracle has a set of 'Wait Events' for activities in 'a' and 'c', and can record CPU utilization for 'b'. This is best illustrated with a simplified example of few seconds in the life of an Oracle shadow process:

 State   Notes...
   ~~~~~   ~~~~~~~~ IDLE    Waiting for 'SQL*Net message from client'.
           Receives a SQL*Net packet requesting 'parse/execute' of a statement
   ON CPU  decodes the SQL*Net packet.
   WAITING Waits for 'latch free' to obtain the a 'library cache' latch
           Gets the latch.
   ON CPU  Scans for the SQL statement in the shared pool, finds a match, 
           frees latch , sets up links to the shared cursor etc.. & begins to 
           execute.
   WAITING Waits for 'db file sequential read' as we need a block which is 
           not in the buffer cache. Ie: Waiting for an IO to complete.
   ON CPU  Block read has completed so execution can continue.
           Constructs a SQL*Net packet to send back to the user containing
           the first row of data.
   WAITING Waits on 'SQL*Net message to client' for an acknowledgement that the
           SQL*Net packet was reliably delivered.
   IDLE    Waits on 'SQL*Net message from client' for the next thing to do.

If we can apportion timings to each of the above steps then one can get an indication of what tuning is possible. This applies at individual statement level, session level or system-wide - Oracle can provide useful information at any of these levels:

 At an Instant in time: V$SESSION
	V$SESSION_WAIT  Statement level: SQL_TRACE or DBMS_SUPPORT.SET_TRACE output  Session level: SQL_TRACE or DBMS_SUPPORT.SET_TRACE output
        V$SESSION_EVENT
        V$SESSTAT  System-wide: V$SYSTEM_EVENT
        V$SYSSTAT

6. A Point In Time View

< > and <> give a point in time indication the current state of each session:

Eg:

 SELECT sid, status FROM V$SESSION;

shows whether each session is considered ACTIVE or INACTIVE. An INACTIVE session is generally waiting for a request from the client. In most cases this will appear in V$SESSION_WAIT as waiting for "SQL*Net message from client" with a WAIT_TIME of ZERO.

For ACTIVE sessions

    SELECT sid, wait_time, event FROM V$SESSION_WAIT;

shows the state of each session at an instant in time:

 WAIT_TIME	Meaning 0		Session is currently WAITING for the specified EVENT (See #1 below) !=0	Session is currently on CPU (but see the notes below).
		The EVENT listed is the last thing this session waited for. 
		WAIT_TIME then indicates how long the session waited for this 
		last event:
      		  -1	The session waited a short time for the listed event
      		  -2	We do not know how long we waited 
			(Used when TIMED_STATISTICS=false)
      		  >0	Actual time waited in 1/100ths of a second.  #1 Note: In Oracle9i 9.0.1 V$SESSION_WAIT may show WAIT_TIME of 0
             when the session is not actually waiting but is on CPU due to Bug:2117360. V$SESSION_WAIT.STATE will show a value other
             than "WAITING" in this case.

Typically there will only be a few sessions "on CPU" at any point in time. If you run the above statement the session running the SELECT should show as "on CPU" (WAIT_TIME!=0).

NB:If an application uses multiple sessions or performs session switching then it is possible for WAIT_TIME to be non-zero while the session is actually INACTIVE. This occurs when the session is not the current session for the client and so V$SESSION_WAIT shows the last thing that the session waited for but the session itself is now INACTIVE. You may see this if:

Using Oracle Forms version 4.0 (or higher) or similar Oracle client tools.
Using Oracle XA
Session switching in Oracle8 OCI

The V$SESSION_EVENT and V$SYSTEM_EVENT views show the total time spent waiting for each wait-event as session and system-wide levels.

7. Onwards...

The rest of the articles in this set are broken into 3 main areas:

Collecting information and analyzing it to determine where TIME is going to.
1. Systemwide Tuning for Oracle7 and Oracle8 (using UTLBSTAT/UTLESTAT) - Note:62161.1
2. Tracing User Sessions in Oracle7 and Oracle8 - Note:62160.1
3. Identifying causes of Database Hangs in Oracle7 and Oracle8
Describing important features and parameters that affect particular components within Oracle.
1. Issues affecting the Shared Pool in Oracle7 , Oracle8 and 8i - Note:62143.1
2. The Buffer Cache and DBWR - Note:62172.1
3. Issues affecting Redo (LGWR and ARCH) - Note:147471.1
Useful scripts and tools
1. The DBMS_SUPPORT package - Note:62294.1

References

BUG:2117360 - V$SESSION_WAIT.WAIT_TIME CAN BE 0 WHEN SESSION IS NOT WAITING
NOTE:147471.1 - Tuning the Redolog Buffer Cache and Resolving Redo Latch Contention
NOTE:62143.1 - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
NOTE:62160.1 - Tracing Sessions in Oracle Using the DBMS_SUPPORT Package
NOTE:62161.1 - Systemwide Tuning using UTLESTAT Reports in Oracle7/8
NOTE:62172.1 - Understanding and Tuning Buffer Cache and DBWR