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:
-
Buffer cache usage
-
Database data and rollback block contention
-
Dictionary caching
-
Sorts performed on disk as a ratio of all sorts, for memory and disk I/O usage
-
"Search All" ratio
-
Library caching
- 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.
|
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.