Monitoring of wait time attributes of SQL query
Monitor your Oracle database by using BMC Helix Operations Management or TrueSight Operations Management and proactively know about the issues that might occur. Take preventive measures to avoid disruption in the database services. Monitoring the wait time attributes of an SQL query helps in identifying the queries that take more time to execute. You get the total waiting time for these queries. Use it to identify possible blockers in your environment that are causing the delay and fix the issues proactively.
PATROL for Oracle Enterprise Database stores the wait time information in attributes. Use these attributes to understand the reason of long execution time and proactively resolve the issue.
To enable the monitoring of wait time attributes at environment level
Configure the wait time attributes configurations for the following monitor types:
- Oracle Data Guard Database
- Oracle Standalone Database
- Oracle Real Application Cluster or Real Application Cluster One Node Database
To enable the monitoring of wait time attributes at global level
Configure the following variable to monitor the wait time attributes:
| Variable | Description |
|---|---|
/KOE/Oracle/isTopNQueriesSelected | Enables or disables the filter for top n queries by total wait time. |
Additionally, you can configure the following variables:
| Variable | Description |
|---|---|
/KOE/Oracle/topNqueryLimit | Sets the number of queries to filter. |
/KOE/Oracle/maxQueryInstances | Sets the value for the maximum number of query instances to filter. |
/KOE/Oracle/maxQueryDays | Sets the number of days to keep the query instance. |
/KOE/Oracle/waitTimeCollector | Sets the collection time for the wait time query attributes. This field is applicable for query instances attributes only. |
To add a configuration variable
- Click Configuration and select Infrastructure Policies.
- Locate the infrastructure policy and click Edit.
- Click the Configuration Variables tab and then click Add Configuration Variable.
- On the Add Configuration Variable page, in the Variable field, enter the variable path and name.
For example,/KOE/Oracle/waitTimeCollector. - From the Operation list, select REPLACE.
You create a new variable by using the REPLACE operation. Use the following options for the existing variables:- DELVAR: Deletes the variable and disables the task for which you created it. The value is deleted after you save the policy.
- DELETE: Sets the variable value to an empty string. The value is deleted after you save the policy.
- In the Value field, enter the value of the variable.
If you do not enter a value, default value of the variable is used.
Where to go from here
Configure the wait time attributes collection configurations for the following monitor types:
Comments
Log in or register to comment.