The Oracle SQL Warehouse summary page displays a snapshot of the performance categorization of SQL across databases and applications.

The categorizations are:

• Degradation: SQL statements with more than 20% increase in SQL response time, based on linear regression. The value of the SQL response time is derived from the total elapsed time divided by the total number of executions for the SQL.

• Variability: SQL statements with a relative variability of more than 1.66. Relative variability of an SQL is measured by the standard deviation of the SQL response time divided by the average of the SQL response time. Those SQL statements that have a relative variability of more than 3 are identified as SQL statements with highly variant performance.

• Inefficiency: SQL statements with inefficiency of more than 20%. Inefficiency percentage of an SQL is derived from the inefficient wait time (wait time other than I/O, CPU, or idle wait time events) divided by the total database time.

• Plan Changes: SQL statements that utilize multiple execution plans.

• Improvements: SQL statements with more than 20% decrease in SQL response time, based on linear regression. The value of the SQL response time is derived from the total elapsed time divided by the total number of executions for the SQL.

  
  
  
  
  

Clicking the SQL count of each categorization displays a heat map of all the SQL across databases under the selected category. By default, the SQL with the maximum number of active sessions is selected. You can customize the heat map based on the following:

• Size: Customizes the size of the heat map segments based on Average Active Sessions, Average Response Time, Executions/Hour, I/O Time, and CPU Time.

• Color: Customizes the color coding of the heat map based on percentage change value or absolute value of Average Active Sessions, Average Response Time, Executions/Hour, I/O Time, and CPU Time.

Oracle SQL Warehouse allows you to identify the high-load SQL statements across databases that consume a disproportionate amount of system resources and causes a large impact on the database performance.

• Top SQL by CPU: SQL statements with the highest growth in CPU usage

• Top SQL by I/O: SQL statements with the highest growth in I/O usage.

Clicking either menu option opens a heat map that displays the SQL with the highest growth in CPU (or I/O) usage, differentiated by color keys.

Clicking each SQL in the heat map displays the SQL details (grouped by databases) in a bar chart in the section below the heat map. You can customize the bar chart display based on the following:

• Avg. Active Sessions

• Avg. Average Response Time

• Executions Per Hour

• I/O Time

• CPU Time

• Inefficient Wait Time

When you select a SQL from the heat map, if the same SQL exists across databases, all SQL statements get selected. This view helps you identify the SQL statements that are common across databases. The SQL details (grouped by databases) are displayed in a bar chart in the section below the heat map. This display is based on the current time period. You can customize the bar chart display based on the following:

• Active Sessions

• Average Response Time

• Executions Per Hour

• I/O Time

• CPU Time

In the Performance Trend chart, you can click on the Selected SQL identifier to drill down to explicit performance details for the selected SQL. In addition to general information about the SQL, you'll also be able to view detailed charts for the following areas:

• Metrics: Performance Trend, Activity, Response Time Distribution, and Response Time
• Compare by Plan or Database: Average Response Time, Average Active Sessions, Executions Per Hour, I/O Time, and CPU Time
• Execution Plans