3.21 Memory

Use this Knowledge Script to monitor the following parameters:

Freelist Waits: retrieve the number of freelist waits and the total number of data requests, and to compute a ratio of the two numbers. You can set multiple thresholds for the ratio of freelist waits to data requests, and the job raises events when any one of the thresholds exceeds the value you specified.
Buffer Busy Waits: retrieve the number of buffer busy waits and the number of logical reads, and compute a ratio between the two numbers (expressed as a percentage). When you enable data collection, the repository stores the ratio. You can set multiple thresholds for the maximum ratio, and the job raises an event when any of the thresholds exceeds the value you specified. A higher ratio indicates an increased contention for buffers in the SGA memory of Oracle database.
Cache: 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 system requests to access objects in the library cache 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.
BGProc: the total number of physical read/write (I/O) operations per second and/or the total memory usage for Oracle RDBMS background processes. If the total number of read/write operations per second or the total memory usage for the list of specified processes exceeds the threshold, the job raises an event.

Oracle RDBMS background processes include CKPT, DBW0, LGWR, PMON, RECO, SMON, SNP0, and others. You can monitor all or individual background processes.

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

V_$SYSSTAT

V_$VERSION

V_$WAITSTAT

V_$LIBRARYCACHE

V_$ROWCACHE

V_$SYSTEM_EVENT

V_$BGPROCESS

V_$PROCESS

V_$SESSION

V_$SESSTAT

V_$STATNAME

3.21.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.21.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.21.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.
Memory
Freelist Waits	Select Yes to monitor the Freelist Waits. The default is unselected.
Buffer busy Waits	Select Yes to monitor the Buffer Busy Waits. The default is unselected.
Cache
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.
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.
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.
BGProc
Oracle background processes to monitor	Enter the names of the background processes you want to monitor. Separate the names with commas; do not use spaces. To monitor all Oracle RDBMS background processes, enter an asterisk (). Possible valid background process names include: CKPT DBW0 LGWR PMON RECO SMON SNP0 SNP1 The default is ().
Monitor read/write operations?	Select Yes to monitor the number of physical read/write operations per second by background processes. The default is Yes.
Monitor memory usage?	Select Yes to monitor the total memory usage by background processes. The default is Yes.
Event Notification
Raise event if threshold is exceeded for ratio of freelist waits to total data requests?
Raise event if threshold is exceeded?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is Yes.
Threshold - Maximum ratio of freelist waits to data requests	Specify a threshold for the maximum ratio of freelist waits to total data requests. The default is .95.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 5 (red event indicator).
Raise event if ratio exceeds threshold?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.
Threshold - Maximum ratio of freelist waits to data requests	Specify a threshold for the maximum ratio of freelist waits to total data requests. The default is .80.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 15 (yellow event indicator).
Raise event if ratio exceeds threshold?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.
Threshold - Maximum ratio of freelist waits to data requests	Specify a threshold for the maximum ratio of freelist waits to total data requests. The default is .60.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 25 (blue event indicator).
Raise event if ratio exceeds threshold?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.
Threshold - Maximum ratio of freelist waits to data requests	Specify a threshold for the maximum ratio of freelist waits to total data requests. The default is .40.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 35 (magenta event indicator).
Raise event if threshold is exceeded for ratio of buffer busy waits to logical reads?
Raise event if threshold is exceeded?	Select Yes to raise an event if the threshold exceeds the specified limit. The default is Yes.
Threshold - Maximum ratio of buffer busy waits to logical reads	Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is.95.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 5 (red event indicator).
Raise event if threshold is exceeded?	Select Yes to raise an event if the ratio exceeds the threshold specified below. The default is unselected.
Threshold - Maximum ratio of buffer busy waits to logical reads	Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is .80.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 15 (yellow event indicator).
Raise event if threshold is exceeded?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.
Threshold - Maximum ratio of buffer busy waits to logical reads	Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The default is.60.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 25 (blue event indicator).
Raise event if threshold is exceeded?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is unselected.
Threshold - Maximum ratio of buffer busy waits to logical reads	Specify a threshold for the maximum ratio of buffer busy waits to logical reads. The value is .40.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event for successfully setting the monitoring options. The default is 35 (magenta event indicator).
Raise event for cache?
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 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. 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).
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 unselected.
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).
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 unselected.
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. 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).
Raise event for Oracle Background processes?
Raise event if threshold is exceeded?	Select Yes to raise an event when the number of physical read/write operations per second exceeds the threshold you set. The default is unselected.
Threshold - Maximum read/write operations rate	Enter a threshold for the maximum number of physical read/write operations per second. The default is 5.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 15 (yellow event indicator).
Raise event if threshold is exceeded?	Select Yes to raise an event when the memory utilization of the monitored processes exceeds the threshold you set. By default, events are not enabled.
Threshold - Maximum memory usage	Enter a threshold (in MB) for the maximum amount of memory used by all background processes you are monitoring. The default is 15.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 15 (yellow event indicator).
Data Collection
Collect data for ratio of freelist waits to total data requests?	Select Yes to collect data for charts and reports. If data collection is enabled, returns the ratio of freelist waits to total data requests. The default is unselected.
Collect data for ratio of buffer busy waits to logical reads?	Select Yes to collect data for charts and reports. If data collection is enabled, returns the ratio of buffer busy waits to logical reads as a percentage (%).The default is unselected.
Collect data for Cache?	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.
Collect data for memory usage and I/O of Oracle processes?	Select Yes to collect data for charts and reports. If data collection is enabled, returns the number of read/write operations per second, and/or the total memory usage for all monitored background processes. The default is unselected.
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).