Analyze SQL Performance

Using the Oracle SQL Warehouse function of Operations Insights, you can analyze the SQL performance problems for enterprise-wide applications across a fleet of databases. In addition, Operations Insights provides trends and key insights to SQL performance issues thereby helping you to be proactive in avoiding future problems.

By using Oracle SQL Warehouse, you can:

  • View the degrading SQL, variant SQL, inefficient SQL, and SQL with plan changes.
  • View the SQL statements with the highest CPU and I/O usage.
  • Analyze the performance of SQL based on these categories – degrading, inefficient, variant, and with plan changes.
  • Perform a SQL performance analysis with the help of the average response time statistics.
  • View the number of plans per SQL and get further insights to SQL performance based on details such as best performing plan, most executed plan, plan with the most I/O.

Typical Tasks for Analyzing SQL Performance

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

Task Description More Information
Monitor the performance of SQL statements based on the summary data, across databases. Identify degrading SQL statements, variant SQL statements, inefficient SQL statements, and SQL statements with multiple execution plans across databases. Monitor SQL Performance Across Databases
Analyze the performance of SQL statements on the basis of the performance category. Analyze the performance of SQL statements in different categories (degrading, variant, inefficient, and with plan changes) by using parameters such as percentage database time, number of SQL statements, and size. Analyze the Performance of SQL Statements

Monitor SQL Performance Across Databases

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.


    Graphic shows the SQL summary bar.

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.

The Insights section provides the following links 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 the links opens heat maps that display the SQL with the highest growth in CPU and I/O usage differentiated by color keys.


Graphic shows the heat map with performance trend.

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

Analyze the Performance of SQL Statements

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.


Graphic shows the SQL response time analysis.

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.

Search for SQL

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.

Perform Fine-grained Data Analysis

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.
    Graphic shows the database 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, clicking on the Top SQL by CPU Insight now displays SQL analytic information with a higher level of granularity.
    Graphic shows Top SQL by CPU data at a higher level of granularity.