1.3.4 Data update processing performance
Oracle can be monitored to prevent performance degradation for Oracle Database data update processing. The following items can be used in Oracle monitoring to prevent degraded performance for data update processing:
-
Buffer cache usage
-
Database data and rollback block contention
-
Free list contention
- Organization of this subsection
(1) Primary fields related to data update processing performance
The following table lists the records and fields related to data update processing performance.
|
Record used |
Field used |
Value measured (example) |
|---|---|---|
|
PI |
Cache Hit % |
Buffer cache usage. |
|
Buffer Busy Wait % |
Buffer busy wait ratio. |
|
|
Free List Wait Events |
Free list wait events. |
(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 search performance. This problem can be handled by increasing the size of DB_CACHE_SIZE.
- Monitoring free list contention
-
The free list ratio can be monitored by using the Free List Waits alarm provided by the monitoring template.
When Free List Waits is at or above its threshold, this is often due to insufficient segment free lists. Insufficient free lists may degrade performance for data update processing. This problem can be handled by adding the FREE LISTS option to the STORAGE operator, to rebuild the table.