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.

Select the query time and target on the screen. Select the Time and Instance options, and then select . 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.

  

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.

• 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.

   

2. In Edit filter, select FULL or INDEX.

3. Select Apply.

4. To apply the selected conditions to the SQL list, select the 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 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.

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

The Plan Change Count section is a bar graph chart that displays the number of plan changes that occurred over time. When you select a time zone, a list of plan changes for the selected time appears.

Checking the plan change details

When you select a specific change from the list at the bottom of the screen, a Query section appears at the bottom of the screen where you can see the details before and after the plan change. By comparing the differences before and after the plan change in detail, you can determine which change affected the performance.

• You can open the Query section in a new window by selecting on the upper right of the section.

• To close the Query section, select in the upper right corner.

Filtering the searched results

Adding the filter conditions

You can filter the desired results based on the values ​​of the following items in the searched results.

Info

• sql_id

• sql_hash_value

• after_plan_hash_value

• before_plan_hash_value

1. In the Filter option, select .

   

2. In Filter key, select a desired filtering criteria.

   • If the value of the selected item is text, you can select any of Includes (blue) and Excludes (red).

     

   • If the value of the selected item is number, you can select any condition of == (equal to), >= (greater than or equal to), and <= (less than or equal to).

3. In Condition, select a condition.

4. Enter a string or number to match the condition.

5. Select Apply.

Note

• To add filtering conditions, select Add and then repeat Step 1 to 5. Added conditions are applied based on AND (&&).

• To delete some items while adding conditions, select on the right of the filter conditions. To delete all conditions, select Delete All.

• To quickly delete the conditions applied to the Filter option, select .

Modifying the filter conditions

Click any applied to the Filter option. If the Edit filter window appears, modify any desired items and then select Apply.