SQL Plan analysis

Home > Select Project > Analysis > SQL Analysis

It provides useful data for diagnosing performance issues by analyzing SQL statements executed in the database.
SQL Plan Analysis consists of the Access Statistics tab and the Plan Change History tab.

Access Statistics

In the Access Statistics tab, you can check when each SQL performs a Full Scan, its type, and how many times it occurs. Queries that frequently trigger Full Scans can be optimized to use indexes, improving performance and reducing resource usage.

1. Go to the Access Statistics tab in Analysis > SQL Plan Analysis.

2. Select the query time and instance.

3. Set the Time and Instance options, then click the button.
   If needed, you can also configure the Filter option to filter the results.

4. Review the search results in the Access Count and Operation Count sections.

   • Access Count: A bar chart showing the number of Full Scans by time range.
     Selecting a specific time displays the list of SQL statements executed during that time in the bottom list and updates the Operation Count chart on the right.

   • Operation Count: Displays the types and counts of Full Scans.
     Hovering over each bar shows the object name.

     

View SQL 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.

Click the View SQL Statistics → button to move to SQL statistics,
where you can view statistical information related to the selected SQL query.

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

AI Tuning Guide

The AI Tuning Guide analyzes SQL queries, plans, and statistical information to diagnose performance issues and suggest optimization strategies.
It helps developers and DBAs quickly identify bottlenecks and improve performance through efficient SQL optimization.

Caution

Usage Conditions and Notes

For PostgreSQL, MySQL, and SQL Server, query plan retrieval is required.
If the plan is not retrieved, the AI Tuning Guide icon appears as (disabled), and the feature cannot be used.

• Please note that AI-generated results are based on automated analysis and may not be 100% accurate.

1. Click the SQL you want to analyze and diagnose to move to the SQL details screen.

2. On the SQL details screen, click the AI Tuning Guide icon at the bottom right to start the AI analysis.

3. Review the AI analysis results.

   Result Item	Description
   Query Plan and Summary	Provides the purpose and execution summary of the query. Analyzes execution count, cumulative execution time, and overall database load ratio to assess the query’s impact on system performance.
   Performance Analysis	Provides performance scores and diagnostic results based on overall analysis. Analyzes detailed resource usage such as CPU, disk, cache hit rate, and wait time to visually identify bottleneck areas during query execution.
   Key Issues Found	Summarizes detected major issues.
   Optimization Recommendations	Suggests optimized queries based on identified issues.

Filtering the searched results

You can filter the retrieved results based on the following criteria:

• Access Type: Filter results by Full Scan or Index Scan.

• Object Name: Filter results by the object name.

Adding the access type filtering conditions

1. In the Filter option at the top of the screen, click the button.

2. In Edit filter, select the Access Type item and the Equal condition, then choose either FULL or INDEX.

3. Click the Apply button.

4. To apply the selected conditions to the SQL list, click the button.

Note

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

Adding the object name filtering conditions

1. In the Filter option at the top of the screen, click the button.

2. In Add filter, select the Object Name item and the Equal condition.

3. Click the empty input field and choose the desired object name from the displayed object list.

4. Click the Apply button.

5. To apply the selected conditions to the SQL list, click the button.

Note

Adding and Deleting Filter Conditions

• To add a filtering condition, click the Add button and repeat steps 1~5. Added conditions are applied using the AND (&&) operator.

• To delete specific conditions while adding, click the button on the right side of the filter condition. To delete all conditions, click the Delete All button.

• To quickly remove all applied conditions from the Filter option, click the button.

Modifying the filter conditions

1. Click the applied item in the Filter option at the top of the screen.

2. In the Edit filter window, edit the desired item and click the Apply button.

Plan Change History

In the Plan Change History tab, even if the SQL ID is the same, performance can be affected when the execution plan changes.
By detecting and monitoring plan changes made by the optimizer, you can prevent unnecessary changes and maintain consistent SQL performance.

1. Go to the Plan Change Historys tab in Analysis > SQL Plan Analysis.

2. Select the query time and instance.

3. Set the Time and Instance options, then click the button.

4. In the Plan Change Count section, select a specific time period to view the list of plan changes that occurred during that time.

   • Plan Change Count section: A bar chart showing the number of plan changes that occurred by time period.

Checking the plan change details

1. In the Plan Change Historys tab of SQL Plan Analysis, click a specific change item from the list.

2. In the Query section, details before and after the plan change are displayed.
   Compare the differences to identify the cause of performance changes.

   • In the Query section, click the button in the upper-right corner to view the section in a new window.

3. To close the Query section, click the button in the upper-right corner.

Filtering the searched results

You can filter the query results based on the following criteria.

• sql_id

• sql_hash_value

• after_plan_hash_value

• before_plan_hash_value

Adding the filter conditions

1. In the Filter option, click .

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

Adding and Deleting Filter Conditions

• To add a filtering condition, click the Add button and repeat steps 1~5. Added conditions are applied using the AND (&&) operator.

• To delete specific conditions while adding, click the button on the right side of the filter condition. To delete all conditions, click the Delete All button.

• To quickly remove all applied conditions from the Filter option, click the button.

Modifying the filter conditions

1. Click the applied item in the Filter option at the top of the screen.

2. In the Edit filter window, edit the desired item and click the Apply button.