How to Run SQL Tuning Advisor in Oracle Database – Step-by-Step Practical Guide

Leave a reply

Performance tuning is one of the most important responsibilities of an Oracle DBA. Slow SQL queries can affect application performance, increase server load, and impact end users. Oracle provides a powerful built-in utility called SQL Tuning Advisor that helps identify performance problems and recommends solutions automatically.

In this article, we will learn how to run SQL Tuning Advisor in Oracle Database with a complete practical example.

What is SQL Tuning Advisor?

Oracle SQL Tuning Advisor analyzes SQL statements and provides recommendations to improve performance. It can suggest:

  • Creating indexes
  • Gathering optimizer statistics
  • SQL Profile recommendations
  • Query restructuring
  • Access path improvements

It uses the DBMS_SQLTUNE package internally.

Step 1: Connect to Oracle Database

Login as SYSDBA user.

sqlplus / as sysdba

Step 2: Create a Test User

Create a sample user for testing.

create user tuning identified by tuning;
grant connect, resource, unlimited tablespace to tuning;

Connect with the newly created user.

conn tuning/tuning

Step 3: Create a Large Test Table

Now create a large table with sample data. This table contains 500,000 rows and simulates a production-like workload.

create table emp_test
as
select
rownum emp_id,
'EMP_' || rownum emp_name,
mod(rownum,100) dept_id,
sysdate - mod(rownum,1000) hire_date,
dbms_random.value(1000,50000) salary
from dual
connect by level <= 500000;

Step 4: Gather Table Statistics

Gather optimizer statistics. Statistics help Oracle generate better execution plans.

exec dbms_stats.gather_table_stats(user,'EMP_TEST');

Step 5: Execute a Slow SQL Query

Enable timing first. This query may perform a full table scan depending on available indexes.

set timing on
Set autotrace on
select count(*) from emp_test where dept_id = 50 and salary > 20000;

Execution plan for the query given below:

Execution Plan
----------------------------------------------------------
Plan hash value: 982230031
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    25 |  1040   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP_TEST |  3061 | 76525 |  1040   (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPT_ID"=50 AND "SALARY">20000)

Note: it using full table scan and cost is 1040

Step 6: Find the SQL_ID

Now identify the SQL_ID from memory.

select sql_id,
executions,
elapsed_time/1000000 elapsed_seconds,
sql_text
from v$sql
where sql_text like '%emp_test%'
and sql_text not like '%v$sql%';

Step 7: Create SQL Tuning Advisor Task

This creates and executes the SQL tuning task.

Replace YOUR_SQL_ID with your actual SQL_ID

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'YOUR_SQL_ID',
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 60,
task_name => 'emp_tuning_task',
description => 'SQL tuning demo task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'emp_tuning_task');
END;
/

Step 8: View SQL Tuning Report

Display the advisor recommendations.

set long 10000
select dbms_sqltune.report_tuning_task('emp_tuning_task')
from dual;

Report got form advisory:

SQL> select dbms_sqltune.report_tuning_task('emp_tuning_task')from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
Elapsed: 00:00:01.52
SQL> set long 10000
SQL> /
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : emp_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/22/2026 22:07:07
Completed at       : 05/22/2026 22:07:09
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 02mvbc8n8uw0v
SQL Text   : select count(*) from tuning.emp_test where dept_id = 50 and
             salary > 20000
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
  
  Recommendation (estimated benefit: 98.16%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TUNING.IDX$$_00490001 on TUNING.EMP_TEST("DEPT_ID","SALARY");
  Rationale
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 982230031
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    25 |  1040   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    25 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP_TEST |  3061 | 76525 |  1040   (2)| 00:00:01 |
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPT_ID"=50 AND "SALARY">20000)
2- Using New Indices
--------------------
Plan hash value: 4084213471
--------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
----
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time
   |
--------------------------------------------------------------------------------
----
|   0 | SELECT STATEMENT  |                |     1 |    25 |    19   (0)| 00:00:
01 |
|   1 |  SORT AGGREGATE   |                |     1 |    25 |            |
   |
|*  2 |   INDEX RANGE SCAN| IDX$$_00490001 |  3061 | 76525 |    19   (0)| 00:00:
01 |
DBMS_SQLTUNE.REPORT_TUNING_TASK('EMP_TUNING_TASK')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("DEPT_ID"=50 AND "SALARY">20000 AND "SALARY" IS NOT NULL)
-------------------------------------------------------------------------------
Elapsed: 00:00:01.75

Step 9: Create Recommended Index

Step 10: Execute Query Again

Run the same SQL query again.

Display the execution plan:

select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));

Step 11: Drop the SQL Tunning advisory 

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_tuning_task');
This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply