3.7 Cache

Use this Knowledge Script to monitor the frequency with which requested data and resources are retrieved from the cache. As this hit ratio (that is, the percentage of time that data or resources are retrieved from the cache) decreases, performance also decreases because data that must be retrieved from disk or library objects must be reinitialized in order to service the requests.

Use this script to monitor any combination of the following statistics:

  • The buffer cache hit ratio indicates the percentage of time that requested data you can find in the buffer cache.

  • The data dictionary hit ratio indicates the percentage of time that requested data you can find in the data dictionary.

  • The library cache hit ratio indicates the percentage of time that the system requests to access objects in the library cache and you can service without re-initializing or reloading library objects. Changes to the library cache hit ratio might occur when an application becomes active, causing more SQL statements and stored procedures to be used.

You can set a threshold for each cache hit ratio you choose to monitor. If a cache hit ratio falls below a threshold you set, the job raises an event.

The account you use to run this script must have SELECT permissions for the following tables:

V_$LIBRARYCACHE
V_$ROWCACHE
V_$SYSSTAT
V_$VERSION

3.7.1 Resource Objects

Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and monitors only that database.

3.7.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.7.3 Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

Oracle Username

Enter the username that this script needs to access the target databases. If you run this script on more than one database, configure each database with the same username. To use SYSDBA authentication, leave this parameter blank. The default is blank.

Collect data for monitored metrics?

Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the statistics you choose to collect:

  • The buffer cache hit ratio

  • The data dictionary hit ratio

  • The library cache hit ratio

The default is unselected.

Monitor buffer cache hit ratio?

Select Yes to monitor the percentage of time that requested data you can find in the buffer cache. The default is Yes.

Raise event if threshold is not met?

Select Yes to raise an event if the buffer cache hit ratio exceeds the threshold you set. The default is Yes.

Threshold - Minimum buffer cache hit ratio

Enter a minimum threshold for the buffer cache hit ratio. If the actual hit ratio falls below than this threshold, the job raises an event.

Ideally, you should set this percentage relatively high because the more frequently Oracle RDBMS uses the buffer, the better your database performance. When the actual hit ratio falls below the threshold you set, the event alerts you that database performance has degraded.

The default is 70%.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event.

The default is 10 (red event indicator).

Monitor data dictionary hit ratio?

Select Yes to monitor the percentage of time that requested data you can find in the data dictionary. The default is Yes.

Raise event if threshold is not met?

Select Yes to raise an event if the data dictionary hit ratio falls below the threshold you set. The default is Yes.

Threshold - Minimum data dictionary hit ratio

Enter a minimum threshold for the data dictionary hit ratio. If the actual hit ratio falls below this threshold, the job raises an event. set this percentage high because the more frequently Oracle RDBMS uses the data dictionary to service requests, the better your database performance. When the actual hit ratio falls below the threshold you set, the event alerts you that database performance has deteriorated. The default is 90%.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10 (red event indicator).

Monitor library cache hit ratio?

Select Yes to monitor the percentage of time that system pin requests to access objects in the library cache you can service without re-initializing or reloading library objects. The default is Yes.

Raise event if threshold is not met?

Select Yes to raise an event if the library cache hit ratio falls below the threshold you set. The default is Yes.

Threshold - Minimum library cache hit ratio

Enter a minimum threshold for the library cache hit ratio. If the actual hit ratio falls below this threshold, the job raises an event. Ideally, you should set this percentage extremely high because re-initializing or reloading library objects imposes a significant performance hit. When the actual hit ratio falls below the threshold you set, the event alerts you that database performance has degraded. The default is 95%.

Severity

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10 (red event indicator).

Event severity for internal failure

Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator).