Hitachi

JP1 Version 11 JP1/Performance Management - Agent Option for Oracle Description, User's Guide and Reference


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

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 used

Field used

Value measured (example)

PI

Cache 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 alarm 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.