Skip to main content

SQL analysis

Info

This document has been created based on the Oracle Monitoring V2. For the Oracle monitoring V1 document, see the following.

Home > Select Project > Analysis > SQL Analysis

It provides useful information for diagnosing performance issues by analyzing SQL statements executed on the database. The SQL Analysis menu consists of two key tabs.

  • Access Statistics: You can check the SQL that caused Full Scan, which may be the cause of the performance degradation.

  • Plan Change History: It detects the case when a SQL statement with the same SQL ID is changed to a different execution plan by the optimizer. This can affect the performance and needs to be monitored.

Access Statistics​

You can check the SQL that caused Full Scan, which may be the cause of the performance degradation. You can check the number of Full Scan occurrences by time and type. This allows you to optimize SQL statements that frequently cause Full Scan and optimize resource usage by reducing unnecessary full scans. Also consider replacing Full Scan with Index Scan to improve the query performance.

AccessStatistics

Select the query time and target on the screen. Select the Time and Instance options, and then select Search icon. If necessary, you can filter the desired query results by setting the Filter option.

  • Access Count: This bar graph chart allows you to check the number of Full Scan occurrences by the time zone. When you select a specific time zone, a list of SQLs performed during the selected time is displayed in the list at the bottom of the screen and in the Operation Count chart on the right.

  • Operation Count: You can check the type and number of full scans that occurred. You can see the object name by hovering your mouse over each bar.

    Operation Count

See query details​

If you select the query column from the SQL list at the bottom of the screen, the SQL details window appears. You can check the SQL query statements and plan information.

SQL details

  • View SQL Statistics: You can go to the SQL statistics menu where you can check statistical information related to the SQL query statement.

  • Runtime Plan: It provides the execution plan and runtime information for a selected SQL query. It provides the information details such as execution count, average execution time, and average physical reads.

  • Explain Plan: It displays the execution plan predicted by the optimizer. It provides information such as cost, job, object name, and cardinality.

  • Plan History: You can check the history of the execution plans of the SQL queries executed in the database.

  • Bind Capture: You can see the values ​​of the bind variables used in SQL queries executed in the database. This allows you to see actual content of query executions.

    Note

    This is a value captured in the database (v$sql_bind_capture), not a bind value executed in real time. If the number of cases is too large, it displays up to 100.

Filtering the searched results​

You can filter the results in the queried SQL list based on Full Scan or Index Scan through the Filter option.

  1. Select the Filter option.

    Filter

  2. In Edit filter, select FULL or INDEX.

  3. Select Apply.

  4. To apply the selected conditions to the SQL list, select the Search icon button.

Note

To search all for Full Scan and Index Scan, in Edit filter, select all options for FULL and INDEX. Or by selecting the Plus icon button, you add more filtering options.

Plan Change History​

It detects the case when a SQL statement with the same SQL ID is changed to a different execution plan by the optimizer. This can affect the performance and needs to be monitored. By detecting and responding to performance changes due to plan changes, you can identify unnecessary plans and maintain the consistent SQL performance.

Plan Change History

Select the query time and target on the screen. Select the Time and Instance options, and then select Search icon.

Plan Change Count: This bar graph chart displays the number of plan changes by time zone. When you select a time zone, a list of plan changes for the selected time appears. When you select a changed item from the list, you can see the details before and after the plan change at the lower part of the screen.

Plan Change History

By comparing the differences before and after the plan change in detail, you can determine which change affected the performance.