Introduction

Note:

1. It is assumed you have a reasonable working knowledge of Oracle.
2. Please read this article before reading any other article in the set as it provides the ground work for the entire article set.
3. The articles cover Oracle versions 7.1 through to Oracle9i.
4. 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.
5. 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 ?

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

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

3. What can Oracle tell you about time ?

    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

    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 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

 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.

 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

Eg:

 SELECT sid, status FROM V$SESSION; 

For ACTIVE sessions

    SELECT sid, wait_time, event FROM V$SESSION_WAIT; 

 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.  

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...

• 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