3.43 Transaction

Use this Knowledge Script to monitor the following parameters:

Active Transactions: retrieve the number of active transactions that are ongoing, and the maximum number of transactions that can be executed concurrently (set as an initialization parameter). This script computes a ratio, expressed as a percentage, of the two numbers. When you enable data collection, the percentage is stored in the repository. 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.
Call Rate: the demand placed on a database instance from all sources. This demand is determined by tracking the number of database calls per second from all applications and processes accessing the database instance. The database calls that are tracked include Parse, Execute, and Fetch statements. These calls are sometimes described as user calls. When the call rate (and thus the workload demand on the server) exceeds the threshold you set, the job raises an event.
Calls Per Transaction: the demand placed on a database instance by each transaction. This demand is determined by tracking the number of database calls (for example, to parse, execute, and fetch data) per committed transaction. When the number of database requests per transaction exceeds the threshold, the job raises an event.
Transaction Rate: the transaction rate for an Oracle database. This script tracks the number of transactions per second to provide a basic measurement of application workload. In addition, this script raises an event if the number of transactions per second exceeds the threshold you set.

Changes to your applications or to application usage patterns can affect the transaction rate, but in general, an increase in the transaction rate suggests an increase in overall server load. If you see a decrease in the transaction rate with the same number of connected users, it can indicate that you need to do some database tuning or investigate the reasons for the changes.

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

V_$SYSSTAT

V_$VERSION

V_$PARAMETER

V_$TRANSACTION

3.43.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.43.2 Default Schedule

The default interval for this script is Every 10 minutes.

3.43.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.
Monitoring
Monitor ratio of active transactions?	Select Yes to monitor the ratio of active transactions. The default is Yes.
Monitor call rate?	Select Yes to monitor the ratio of call rate. The default is Yes.
Monitor calls made per transaction?	Select Yes to monitor the calls made per transaction. The default is Yes.
Monitor transaction rate?	Select Yes to monitor the transaction rate. The default is Yes.
Event Notification
Event option for the ratio of active transactions to maximum concurrent transactions
Raise event if ratio exceeds threshold?	Select Yes to raise an event if the threshold exceeds the value you specified. The default is Yes.
Threshold - ratio of active transactions to max concurrent transactions	Specify a threshold for the maximum ratio of active transactions to maximum concurrent transactions, expressed as a percentage. 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 - ratio of active transactions to max concurrent transactions	Specify a threshold for the maximum ratio of active transactions to maximum concurrent transactions, expressed as a percentage. 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 - ratio of active transactions to max concurrent transactions	Specify a threshold for the maximum ratio of active transactions to maximum concurrent transactions, expressed as a percentage. 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 - ratio of active transactions to max concurrent transactions	Specify a threshold for the maximum ratio of active transactions to maximum concurrent transactions, expressed as a percentage. 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).
Event option for call rate
Raise event if threshold is exceeded?	Select Yes to raise an event if the maximum number of calls per second exceeds the threshold.The default is Yes.
Threshold - Maximum call rate	Enter a threshold for the maximum number of calls per second allowed 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).
Event option for calls made per transaction
Raise event if threshold is exceeded?	Select Yes to raise an event if the number of calls per transaction exceeds the threshold. The default is Yes.
Threshold - Maximum number of calls per transaction	Enter a threshold for the maximum number of database calls per transaction allowed 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).
Event option for transaction rate
Raise event if threshold is exceeded?	Select Yes to raise an event if the transaction rate exceeds the threshold you set. The default is Yes.
Threshold - Maximum transaction rate	Enter a threshold for the maximum number of transactions per second allowed before an event is raised. The default is 1.
Severity	Set the event severity level, from 1 to 40, to indicate the importance of the event. The default is 10 (red event indicator).
Collect Data
Collect data for the ratio of active transactions to maximum concurrent transactions?	Select Yes to collect data for charts and reports. If you enable data collection, the Knowledge Script returns the ratio of active transactions to maximum concurrent transactions as a percentage (%). The default is unselected.
Collect data for call rate?	Select Yes to collect data for charts and reports. If you enable data collection, the Knowledge Script returns the total user calls per second for all work sources. The default is unselected.
Collect data for calls made per transaction?	Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the current transaction rate (transactions/second). The default is unselected.
Collect data for transaction rate?	Select Yes to collect data for charts and reports. When you enable data collection, the Knowledge Script returns the current transaction rate (transactions/second). 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).