In the high-pressure world of database performance, finding out why a query is slow is only half the battle. The other half is knowing how to fix it without breaking something else.

Enter the SQL Tuning Advisor. It is like having a senior Oracle Performance Engineer sitting inside your database, ready to analyze your slow SQL and provide a list of "prescriptions" to make it healthy again.

1. What exactly does it do?

The SQL Tuning Advisor doesn't just look at the SQL text; it looks at the entire environment. It performs four main types of analysis:

A. Statistics Analysis

It checks if the statistics on the involved tables are stale or missing.

• The Advice: "Your stats are 3 months old. Gather fresh stats to fix the plan."

B. SQL Profiling

This is the "magic" part. The advisor runs the query (or parts of it) to see if the Optimizer's estimates match reality. If it finds that the Optimizer is wildly wrong, it creates a SQL Profile.

• The Advice: "I've found a better way to estimate these rows. Accept this Profile to fix the plan."

C. Access Path Analysis

It looks for missing indexes. Unlike a human, it can mathematically prove that a new index would reduce the "cost" of the query.

• The Advice: "Create a B-tree index on columns (A, B) to reduce I/O by 80%."

D. SQL Structure Analysis

It looks for "bad" SQL coding patterns, like using NOT IN on nullable columns or unnecessary type conversions.

• The Advice: "Rewrite this subquery as a join to improve efficiency."

2. How to Run the Advisor

You can run the advisor through Enterprise Manager (GUI) or via the command line using the DBMS_SQLTUNE package.

Step 1: Create a Tuning Task

You provide the SQL_ID of the problematic query (which you can find in V$SQL).

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                          sql_id      => 'a5y8277pfu16h',
                          scope       => 'COMPREHENSIVE',
                          time_limit  => 60,
                          task_name   => 'tune_slow_query',
                          description => 'Tuning task for the daily sales report');
END;

Step 2: Execute the Task

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_slow_query');

Step 3: View the Report

This is where you get the human-readable advice.

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_slow_query') FROM DUAL;

3. The "SQL Profile": The Secret Weapon

The most powerful recommendation the advisor can give is a SQL Profile.

A SQL Profile isn't a "hint" and it doesn't lock the plan like a Baseline. Instead, it provides the Optimizer with additional information (like "hey, this join actually results in 5 million rows, not 10"). This allows the Optimizer to pick the correct plan naturally.

To accept it:

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tune_slow_query');

4. Why use the Advisor instead of manual tuning?

• Safety: It tests its theories before suggesting them.

• Speed: It can analyze complex, 20-table joins in minutes—tasks that might take a human hours to "untangle."

• Evidence: It provides a "Benefit %" (e.g., "This change will improve performance by 95%"), giving you the confidence to apply the change in production.

5. Summary: The Advisor Workflow

Pro-Tip: Automatic SQL Tuning

By default, Oracle runs the Automatic SQL Tuning Task every night during the maintenance window. It looks for high-resource SQL and tunes it automatically. In modern versions, it can even implement SQL Profiles automatically if the performance gain is at least 3x!