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 PostgreSQL stores the wait time information in attributes. Use these attributes to understand the reason of long execution time and proactively resolve the issues.
To enable the monitoring of wait time attributes at environment level
Configure the wait time attributes configurations for the monitor types. For more information, see Configuring-the-PostgreSQL-KM-in-TrueSight-or-Central-Monitoring-Administration.
To enable the monitoring of wait time attributes at global level
Configure the following variable to monitor the wait time attributes:
Variable | Description |
|---|---|
/PGR/PostgreSQL/isTopNQueriesSelected | Enables or disables the filter for top n queries by total wait time. |
Additionally, you can configure the following variables:
Variable | Description |
|---|---|
/PGR/PostgreSQL/topNqueryLimit | Sets the number of queries to filter. |
/PGR/PostgreSQL/maxQueryInstances | Sets the value for the maximum number of query instances to filter. |
/PGR/PostgreSQL/maxQueryDays | Sets the number of days to store the query instances in the PATROL Agent. |
/PGR/PostgreSQL/waitTimeCollector | Sets the collection time for the wait time query attributes. This variable 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, /PGR/PostgreSQL/maxQueryDays. - 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
Configuring-the-PostgreSQL-KM-in-TrueSight-or-Central-Monitoring-Administration