Tuning Your Database

Spotlight on Oracle supports an approach to Oracle performance tuning that could be described as "tuning by bottleneck". Spotlight on Oracle alerts you if any component of the Oracle architecture is forming a bottleneck. Additionally, Spotlight on Oracle provides tools that allow you to identify and rectify any inefficiency in your database's configuration.

In general, you tune your Oracle instance by reiteratively identifying bottlenecks, contention, and critical resources, and by using the advice in this section to remove the bottleneck or contention or improve the performance of the resource.

Spotlight on Oracle – Detailed Tuning Techniques

Detailed Tuning Techniques

This section outlines detailed techniques for improving the performance of Oracle databases. The following techniques are explained:

Spotlight on Oracle – Reducing Parse Overhead (SOO11)

Reducing Parse Overhead

Parsing is the process of preparing your SQL statement for execution. This process is comparable to the process a language compiler or interpreter must undertake in order to translate high-level statements into machine code. 

The parse process includes the following phases:

  • Checking that the SQL statement is syntactically valid (that is, that the SQL conforms to the rules of the SQL language, and that all keywords and operators are valid and correctly used).
  • Checking that the SQL is semantically valid. This means that all references to database objects (such as tables and columns) are valid.
  • Checking security (that is, that the user has permission to perform the specified SQL operations on the objects involved).
  • Determining an execution plan for the SQL statement. The execution plan describes the series of steps that Oracle performs in order to access and update the data involved.

Parsing can be an expensive operation. Its overhead is often masked by the greater overhead of high I/O requirements. However, eliminating unnecessary parsing is always desirable.

The parse/execute ratio reflects the ratio of parse calls to execute calls. Because parsing is an expensive operation, it is better to parse statements once and then execute them many times. High parse ratios (greater than 20%) can result from the following circumstances:

  • If literals, rather than bind variables, are used as query parameters, the SQL must be re-parsed on every execution. You should use bind variables whenever possible, unless there is a pressing reason for using column histograms.
  • Some development tools or techniques result in SQL cursors being discarded after execution. If a cursor is discarded, then the parse is required before the statement can be re-executed.

If an application is discarding cursors, it may be possible to relieve some of the parse overhead by creating a session cursor cache. You can do this by using the SESSION_CACHED_CURSORS configuration parameter. This setting allows Oracle to maintain a cache of SQL statements in the session memory. If a session requests a parse of a statement that it has already parsed, then it might be found in the cache and re-parsing is avoided.

Spotlight on Oracle – Tuning Rollback Segments (SOO12)

Tuning Rollback Segments

Rollback segments store original (or before-image) copies of database blocks that have been changed, but not committed. Rollback segments contain the information that must be restored if a ROLLBACK command is issued.

The configuration of your rollback segments can have an important effect on the performance of your database, especially for transactions that modify data. Any operation that modifies data in the database must create entries in a rollback segment. Queries that read data that has been modified by uncommitted transactions also needs to access data within the rollback segments.

Poorly-tuned rollback segments can have the following consequences:

  • If there are too few rollback segments, transactions may need to wait for entries in the rollback segment.
  • If rollback segments are too small, they may have to grow dynamically during the transaction and later shrink back (if the rollback segment has an optimal size specified).

As well as these performance-related problems, poorly-tuned rollback segments can lead to transaction failure (failure to extend rollback segment), or query failure (snapshot too old).

The following guidelines serve as a starting point for rollback segment configuration for a transaction-processing environment:

  • The number of rollback segments should normally be at least one quarter of the maximum number of concurrently active transactions. In batch environments, this can mean allocating a rollback segment for each concurrent job.
  • Set OPTIMAL or MINEXTENTS so that the rollback segment has at least ten to twenty extents. This minimizes wastage and contention when a transaction tries to move into an already occupied extent.
  • Make all extents the same size.
  • Allow ample free space in the rollback segment tablespace for rollback segment expansion. Large, infrequent transactions can then extend a rollback segment when required. Use OPTIMAL to ensure that this space is reallocated when required.

It is very difficult to determine the optimal setting for rollback segments by theory alone. Rollback segments should be carefully observed and storage adjusted as required.


Spotlight on Oracle – Reducing lock contention


Spotlight on Oracle – Improving database writer performance  

Spotlight on Oracle – Relieving latch contention 


Spotlight on Oracle – Improving database I/O  


Spotlight on Oracle – Dealing with MTS contention (SOO5)

Dealing with MTS contention

When multi-threaded servers are implemented, multiple Oracle clients share a smaller number of server processes. This can save memory and, in some cases, improve performance. However, if the number of shared servers is too small, then client sessions may queue for an available server. Response time and throughput might suffer dramatically.

A similar problem can occur if there are insufficient dispatchers.

If Spotlight on Oracle detects a bottleneck in the Shared servers or dispatchers, then an alarm (such as the Multi-threaded server alarm) becomes current on the appropriate component within the Server Processes panel. If the alarms persist, you should alter the setting forMTS_MAX_DISPATCHERS or MTS_MAX_SERVERS to ensure that sufficient processes are available for your workload. You could also consider using dedicated servers.

You can view information about MTS activity on Spotlight on Oracle’s Server activity tab.


Spotlight on Oracle – Dealing with Wait Events (SOO6)

Dealing with wait events

Whenever an Oracle session is not actually consuming or waiting for CPU resources, it will usually be in one of a number of wait events. For instance, a session may be waiting for some I/O request to be performed for free space in the SGA, for network traffic, or for an internal Oracle resource such as a latch. Some waits, such as those for datafile or log file I/O are normal and unavoidable (although you may be able to reduce their magnitude). Other waits, such as those for latches or buffers in the SGA may indicate inefficiency or bottlenecks.

In a perfect Oracle implementation, the Oracle server process is able to perform its tasks using its own resources without experiencing any delays. However, in reality, Oracle sessions often wait on system or database requests, or for resources to become available.

During a typical transaction, the Oracle session may need to wait for various resources to become available, such as:

  • While the application is idle, the server process is waiting for a message from the client.
  • When the server process parses a new SQL statement, and the statement has not previously been executed, it has to acquire a latch to add the new statement to the library cache. If the latch required is held by another session, the server process may have to wait for the latch to become available.
  • The server process also has to acquire a latch when executing a SQL statement held in the shared pool. It may have to wait on the latch if it is currently held by a different session.
  • When accessing a data block in the buffer cache, the server process has to change the location of the block on the least recently used (LRU) list. This requires obtaining and possibly waiting for the appropriate latch.
  • If the block is not in the buffer cache, the session has to issue and wait for an I/O request to obtain the block. Moving a new block into the buffer cache also requires a latch that might be unavailable and cause a wait.
  • Changing the data block requires obtaining a latch both to change the block itself and to make an entry in the redo log buffer. Additionally, if there is insufficient free space in the redo log buffer, the session needs to wait for the Logwriter process to make space available.
  • When a COMMIT is issued, the session must wait for the Logwriter process to write the blocks in question to the redo log file.
  • The Logwriter session itself may need to wait if the redo log is full, and the next redo log has an outstanding checkpoint or archive operation outstanding.

There are many reasons why an Oracle session may need to wait. Some of these waits (such as waiting for I/O operations) are inevitable. However, you can reduce waits in many cases by tuning I/O, the buffer cache, or the SQL involved. Other operations (such as waiting for latches) may indicate inefficiencies in your configuration and opportunities for further tuning.


Start the discussion at forums.toadworld.com