Here’s a list of tasks to analyze the performance of your SQL requests by using the SQL Analytics application of Oracle IT Analytics.

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

The SQL Response Time Analysis section of the home page displays a graph of the performance of a set of SQL statements in their databases based on the percentage database time.

For example, to view a graph of the degrading SQL statements among a set of 40 SQL statements that are taking more than 1% database time in their respective databases:

1. Select Degrading in the Insight Category field.
2. Enter 1 in the DB Time (%) > field.
3. Enter 40 in the Limit field. The Limit field specifies the number of SQL Statements that you want to base your graph on.
4. Click Apply.
5. (Optional) Select Average Response Time in the Size drop-down list.

   The display of the graph changes based on the value that you select from the Size drop-down list. The other available values are Average Active Sessions, Executions Per Hour, I/O Time, CPU Time, and Inefficient Wait Time.

The bubbles on the chart denote the degrading SQL statements. Hovering the mouse cursor over a bubble displays the SQL details.

Similarly, you can view a graph with top SQL statements that are variant, inefficient, and/or with plan changes by selecting the relevant values in the Insight Category field.

You can search for specific SQL statement executions across one or more databases using the statement's SQL identifier SQL ID.

Enter the SQL ID in the text entry field and hit Enter to display the SQL Search Results dialog.

Click Show to view the SQL statement in its entirety or Copy if you want to copy and paste the statement to a document for further analysis.

The SQL Search returns a list of databases where the SQL has been found over a specified time period. By default, seven days is used for the search window. Optionally, you can select up to 12 months for a broader view of SQL statement execution.

SQL Detail

To view detailed information on how the SQL has been used for a specific database, select a database from the list and click View SQL for Selected Database. The SQL Detail page for that statement displays. In addition to a summary of general usage information and performance statistics, you'll be able to view performance metrics, execution plans, as well as being able to compare plans.

Oracle SQL Warehouse allows you to perform fine-grained data analysis to optimize SQL performance.

To perform fine-grained data analysis:

1. Select a single database from the Database drop-down selector.
   
   
   
   
   Note
   
   The selector allows you to type in a partial database name to narrow the number of entries that show up in the drop-down list.
2. Select a time frame of 30 days or less.
   Note
   
   The selected time range must fall within the last 30 days. For example, you cannot select a one week range from the previous year.
3. You can now drill down to view finer-grained SQL data. For example, open the navigation menu and then click Observability & Management. Under Operations Insights, click Oracle SQL Warehouse and then Top SQL by CPU. You can now view SQL analytic information with a higher level of granularity.