Oracle -- Common Performance Tuning Issues

TROUBLESHOOTING GUIDE
Common Performance Tuning Issues

Table of Contents

1. Introduction
2. Shared Pool and Library Cache Performance Tuning
3. Buffer Cache Performance Tuning
4. Latch Contention
5. Redo Log Buffer Performance Tuning
6. Rollback Segment Performance Tuning
7. Temporary Tablespace Performance Tuning
8. Checkpoint Performance Tuning
9. Query Performance Tuning
10. Import Performance Tuning
11. STATPACK Utility
12. Utlbstat/Utlestat Utility

1. Introduction

This document covers some of the most common tuning scenarios. More specific
information on each tuning area is available through the links provided.

2. Shared Pool and Library Cache Performance Tuning Note 62143.1

Oracle keeps SQL statements, packages, object information and many other items
in an area in the SGA known as the shared pool. This sharable area of memory
is managed as a sophisticated cache and heap manager rolled into one. It has 3
fundamental problems to overcome:

- The unit of memory allocation is not a constant. Memory
allocations from the pool can be anything from a few bytes to
many kilobytes

- Not all memory can be 'freed' when a user finishes with, as the
aim of the shared pool is to maximize sharability of
information.

- There is no disk area to be able to page out to so this is not
like a traditional cache where there is a file-backing store.
Only "recreatable" information can be discarded from the cache
and it has to be re-created when it is next needed.

Here are some tips in tuning the shared pool:

· Flushing the Shared Pool will coalesce small chunks of memory. When
the shared pool is highly fragmented, this may temporarily restore
performance. To flush the shared pool issue: alter system flush shared_pool;
Please note that executing this statement will cause a spike in performance
while the objects are reloaded and should be done when the database is not
being heavily used.

· Make sure that OLTP application uses "bind variables".
This is not that important for DSS.

· Make sure that the library cache pinhitratio is > 95%

. Increasing the size of the shared pool is not always the answer for poor
hitratios.

For more information reference: Note 62143.1

3. Buffer Cache Performance Tuning Note 62172.1

The database buffer cache holds copies of data blocks read from disk.
Since the cache is usually limited due to memory constraints, all the data on
the disk cannot fit in the cache. When the cache is full, subsequent cache
misses cause Oracle to write data already in the cache to disk. A subsequent
access to the data written to disk results in a cache miss.

Here are some tips in tuning the buffer cache:

· Enable Buffer Cache Advisory in order to size your Buffer Cache correctly.
Please refer to Note 148511.1 for more details.

Avoid the following

· 'cache buffers lru chain' latch contention

· Large "Average Write Queue" length

· Lots of time spent waiting for "write complete waits"

· Lots of time spent waiting for "free buffer waits"

For more information reference: Note 62172.1

4. Latch Contention Note 22908.1

Latches are low level serialization mechanisms used to protect
shared data structures in the SGA. A latch is a type of a lock
that can be very quickly acquired and freed. The implementation
of latches is operating system dependent, particularly in regard
to whether a process will wait for a latch and for how long.

Here are some of the important latches to tune:

- Redo Copy/Allocation Latch Note 147471.1
- Shared Pool Latch Note 62143.1
- Library Cache Latch Note 1012049.6

For more information reference: Note 22908.1

5. Redo Log Buffer Performance Tuning Note 147471.1

LGWR writes redo entries from the redo log buffer to a redo log file.
Once LGWR copies the entries to the redo log file the user process can
over write these entries. The statistic "redo log space requests" reflects
the number of times a user process waits for space in the redo log buffer.

Here are some tips in sizing the redo logs:

· The value of "redo log space requests" statistic in v$sysstat should be near 0.

· Size your redo appropriately. The recommendation is to have the redo
log switch every 15-30 minutes.

Using the options UNRECOVERABLE in Oracle7 and NOLOGGING in Oracle8 you can avoid
redolog entries generation of certain operation to improve the performance.
Operations like: index creation, create table as select,SQL*Loader operation, etc.
can be easily rebuild without having redolog entries available. See Note 147474.1
for further detail.

For more information reference: Note 147471.1

6. Rollback Segment Performance Tuning Note 62005.1

The Oracle database provides read consistency on rows fetched for
operations such as SELECT, INSERT, UPDATE, and DELETE against any
database object. Rollback segments are used to store undo transactions
in case the actions need to be "rolled back" or the system needs to
generate a read-consistent image from an earlier time.

Here are some tips in sizing the rollback segments:

· It is recommended to have at least 1 rollback segment for every 4
transactions.

· One large rollback segment is recommended for long running queries.

For more information on tuning Rollback segments read: Note 62005.1

7. Temporary Tablespace Performance Tuning Note 73439.1

In RDBMS release 7.3, Oracle introduced the concept of a temporary
tablespace. This tablespace would be used to hold temporary objects,
like sort segments. Sort segments take their storage parameters from
the DEFAULT STORAGE (NEXT) clause of the tablespace in which they reside.

Here are some tips in tuning the temporary tablespace:

· If there is a lot of contention for the Sort Extent Pool latch, even in
the stable state, then you should increase the extent size by changing
the NEXT value of the DEFAULT STORAGE clause of the temporary tablespace.

· If there is a lot of contention for the Sort Extent Pool latch and if the
wait is the result of too many concurrent sorts, you should increase the
SORT_AREA_SIZE parameter so that more sorts stay in memory.

. It is recommended to have the extent size equal to sort_area_size.
Here is an example why. Say your extent size = 500K and sort_area_size = 1Mg.
Now if there is a sort to the disk, it aquires 2 extents of 500K each and
this could cause performance degradation.

For more information reference: Note 73439.1

8. Checkpoint Performance Tuning Note 147468.1

A Checkpoint is a database event, which synchronizes the data blocks in memory
with the datafiles on disk. A checkpoint has two purposes:

(1) to establish data consistency, and
(2) Enable faster database recovery.

When a checkpoint fails messages must be verified into into the alert.log file.

Here are some tips to tune the checkpoint process:

· The CKPT process can improve performance significantly and decrease the
amount of time users have to wait for a checkpoint operation to complete.

· If the value of LOG_CHECKPOINT_INTERVAL is larger than the size of the redo
log, then the checkpoint will only occur when Oracle performs a log switch
from one group to another, which is preferred. There has been a change in
this behaviour in Oracle 8i.

· The LOG_CHECKPOINTS_TO_ALERT when set to TRUE allows you to log checkpoint
start and stop times in the alert log. This is very helpful in determining
if checkpoints are occurring at the optimal frequency

. Ideally checkpoints should occur only at log swiches.

For more information reference: Note 147468.1

9. Query Performance Tuning Note 33089.1

If queries are running slow consider the following:

· How fast do you want the query to run and is it a reasonable request?
· What is the OPTIMIZER_MODE set to?
· Are all indexes involved in the query valid?
· Is there any other long running query on the database?

In case of CBO:

· Are there statistics on the tables and indexes?
· Were the statistics computed or estimated?

Here are the 2 main diagnostic tools used for query performance tuning

- TKPROF Note 41634.1
- AUTOTRACE Note 43214.1

For more information reference: Note 33089.1

10. Import Performance Tuning Note 93763.1

There is very little consolidated information on how to speed up import when it
is unbearably slow. Obviously import will take whatever time it needs to
complete, but there are some things that can be done to shorten the time it
will take.

For more information reference: Note 93763.1

11. STATPACK Utility Note 94224.1

The STATPACK utility is the next generation of the Utlbstat/Utlesta report which
helps the database administrator to gather statistical information to detect
performance problems. Statspack improves on the existing UTLBSTAT/UTLESTAT performance
scripts collecting more data, including high resource SQL, pre-calculating some ratios,
such as cache hit ratios, per transaction and per second statistics, keeping a
permanent repository which makes historical data comparisons easier and separating
data collection from the report generation.

For more information reference: Note 94224.1

12. Utlbstat/Utlestat Utility Note 62161.1

Bstat/Estat is a set of sql scripts located under your $ORACLE_HOME/rdbms/admin
directory that are useful for capturing a snapshot of system wide database
performance statistics. UTLESTAT creates a second snapshot of these views and
reports on the differences between the two snapshots to a file called
'report.txt'.

Bstat.sql creates a set of tables and views in your sys account, which contain
a beginning snapshot of database performance statistics.

Estat.sql creates a set of tables in your sys account, which contain an ending
snapshot of the database performance statistics and them to a file called
'report.txt'.

Here are some tips:

· Make sure that you have TIMED_STATSTICS set to TRUE (this adds only a very
small overhead to database operations).

. Make sure that the database is up and running for a while before running
utlbstat.

· Run the utbstat.sql and the utlestat.sql from svrmgrl and not sql*plus.

. Make sure that the database is not shutdown while the utlbstat/estat scripts
are running, otherwise the statstics generated are not accurate.

· Run utlbstat/estat at least for 1-3hrs during the period you are trying to
tune the database.

For more information reference: Note 62161.1