Spotlight on Oracle – Tuning Your Database

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

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.

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.