Troubleshooting long running queries

PATROL for Oracle Enterprise Database execute SQL queries in the monitored databases to collect data in most attributes. The KM opens a JDBC connection and executes the queries. A query that takes a long time to run can block the connection and affect other queries and cause data gaps. In this KM, to avoid query blocking issues and data gaps, you can set a threshold to release a query when its execution duration is equal or greater than the configured threshold. In this way, only the specific attributes for which the query is collecting data might encounter a data gap.

To set the threshold, go to the affected monitor type (like Oracle Data Guard Database) > Environment Configurations > Collections settings > Long running data collection queries (min). 

If the threshold is breached, the Long running queries attribute at the environment level reports the query, the effected category, and attributes.

<can you help me take this screenshot?>

Recommendations

• If you encounter data gaps for an attribute or performance issue in the database, use the Long running queries attribute annotation message to identify the affected category, attribute, and the SQL query that is causing the issue. When the query has been identified, you can request your DBA to run the query to understand the reason of the long running time.
• The size of your environment is important to identify the threshold. For small environments, you might set the value the Long running data collection queries (minutes) attribute to a lower number. If you are not sure about the value that you must assign the threshold, leave the default value (30 minutes).
• If you have a large environment with performance issues, you might want to configure the affected instance in a different environment. You can also do the same with the monitored categories. For example, if you have a large number of tablespaces, you can use another environment to monitor tablespaces category only, and the rest of categories are monitored from a different environment. All categories are consolidated under the same device.
• If you don’t need to monitor the attribute or category causing the issue, disable it from being monitored.