SQL Performance

This page is accessed via Configuration and Administration > Technical Support > Diagnostics and Tools > Database > SQL Performance.

If you wrote custom SQL for OTM or are working with SQL, this page lets you search for SQL queries that perform poorly. It allows you to search for particular categories of queries and view their theoretical or historical performance. Alternatively, you can sort all explicit and implicit SQL by execution time, rows processed, or number of executions, looking for problematic statements. It uses the data collected by the Collect SQL Performance process. 

The page is a finder that lets you query on several fields, including:

• SQL ID: A unique ID generated when the SQL query is run.
• Type: The source for the data.
  • No Execution Data: Also known as APRIORI, which are theoretical results of a SQL query before it is run.
  • Cursor Cache: Contains real performance numbers based on a set number of calls to a database. This is the most recently run SQL.
  • AWR Cache: The top 30 high impact issues the scan found.

    Note: Explain plans are only available for the worst 10 statements by category (recently discovered).

• Category: How the SQL query is classified, such as coming from a saved query, integration saved query, or an agent.
• Use Case: What caused the SQL query to run, such as a specific saved query or finder.
• Elapsed Time: Elapsed time measured in milliseconds.
• Total Executions: Number of times the query was run.
• Rows Processed: Number of rows processed.

From the finder results, you can run actions that can help you review the SQL performance.