Tuning a Production System

Follow these steps to tune your existing system:

1. Tune the operating-system hardware and software.

2. Identify performance bottlenecks by querying the V$SESSION_WAIT view. This dynamic performance view lists events that cause sessions to wait.

3. Determine the cause of the bottleneck by analyzing the data in V$SESSION_WAIT.

4. Correct the problem.

Tuning the Operating System

Subsequent chapters of this manual describe some memory and I/O operating system issues. Unfortunately, this manual cannot cover every operating system-specific topic.

Additional Information: Tuning your operating system is different for every operating system Oracle runs on. Refer to your operating-system hardware/software documentation as well as your Oracle operating system-specific documentation for more information on tuning your operating system.

Identifying Performance Bottlenecks

Certain events described in this table are more meaningful than others; you need to collect historical data to determine if the wait times are unusually slow for certain events. The following example illustrates the contents of V$SESSION_WAIT:

SQL> SELECT sid, event, wait_time

  2     FROM v$session_wait

  3     ORDER BY wait_time, event;

 SID EVENT                      WAIT_TIME

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

   9 Null event                         0

 174 client message                     0

 164 db file sequential read            0

   6 rdbms ipc message                  0

   5 smon timer                         0

 108 db file sequential read            1

 234 db file sequential read            1

  63 log file sync                      1

  33 log file sync                      1

 201 virtual circuit status             1

  89 db file sequential read            2

  60 db file sequential read            2

  19 log file sync                      2

 166 log file sync                      2

 233 db file sequential read            3

 226 db file sequential read            3

 170 log file sync                      3

 130 db file sequential read            4

  95 db file sequential read           11

   1 pmon timer                       300

 205 latch free                4294967295

 207 latch free                4294967295

 209 latch free                4294967295

 215 latch free                4294967295

 293 latch free                4294967295

 294 latch free                4294967295

 117 log file sync             4294967295

 129 log file sync             4294967295

  22 virtual circuit status    4294967295

The unusually large wait times for the last several events signify that the sessions are currently waiting for that event. As you can see, there are currently several sessions waiting for a latch to be free and for a log file sync.

This information is only part of the picture. You need the other columns in V$SESSION_WAIT as well as information in the other dynamic performance views before you can determine the cause of the performance problem. The previous query does, however, point you in the right direction. We can see from this example that the redo log files and certain latches could be causing response time problems. The next section describes how to diagnose the cause of the problem.

The following table describes some of the more important events in V$SESSION_WAIT and where in this manual those types of problems are addressed. Use this table to locate the sections on troubleshooting the specific performance problems you encounter.

Event	Possible Cause	Corrective Action/ See:
free buffer waits	DBWR not writing frequently enough	increase number of checkpoints,
latch free	contention for latches	dependent upon the latch,
buffer busy waits	I/O contention, Parallel Server contention for data blocks	tune I/O and distribute data effectively, chapter 9, Oracle7 Parallel Server manual
db file sequential read	I/O contention	tune I/O and distribute data effectively, chapter 9 improperly tuned SQL statements, chapter 7
db file scattered read	too many table scans	tune I/O and distribute data effectively, chapter 9 improperly tuned SQL statements, chapter 7
db file parallel write	not checkpointing frequently enough	increase number of checkpoints,
undo segment extension	too much dynamic extension/shrinking of rollback segments	prevent rollback segments from growing or shrinking by appropriately sizing them,
undo segment tx slot	not enough rollback segments	create the appropriate number of rollback segments,

Table 1 - 1. Causes of Events in V$SESSION_WAIT

Determining the Cause of the Problem

• CPU

• memory

• I/O

• contention for latches or other structures

Examine the values in P1, P2, and P3 of V$SESSION_WAIT for the latch problems we discovered in the previous section. Use the following query to obtain additional information about the events that current sessions are waiting on.

SQL> SELECT sid, event, p1text, p1, p2text, 

2		 p2, p3text, p3, wait_time 

3     FROM v$session_wait  

4     WHERE event = 'latch free' 

5		 AND wait_time> 40000000;

 SID EVENT            P1TEXT          P1  P2TEXT        P2 P3TEXT           P3   WAIT_TIME

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

 205  latch free      address     50367360 number       15 tries             1  4294967295

 207  latch free      address     50367260 number       16 tries                4294967295

 209  latch free      address     50367360 number       15 tries             1  4294967295

 215  latch free      address     50367360 number       15 tries                4294967295

 293  latch free      address     50367260 number       16 tries             1  4294967295

 294  latch free      address     50367260 number       16 tries                4294967295

P2 tells us that three sessions each are currently waiting on latch numbers 15 and 16. We can find the names of those latches by querying V$LATCH.

SQL> SELECT latch#, name 

2	  FROM v$latch 

3	  WHERE latch# IN (15,16);

LATCH# NAME

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

    15 redo allocation

    16 redo copy

You can now see that the redo allocation and redo copy latches are the latches for which these sessions are waiting. The next step is to examine redo latch activity. Use the following query to determine the redo latch activity:

SQL> SELECT ln.name, gets, misses,

  2         immediate_gets, immediate_misses

  3     FROM v$latch l, v$latchname ln

  4     WHERE ln.name IN('redo allocation', 'redo copy')

  5         AND ln.latch# = l.latch#;

NAME                 GETS  MISSES IMMEDIATE_GETS IMMEDIATE_MISSES

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

redo allocation  32624433  147985              0                0

redo copy             527     227       47615279              540

The following table illustrates the significant ratios for the statistics:

	redo allocation	redo copy
MISSES/GETS	4.5%	43%
IMMEDIATE_MISSES/ (IMMEDIATE_GETS + IMMEDIATE_MISSES)	0%	<0.01%

If either the ratio of MISSES to GETS or the ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_GETS and IMMEDIATE_MISSES is greater than 1%, there is a contention problem for the redo allocation or redo copy latches. In the example, the ratio of MISSES to GETS is greater than 1% for both the redo allocation and redo copy latches, so you must take corrective action.

Correcting the Problem

In the example, the redo allocation and redo copy latches are suffering from contention. To correct the redo allocation latch problem, you must decrease the value of the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE. To correct the redo copy latch problem, you must increase the value of the initialization parameter LOG_SIMULTANEOUS_COPIES.

Each chapter in this manual suggests corrective action for the various performance problems you may encounter. You must read the chapter concerning your problem to properly diagnose and solve the performance problem.