About SQL Performance Monitoring

When working in OTM, there are a few ways in which you can create SQL, both directly and indirectly. There are two types of SQL statements you can control:

• Explicit SQL: These allow you to write complex SQL statements for OTM to execute. These include: saved queries, integration saved queries, and DIRECT SQL UPDATE agent actions.
• Implicit SQL: OTM's Query object infrastructure allows you to run ad-hoc queries based on Query By Example field inputs. Many of these inputs, such as finder UIs, can result in complex table joins that might impact performance. Tracking these implicit SQL specifications allows you to identify where you need such queries and when they need to be optimized by an explicit saved query (or property modification).

Screens that use explicit SQL entry can be monitored via SQL Performance. Oracle maintains the following sources for this information:

• Cursor Cache: Recently run SQL is stored in the database cursor cache, along with its last explain plan and execution stats.
• Automatic Workflow Repository (AWR): Each hour, OTM queries the last day of AWR snapshot collection in the database. This provides historical explain plans and execution statistics for the most costly (typically 30) SQL statements. 
• A Priori: which provides an a priori explain plan for SQL. This is a theoretical explain plan, independent of actual execution information.

Process Management

The Collect SQL Performance process management task automatically runs at regular intervals to collect data to help you troubleshoot for poorly-performing SQL, via the SQL Performance page.

Review SQL Performance

Use the SQL Performance page to review the data collected by the Collect SQL Performance process management task.

Metrics

The AWR SQL metric component collects all explicit and implicit SQL retrieved from the last hour's AWR snapshot(s).

Logging

A log ID, SqlPerformance monitors performance of Collect SQL Performance and logs each update to stored SQL IDs.