1.3.3 Search processing performance

To maintain and improve Oracle Database search processing performance, set the maximum number of tuning items, and monitor Oracle to check whether they remain in the permitted ranges.

The following Oracle items may be monitored to maintain or improve search processing performance:

Organization of this subsection
(1) Records and fields related to search processing performance
(2) Monitoring methods

(1) Records and fields related to search processing performance

The following table describes the records and fields related to search processing performance.

Table 1-1 Records and fields related to search processing performance

Record usedField usedValue measured (example)
PICache Hit %Buffer cache usage
Buffer Busy Wait %Buffer busy wait ratio
Dict Cache Get Miss %Ratio of data requests due to cache misses
Sort Overflow %Ratio of sorts using temporary segments
Non-Index Lookups %Ratio of full-table scans for which caching is not performed
Lib Cache Miss %Library cache miss rate
This field means the ratio of times the allocated objects in library cache are reloaded. As the value of this field increases, the amount of resources in use also increases.

(2) Monitoring methods

Monitoring buffer cache usage
Buffer cache usage can be monitored by using the Buffer Cache Usage alarm provided by the monitoring template.
Monitoring database data and rollback block contention
Database data and rollback block contention can be monitored by using the Buffer Cache Waits alarm provided by the monitoring template. Monitoring database data and rollback block contention alongside buffer cache usage can be very useful.
When Buffer Cache Usage is at or below its threshold, and Buffer Cache Waits is at or above its threshold, this is often because the buffer cache is insufficient. When the buffer cache is insufficient, disk I/O is performed, which may degrade performance. This problem can be handled by increasing the size of DB_CACHE_SIZE.
Monitoring dictionary caching
Dictionary caching can be monitoring by using the Dict. Cache Usage alarm provided by the monitoring template.
Monitoring library caching
Library caching can be monitored by using the Library Cache Usage alarm provided by the monitoring template.
When Dict. Cache Usage is at or above its threshold, and Library Cache Usage is at or above its threshold, this is often because the shared pool is insufficient. An insufficient shared pool may degrade search performance. This problem can be handled by increasing SHARED_POOL_SIZE#.
#
With Oracle 10g, when the initial parameter SGA_TARGET is specified, since the SGA configuration parameter is automatically adjusted, no action needs to be performed when thresholds for warning or abnormal conditions are exceeded.
Monitoring sorts performed on disk as a ratio of all sorts, when memory or disk I/O is used
The sorts performed on disk as a ratio of all sorts when memory or disk I/O is used can be monitored using the Disk Sorts alarm provided by the monitoring template.
When Disk Sort is at or above its threshold, this is often because the memory for sort operations is insufficient. When the memory for sort operations is insufficient, a disk sort is performed using the TEMPORARY segment. This may degrade performance, but the problem can be handled by increasing the value of SORT_AREA_SIZE.
Monitoring the "search all" ratio
The "search all" ratio can be monitored using the Full Table Scans alarm provided by the monitoring template.
When Full Table Scans is at or above its threshold, this is often because "search all" occurs, degrading search performance. Search performance can be tuned by narrowing down the search targets.