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.

     

Note

• If the query_text column is selected from the table list, the SQL details window appears. For more information about the SQL details window.

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.