Use this Knowledge Script to monitor block-level database activity. Oracle RDBMS data blocks are the smallest unit of storage for a database. Monitoring I/O activity at the block level can be a key indicator of database performance.
Use this script to monitor any combination of the following statistics:
The number of block changes per transaction. This block change rate measures the number of SQL Data Manipulation Language (DML) commands that each transaction performs (for example, to create and drop indexes). As the number of block changes increases, the efficiency of the database transaction and database performance decreases.
The number of times the Oracle RDBMS buffer manager locates a database per second. This block get rate indicates the rate at which an application references the database. An increase in the block get rate suggests an increase in overall server load. A decrease in the block get rate without a decrease in load might indicate that you need to do some database tuning because there has been a slowdown in the number of database blocks requested and located per second.
The number of times database blocks are requested per committed transaction. This block visit rate measures the database work load per completed transaction (including both successful and aborteded database transactions).
The account you use to run this script must have SELECT permissions for the following tables:
V_$SYSSTAT
V_$VERSION
Oracle Database folders. When you drop a script on an Oracle Database folder, a job executes on that database and monitors only that database.
The default interval for this script is Every 10 minutes.
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 block activity? |
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 default is unselected. |
|
Monitor block change rate? |
Select Yes to monitor the block change rate, the number of block changes per transaction. The default is Yes. |
|
Raise event if threshold is exceeded? |
Select Yes to raise an event if the block change rate exceeds the threshold you set. The default is Yes. |
|
Threshold - Maximum block change rate |
Enter a threshold for the maximum number of block changes per transaction before the job raises an event. The default is 100. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator). |
|
Monitor block get rate? |
Select Yes to monitor the block get rate, the number of times the Oracle RDBMS buffer manager locates a database block per second. The default is Yes. |
|
Raise event if threshold is exceeded? |
Select Yes to raise an event if the block get rate exceeds the threshold you set. The default is Yes. |
|
Threshold - Maximum block get rate |
Enter a threshold for the maximum number of block get operations per second before the job raises an event. The default is 100. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (red event indicator). |
|
Monitor block visit rate? |
Select Yes to monitor the block visit rate, the number of times database blocks are requested per committed transaction. The default is Yes. |
|
Raise event if threshold is exceeded? |
Select Yes to raise an event if the block visit rate exceeds the threshold you set. The default is Yes. |
|
Threshold - Maximum block visit rate |
Enter a threshold for the block visit rate, the maximum number of block get operations per committed transaction. The default is 100. |
|
Severity |
Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 5 (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). |