Mar 12, 2013 4:22:00 PM by Quest Software
![]() |
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.
![]() |
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:
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 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.
![]() |
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:
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:
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 - Improving database I/O
![]() |
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:
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.
Tags: Spotlight on Oracle Wiki
Written by Quest Software
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.