Master Oracle SQL Tuning with ADDM and SQL Tuning Advisor: A Step‑by‑Step Guide

Using ADDM to Locate SQL

If the AWR report does not reveal the SQL that needs tuning, ADDM can be consulted. The example output shows a top‑SQL statement consuming 17.86 active sessions (61.29% of total activity) and recommends running the SQL Tuning Advisor on a specific SQL_ID.

Finding 1: Top SQL Statements
Impact is 17.86 active sessions, 61.29% of total activity.
----------------------------------------------------------
SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement.

Recommendation 1: SQL Tuning
Estimated benefit is 4.76 active sessions, 16.35% of total activity.
--------------------------------------------------------------------
Action
   Run SQL Tuning Advisor on the SELECT statement with SQL_ID "XXXXXXXXXXX".
   Related Object
      SQL statement with SQL_ID XXXXXXXXXXX.

Rationale
   The SQL spent 99% of its database time on CPU, I/O and Cluster waits.
   This part of database time may be improved by the SQL Tuning Advisor.
   Database time for this SQL was divided as follows: 100% for SQL execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java execution.
   SQL statement with SQL_ID "XXXXXXXXXXX" was executed 1,094,801 times and had an average elapsed time of 0.015 seconds.
   I/O and Cluster wait for INDEX "XXXXXX.XXXXXXXX" with object ID 2133671 consumed 47% of the database time spent on this SQL statement.
   XXXXX为出于隐私进行准换。

Creating and Executing a SQL Tuning Task

The following PL/SQL block creates a comprehensive tuning task for a given sql_id and snapshot range, then executes it.

set autot off
set timing off
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := dbms_sqltune.create_tuning_task(
   begin_snap => 22176, -- start snapshot
   end_snap   => 22184, -- end snapshot
   sql_id     => '2hrbkst309jyj',
   scope      => 'COMPREHENSIVE', -- LIMITED or COMPREHENSIVE
   time_limit => 60, -- minutes
   task_name  => 'tuning_sql_test',
   description=> 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tuning_sql_test');
END;
/

Execute the task:

exec dbms_sqltune.execute_tuning_task('tuning_sql_test');

Check its status:

SELECT task_name, status FROM USER_ADVISOR_TASKS WHERE task_name = 'tuning_sql_test';

Retrieve the tuning report:

set long 999999
set serveroutput on size 999999
set line 120
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tuning_sql_test') FROM dual;

Applying the SQL Profile Recommendation

The first suggestion is to accept the recommended SQL profile and enable parallel execution, which is estimated to improve response time by 99.13% while increasing resource consumption by about 11%.

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 99.13%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution for this statement.
  execute dbms_sqltune.accept_sql_profile(
     task_name => 'tuning_sql_test',
     task_owner=> 'SYS',
     replace   => TRUE,
     profile_type=> DBMS_SQLTUNE.PX_PROFILE);

Executing this query parallel with DOP 128 will improve its response time 99.13% over the original plan, but will increase resource consumption by an estimated 11.03%.

Index Creation Recommendation

The second recommendation is to create a new index, which can dramatically reduce execution time. Three execution plans are compared:

Original plan (hash 612724806) – 00:36:55

With new index (hash 2621731162) – 00:05:53

With index and parallel execution (hash 3522323416) – 00:00:20

2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more indices.

Recommendation (estimated benefit: 84.07%)
------------------------------------------
- Consider running the Access Advisor or creating the recommended index.
  create index XXXXX.IDX$$_5191F0001 on XxXX.XXXXXXxx(SUBSTR("ESN",-1),"STAT");

Rationale
---------
Creating the recommended indices significantly improves the execution plan, though the Access Advisor may provide a more comprehensive set of recommendations.

Explain Plan Comparisons

Three plans are shown to illustrate the impact of indexing and parallelism.

1- Original
-----------
Plan hash value: 612724806
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0  | SELECT STATEMENT   |      | 8052 | 2665K | 184K (1)    | 00:36:55 |
| *1 | COUNT STOPKEY      |      |      |       |             |          |
| *2 | TABLE ACCESS FULL  | xxxxx| 8052 | 2665K | 184K (1)    | 00:36:55 |

2- Using New Indices
--------------------
Plan hash value: 2621731162
| Id | Operation                | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0  | SELECT STATEMENT         |      |10000 | 3310K | 29383 (1)   | 00:05:53 |
| *1 | COUNT STOPKEY            |      |      |       |             |          |
| *2 | TABLE ACCESS BY INDEX ROWID| ... |50325| 16M   | 29383 (1)   | 00:05:53 |
| *3 | INDEX RANGE SCAN          | IDX$$_5191F0001 |46977|       | 115 (0)   | 00:00:02 |

3- Using Parallel Execution
---------------------------
Plan hash value: 3522323416
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0  | SELECT STATEMENT   |      | 8052 | 2665K | 1601 (0)    | 00:00:20 |
| *1 | COUNT STOPKEY      |      |      |       |             |          |
| 2  | PX COORDINATOR     |      |      |       |             |          |
| 3  | PX SEND QC (RANDOM)| :TQ10000|8052|2665K|1601 (0) | 00:00:20 |

Conclusion

By following the ADDM identification, creating a SQL Tuning Advisor task, accepting the suggested SQL profile, and optionally adding the recommended index (with or without parallel execution), the original 36‑minute query can be reduced to under a minute, demonstrating the substantial performance gains achievable through systematic Oracle SQL optimization.