Does the Oracle Agent Provide Metrics on Long Running Queries or Query Optimization?

Q: Customer had a select query running for 250 seconds and the application was not build to wait that long for the query.  As a result, it was throwing errors in the application’s log.  Took them awhile to troubleshoot and narrow down the issue.  Afterwards, their DBA optimized the query.  Customer wants to monitor all their queries, understand the long running queries and then optimize them if they are running poorly. How can GPE help with is or is this all OEM?

A:  It seems like this customer is experiencing a very common scenario when it comes to working with Oracle or any relational database. By default OEM does not offer long running query monitoring and alerting in the sense of automatic notification. There is something called OEM Metric extensions where you could possibly do something here but that’s a lot of work. OEM does have query monitoring but you typically have to manually select the query to monitor and it’s manual. Oracle and OEM have mechanisms to alert a DBA about queries which could possibly need tuning but that requires an additional license and again is not alerting in the sense of someone getting an email notification, etc.

As far as our Galileo agent goes, we do have a long running query chart in core shown below, as well as two analytic rules. For this particular query they are describing, this could be a fit for them. However, we do not show the actual query text, just the 13 character SQL ID hash and we only currently report on time running for the queries. We would have to look at adding additional charts for more in depth SQL stats and even query optimization features.  Every SQL statement is computed into a unique 13 character hash. Unfortunately we currently do not display the actual SQL text in GPE that generated the hash. The hash generation is only one way. To correlate what SQL generated that hash the DBA would have to look in the shared pool/cache, one of many places, in the database. The following query ran by a DBA will reveal the actual SQL text:   select sql_text from v$sql where sql_id=’ty8jj8567fn45′;      If the statement is not in the cache anymore then there are other places in the database this can be found, i.e. AWR.

Core chart:

Analytic rules: