Optimize the Application Workload

Bind Variables

SQL statements may be using literal values when they could be using bind variables.

A SQL statement may contain variables that change from execution to execution. These variables are typically parameters to the SQL statement. They define the rows to be processed or new values to be inserted or updated. These variables can be specified either as literals or as bind variables. Using literals to retrieve details for employee 1234 could use the following SQL statement:
SELECT * FROM employees WHERE employee_id=1234

A more efficient and scalable approach is to define the employee_id as a bind variable:

SELECT * FROM employees WHERE employee_id=:empid

The value of the host variable empid will be supplied to the SQL statement when it is executed. When a SQL statement uses bind variables, it is not necessary to re-parse the statement every time the argument changes. When the SQL statement is executed in another session, the matching SQL will be found in the shared pool and parsing will be avoided.

Cursor Management

Determine if cursors are being reused inefficiently.

When executing a SQL statement, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. When an application submits the same SQL statement multiple times, reusing the cursor can improve database efficiency. Applications may not be set up or designed properly to take advantage of cursor reuse.

Shared Pool Size

Determine if overall database performance would benefit from resizing the shared pool.

The shared pool area of the SGA contains two primary areas: the library cache and the dictionary cache. It also contains control structures such as NLS loadable objects, session and process context areas, enqueue (lock) resources, etc. Additionally, multi-threaded server (MTS) and parallel execution (PX) are optional components that prefer to locate their respective memory areas within the large pool when defined. When a large pool is unavailable, the session memory of MTS and the query buffers of PX will reside in the shared pool.

Performance can improve when the shared pool is sized properly to avoid reparsing and reloading.

Reduce Contention

Shared Pool Latch

Determine if significant shared pool latch contention exists.

Shared pool latch contention can occur when the shared pool is sized too large to be effectively managed. This problem is less common starting with Oracle version 8.1.6 when Oracle implemented improved shared pool management architecture. In previous Oracle versions, the shared pool management did little to avoid shared pool latch contention. The overhead to track the memory chunks in a large shared pool impacted the speed at which processes were able to obtain a needed chunk of memory.

Library Cache Latch

Determine if significant library cache latch contention exists.

The library cache latches protect the cached SQL statements and the cached object definitions held in the shared pool library cache. A library cache latch must be obtained before Oracle can add a new statement to the library cache. During a parse request, Oracle searches the library cache for a matching statement. When one is not found, Oracle will acquire the library cache latch, parse the SQL statement, and insert the new SQL. Contention for the library cache latch occurs when an application generates numerous unique, unsharable SQL (when literals have been used instead of bind variables). When library cache latch contention exists, improve the use of bind variables within your application. Misses on this latch can indicate that your application is parsing SQL at a high rate and may be suffering from excessive parse CPU overhead.

Redo Allocation Latch

Determine if significant redo allocation latch contention exists.

Redo allocation latches are used to control the allocation of space for redo entries in the redo log buffer. When redo information is generated, it is written to the log buffer. The redo allocation latch protects against simultaneous writes to the same log buffer memory. There is only one redo allocation latch per instance and only one process can allocate space in the log buffer at a time.

Redo Copy Latch

Determine if significant redo copy latch contention exists

When redo information is generated, it is first written to the log buffer before being written to disk. When the LOG_SIMULTANEOUS_COPIES (renamed _LOG_SIMULTANEOUS_COPIES in 8i) parameter is set to a value greater than 0, Oracle uses redo copy latches to reduce contention for the redo allocation latch. The redo allocation latch is used to prevent another process from writing in the same location of the log buffer. There is only one redo allocation latch per instance and only one process can allocate space in the log buffer at a time.

Cache Buffers Chains (CBC) Latch

Determine if significant cache buffers chains latch contention exists.

Cache buffers chains (CBC) latches enforce synchronized updates to data in the Oracle buffer cache. Data blocks in the buffer cache are managed in singularly linked lists of data block headers (sometimes referred to as buckets). Data block headers contain information about the data block including the memory address of the block in the buffer cache. To provide efficient data block access, Oracle computes a hash algorithm on the dat block address (DBA - an encoding of file and block number) to identify the cache buffers chain (one of many) that may contain the block. The chain is then scanned to find the corresponding header. When the header is located, the buffer cache address is available and the block can be retrieved. This process is much faster than scanning the entire buffer cache for the requested block.

While Oracle scans and updates a cache buffers chain, a CBC latch protects the chain to ensure that block headers are not being added or removed from the list simultaneously by other users. Optimally, other users should not have to wait long to access the same cache buffers chains latch.

CacheBuffers LRU Chain Latch

Determine if significant cache buffers LRU chain latch contention exists.

The cache buffers LRU chain latch is held when updates are made to the LRU (Least Recently Used) lists. Two LRU lists are used to track database blocks. The first list, the LRUW, tracks "dirty" (modified blocks not yet written to disk) block buffers. The second list, the LRU, tracks "clean" (unmodified) block buffers. Both lists track the least recently used blocks. When a block is used or freed, it goes to the top of the list, and the blocks below are moved down the list. After the dirty blocks on the LRUW reach a certain level, they are written to disk and placed on the LRU list containing clean blocks. When a clean block is needed, the LRU list is searched. When a clean block is modified, it is placed on the LRUW list for writing by the DB writer.

Cache buffers LRU chain latch contention is caused when the LRU and LRUW lists are updated and the chains of buffers in each hash bucket are too numerous to be scanned quickly. Contention is also caused when the volume of transactions overloads the block tracking on the LRU lists. The solution to either of these cases is to allocate the resources needed to update the LRU lists quickly.

Locking

Determine if significant lock contention exists.

Lock (enqueue) waits occur when a process must wait to update data when another process is already updating it. Locks are mechanisms used to provide ordered access when updating data. Oracle also uses locks for maintaining internal data structures and processes may have to wait for access to these internal locks as well (for instance, the Space Transaction enqueue).

When the database is well tuned and the application design sound, lock waits should be negligible.

Tables Experience Lock Waits

Determine if transaction lock waits for tables were observed.

The purpose of a lock within the database is to protect a database resource (in this case a table) that may be needed for a relatively long time as compared to latches which are expected to be held only briefly. The Oracle database locking mechanism allows multiple sessions to wait in line for a given table if that table is currently locked by another session in a mode that is incompatible with the lock being requested. When entire tables are being locked unnecessarily, contention on such tables may result.

Space Transaction Waits

Determine if significant space transaction lock wait exists. If so, identify users that have permanent tablespaces defined for use as temporary storage.

When Oracle modifies the segments allocated to objects, it grabs and holds the space transaction lock until the modification completes. Excessive segment modification is especially noticeable when permanent tablespaces are assigned for use as temporary storage, significant disk sort operations are occurring in those tablespaces, and the default size for the tablespace is set too low. It may also occur when rollback segments are growing and shrinking excessively.

Oracle addressed this issue with the introduction of tablespaces of type TEMPORARY and further improved the situation with the introduction of Temporary tablespaces in version 8i.

Assigned Temporary Tablespace

Each database user is assigned a tablespace to use for temporary storage operations. Temporary storage is necessary when performing large sorts, large hash joins, and other operations. However, sort operations are typically the primary use for temporary storage. When sort operations are too large to be done entirely in memory, temporary disk storage is utilized to store intermediate sort results. Sort operations are necessary for such purposes as completing ORDER BY or GROUP BY operations, sort-merge join operations, and for index creation.

When creating a tablespace, it can be designated for use as temporary-only storage. By doing this, Oracle more efficiently manages the utilized space for temporary purposes. Temporary tablespaces should always be specified when assigning tablespaces to users for temporary space usage.

Redo Log Disk Configuration

Determine if there are enough redo log disks defined and if they are optimally sized.

When a transaction is committed, a physical write to the redo log file must occur. The write must complete before the COMMIT returns control to the user and therefore, the writing of the redo log imposes a limit to throughput of update intensive applications. Redo log I/O is optimized when the log is located on a dedicated device where there is no contention with other processes. By not having other processes using the disk device, the disk head maintains its position while awaiting the next write request. Thus, the disk does not need to take the time to "seek" before performing the next write and thus write times are minimized.

Number of Redo Logs

Determine if the database has a sufficient number of redo logs.

A log switch checkpoint must complete before a redo log group can be used. This checkpoint ensures that all transactional information in the redo log buffer has been written to disk.

When in ARCHIVELOG mode, redo log groups must be archived before they can be reused. When the archiving process is sufficiently slow (usually due to disk device contention) and the archiver has not yet completed archiving a redo log group, the database will halt and transactions will wait for the archive process to complete before allowing the LGWR (log writer) process to re-use a redo log group.

The database cannot be modified before the checkpoint or the archive processes have completed. Avoiding these waits is therefore very important to ensure adequate throughput and response time.

The best way to guarantee that you do not experience waits for checkpoints or for archiving is to ensure that there are enough redo log groups. This allows checkpoints and archiving to complete before the logs are scheduled for reuse.

Redo Log Size

Determine if redo logs are sized too small.

When a redo log is full, Oracle must perform a "log switch." Redo logs should be large enough to avoid too frequent log switches and any corresponding log switch waits.

Log switch waits can occur under the following conditions:

• The log switches are too frequent.

• The log switch checkpoint is not complete.

• The database is in ARCHIVELOG mode and the log being switched to is not yet archived.

Redo logs should be sufficiently large (and numerous) to avoid these conditions.

Redo Log Buffer Size

Determine if resizing the redo buffer log will improve performance.

The redo log buffer stores changes that will eventually be written to the redo log files. It buffers these writes so that the user session can continue working without requiring a disk I/O. When a COMMIT is issued, changes in the redo log are written to a disk by the log writer before the COMMIT is completed. When the log buffer reaches 1/3rd full, the log is written to disk even without a COMMIT.

The redo log buffer should be sized properly to minimize the time waiting for writes to the log file to complete.

Sequence Cache Size

Determine if sequence generators have an optimal cache size.

Oracle sequence number generators utilize a built-in caching mechanism to gain their substantial efficiency over an approach of storing and retrieving sequence values from a user table. This is because reading and updating a sequence value in memory is much faster than updating one stored on disk. Oracle sequence generators are cached in the shared memory area (the SGA). By default, 20 numbers are cached. Once the 20 cached numbers are used by the application, Oracle caches another 20 values and updates Oracles internal sequence table. When sequence numbers are allocated at a high rate, performance can be improved by increasing sequence cache size. A larger cache size reduces the number of required read/writes to the sequence table.

Increasing the cache size does not consume additional memory in the SGA. However, it does increase the number of sequence numbers that can be lost if the cache is flushed or the database shutdown. Most applications do not require a complete set of contiguous numbers and therefore, lost sequence numbers do not pose a problem. If a specific application does require complete accounting for a set of sequence numbers, take care in designing the application code to avoid loss of numbers, including when transactions are rolled back.

Buffer Busy Wait

Determine if database buffers are experiencing contention.

Buffer busy waits happen when multiple sessions want concurrent access to a block in the buffer cache. The modifying session marks the blocks header with a flag letting other users know a change is taking place and to wait. This temporary blocking ensures the reader has a coherent image of the block (with either the before or after image). The two main cases where this wait can occur are:

• Another session is reading the block into the buffer

• Another session holds the buffer in an incompatible mode

While the block is being changed, the block is marked unreadable. The changes should last under a few hundredths of a second. A disk read should be under 10 to 20 milliseconds and a block modification should be under one millisecond. Many buffer busy waits need to occur before performance degradation is noticeable.

However, in a problem situation, there is usually a hot block, such as the first block on the free list of a table with high concurrent inserts. Users insert into that block at the same time, until the block exceeds PCTFREE, then users insert into the next free block on the list, and so on.

Description of Freelists

Freelists are structures which are included within each table. They list the blocks that are free to be used for inserts. These structures are maintained in the table header block (unless multiple FREELIST_GROUPS are defined). Multiple freelists can be configured with the FREELISTS clause in the CREATE TABLE and (in 8.1.6) the ALTER TABLE statements. Configuring multiple freelists is an important optimization for tables that are going to be subjected to high rates of concurrent inserts.

A buffer busy wait occurs when an oracle session wants to modify or pin a buffer (copy of a database block in the SGA) but cannot because another session holds the buffer in an incompatible mode. The V$WAITSTAT virtual table records the number and duration of "buffer busy" waits, broken down by the class of buffer for which waits are occurring.

When there are insufficient freelists, multiple sessions are allocated the same block in which to perform their insert When they both insert into this data block concurrently, one session performs the insert and the other experiences a "buffer busy wait" on "data block".

Rollback Segments

Determine if sufficient rollback segments are defined.

When Oracle transactions make database changes, the information required to undo those changes is written to a rollback segment. Oracle ensures that the available rollback segments are evenly used. However, when too many simultaneously active database transactions are using the same rollback segment, the generation of rollback information can be inefficient.

Cluster Interconnect Latency

Determine if the current workload across the interconnect in a Real Application Cluster (RAC) environment is an indicator of any network latency.

RAC uses cluster interconnects to transfer blocks between the nodes participating in the cluster. A block transfer occurs when a user session connected to one instance requests a block being held in the cache of another instance. This feature of RAC to transfer information from the cache of one instance to the cache of the requesting instance is called cache fusion.

The performance of the cluster interconnects is crucial to the performance of the RAC cluster and more specifically to the movement of cached data between instances. Its performance is measured by determining the average time a block takes to reach the requested node i.e., from the moment that a block was requested by an instance to the moment the requesting instance received the block. As in any application or database instance, occasional spikes of user activity are expected. However, when the average of such spikes remains high for an extended period of time, it could indicate a correctable performance degradation of the database cluster.

Minimize Physical Disk I/O

Buffer Pools

Determine if resizing the buffer cache will improve performance.

Reading a data block from a physical disk device is thousands of times slower than reading it from memory. A disk read is avoided when a data block is already in the buffer cache. Avoiding disk reads in this manner is one of the most effective optimizations that can be made to an Oracle database. Allocating more memory to the buffer cache improves the chance that a data block will be in memory when requested a second time.

Sort Area Size

Determine if adjusting the sort area size may improve performance

Increasing the sort area size allocates more memory for sort operations and thus can improve overall performance by reducing the need to use slower disk storage to perform sorts. The sort area size is specified by setting the SORT_AREA_SIZE configuration parameter.

Sort operations in Oracle are of two types and commonly referred to as either disk sorts or memory sorts. When the data volume of the sort operation is small enough to fit in the allocated sort memory, the sort can be performed using only memory and no disk storage. Otherwise, temporary disk storage must be used to perform the sort.

PGA Size

Determine if resizing the PGA will improve performance.

Oracle allocates a private process memory area for each server process called the Program Global Area or PGA. This memory will be allocated whether the type of connection is dedicated or shared. The contents and location of the memory will vary depending upon the type of connection. The table below lists the two main components and their corresponding memory locations for dedicated and shared servers. 

By adjusting the memory allocated to the PGA, performance may improve by reducing the time needed to recreate memory structures needed to complete frequent database operations.

Optimize Physical Disk I/O

 Datafile I/O Load Balance

Determine if I/O load is balanced across datafile disk devices.

Datafiles should have similar I/O service times. When datafiles have very different I/O service times, it indicates that the I/O load across datafile disk devices may not be balanced. Following are common reasons why a device/filesystem may have longer service times than another:

• It may be physically slower (IO per second rating or average service time rating).

• It may be servicing simultaneous requests from multiple processes and/or datafiles (contention).

• It may not be using caching (or better caching algorithms).

• Other devices may be using RAID or other advanced technology disk architectures.

Database Writer Processes

Determine if a sufficient number of database writer processes or slaves are configured.

The database writer process (DBWR) is the only process that writes modified database blocks from the buffer cach to the database files. The database writer works asynchronously. This means that a user process never needs to wait for the database writer to finish writing. However, if the DBWR falls behind sufficiently, then the buffer cache will fill up with "dirty" blocks and waits will occur while user processes try to read new blocks into the cache. These bottlenecks can be eliminated by configuring multiple database writers/slaves or by enabling asynchronous I/O.

Number of Datafile Disk Devices

Determine if sufficient disk devices are allocated for database files.

The amount of physical (non-cached) I/O that can be performed by the database server is constrained by the following factors:

• The number and type of disk devices allocated to the database

• The I/O rate each disk can sustain

• The degree to which database I/O is evenly spread across disks

• The degree to which database I/O is evenly spread over time

• Contention for database disk devices by non-Oracle processes

Ensure there is enough I/O capacity to handle the database I/O workload.

Review Performance Deviations

CPU Usage Deviation

Determine if CPU utilization has varied significantly from the typical utilization for the given period.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance.

CPU Wait Deviation

Determine if CPU wait time has varied significantly from the typical wait time for the given period.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance

I/O Wait Deviation

Determine if I/O wait time has varied significantly from the typical wait time for the given period.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance

Latch Wait Deviation

Determine if latch wait time has varied significantly from the typical wait time for the given period.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance

Lock Wait Deviation

Determine if lock wait time has varied significantly from the typical wait time for the given period.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance

Other Deviation

Determine if other wait time has varied significantly from the typical wait time for the given period. Other wait is a catch bucket for resource wait items that do not fit within the other listed categories.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance.

Workload Deviation

Determine if database workload has varied significantly from the typical workload for the given period.

Variances should be reviewed to ensure that they do not indicate the occurrence of a problematic change in database performance.