How to use SQL Tuning Advisor ?

Oracle SQL Tuning Advisor (STA) is a powerful diagnostic tool that analyzes SQL statements and provides recommendations to improve performance — including SQL profiles, statistics gathering, and more.

What is SQL Tuning Advisor?

SQL Tuning Advisor is an Oracle performance optimization tool designed to analyze individual SQL statements and recommend ways to improve their execution efficiency. It examines the execution plan, optimizer statistics, and database workload to identify performance bottlenecks such as inefficient access paths, missing or stale statistics, and suboptimal join methods. Based on its analysis, it provides actionable recommendations like creating SQL profiles, gathering statistics, or restructuring queries—allowing DBAs to improve performance without modifying application code.

The advisor is typically used for high-impact or long-running SQL statements identified through AWR reports, SQL Monitor, or the cursor cache. It can be run manually or scheduled automatically, making it suitable for both proactive tuning and reactive troubleshooting. By leveraging SQL Tuning Advisor, organizations can achieve consistent query performance, reduce database load, and ensure optimal use of system resources with minimal operational risk.

Pre-Requisites

The user that has logged in must have at least one of the following privileges

ADVISOR
ADMINISTER SQL TUNING SET

Step 1: Find the SQL Id

SELECT sql_id, sql_text
FROM v$sql
WHERE sql_text LIKE '%employee%';

Step 2: Create a SQL Tuning Task

BEGIN
  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => '7m9f2x3b8yq1k',
    scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit  => 300,
    task_name   => 'emp_query_tuning_task',
    description => 'Tuning employee query'
  );
END;
/

Step 3: Execute the SQL Tuning Task

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
    task_name => 'emp_query_tuning_task'
  );
END;
/

Step 4: View Tuning Recommendations

SET LONG 100000
SET PAGESIZE 1000

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
  'emp_query_tuning_task'
) AS report
FROM dual;

Step 5: Review and Use the Recommendations

Carefully review and use the recommendations if deemed appropriate

Step 6: Drop the Tuning Task

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('emp_query_tuning_task');

Never forget to Cleanup the created tuning tasks

References:

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-advisor.html

That’s All Folks, See ya!