DBMS_SQLTUNE - Oracle SQL Tuning Advisor Guide

DBMS_SQLTUNE is Oracle’s package for programmatically interacting with the SQL Tuning Advisor. It creates and executes tuning tasks against individual SQL statements or SQL Tuning Sets, then provides structured recommendations including SQL Profiles, index suggestions, statistics refresh, and query restructuring guidance.

Overview

What SQL Tuning Advisor Analyzes

Recommendation Type	Description
SQL Profile	Stores additional optimizer statistics to improve the execution plan without changing SQL
New Index	Suggests indexes that would benefit the query
Statistics	Flags stale or missing object statistics
Restructure SQL	Identifies anti-patterns (e.g., implicit type conversions, non-SARGable predicates)
Alternative Plan	Identifies better plans from the cursor cache or AWR history

Typical Workflow

1. Identify a poorly performing SQL (by SQL_ID from V$SQL or AWR)
2. CREATE_TUNING_TASK   → Define what to tune and how long to spend
3. EXECUTE_TUNING_TASK  → Run the advisor analysis
4. REPORT_TUNING_TASK   → Review findings and recommendations
5. ACCEPT_SQL_PROFILE   → Optionally apply a SQL Profile recommendation
6. DROP_TUNING_TASK     → Clean up after review

CREATE_TUNING_TASK

Creates a named tuning task from a SQL ID, SQL text, or SQL Tuning Set.

-- Syntax variants (three overloads)

-- 1. By SQL ID (from cursor cache or AWR)
DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id        IN VARCHAR2,
  plan_hash_value IN NUMBER   := NULL,   -- tune specific plan
  scope         IN VARCHAR2  := 'COMPREHENSIVE',  -- or 'LIMITED'
  time_limit    IN NUMBER    := 1800,    -- seconds (default 30 min)
  task_name     IN VARCHAR2  := NULL,    -- auto-generated if NULL
  description   IN VARCHAR2  := NULL
) RETURN VARCHAR2;                        -- returns task name

-- 2. By SQL text
DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_text      IN CLOB,
  bind_list     IN SQL_BINDS := NULL,
  user_name     IN VARCHAR2  := NULL,
  scope         IN VARCHAR2  := 'COMPREHENSIVE',
  time_limit    IN NUMBER    := 1800,
  task_name     IN VARCHAR2  := NULL,
  description   IN VARCHAR2  := NULL
) RETURN VARCHAR2;

-- 3. By SQL Tuning Set
DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sqlset_name   IN VARCHAR2,
  sqlset_owner  IN VARCHAR2  := NULL,
  scope         IN VARCHAR2  := 'COMPREHENSIVE',
  time_limit    IN NUMBER    := 3600,
  task_name     IN VARCHAR2  := NULL,
  description   IN VARCHAR2  := NULL
) RETURN VARCHAR2;

Create Tuning Task by SQL ID

-- Tune a specific SQL statement from the cursor cache
DECLARE
  l_task_name VARCHAR2(100);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => '7x2kkv8407k1d',
    scope       => 'COMPREHENSIVE',
    time_limit  => 300,    -- 5 minutes max analysis time
    task_name   => 'TUNE_ORDER_QUERY_01',
    description => 'Tuning task for order summary report SQL'
  );
  DBMS_OUTPUT.PUT_LINE('Task created: ' || l_task_name);
END;
/

-- Tune a specific plan hash value (when a statement has multiple plans)
DECLARE
  l_task_name VARCHAR2(100);
BEGIN
  -- Find plan hash values first
  -- SELECT sql_id, plan_hash_value, executions, elapsed_time/1e6 elapsed_sec
  -- FROM v$sql WHERE sql_id = '7x2kkv8407k1d';

  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id          => '7x2kkv8407k1d',
    plan_hash_value => 3792681213,
    scope           => 'COMPREHENSIVE',
    time_limit      => 600,
    task_name       => 'TUNE_ORDER_PLAN_01'
  );
  DBMS_OUTPUT.PUT_LINE('Task: ' || l_task_name);
END;
/

Create Tuning Task by SQL Text

-- Tune ad-hoc SQL not in the cursor cache
DECLARE
  l_sql_text  CLOB;
  l_task_name VARCHAR2(100);
BEGIN
  l_sql_text :=
    'SELECT c.customer_name, COUNT(o.order_id) AS order_count,
            SUM(o.total_amount) AS total_spend
     FROM   customers c
     JOIN   orders o ON o.customer_id = c.customer_id
     JOIN   order_lines ol ON ol.order_id = o.order_id
     WHERE  o.order_date >= TRUNC(SYSDATE) - 90
       AND  c.country_code = :p_country
     GROUP BY c.customer_name
     HAVING SUM(o.total_amount) > 10000
     ORDER BY total_spend DESC';

  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text    => l_sql_text,
    user_name   => 'APP_USER',
    scope       => 'COMPREHENSIVE',
    time_limit  => 300,
    task_name   => 'TUNE_CUSTOMER_SPEND',
    description => 'Customer spend summary - last 90 days'
  );
  DBMS_OUTPUT.PUT_LINE('Task: ' || l_task_name);
END;
/

Create Tuning Task from AWR

-- Tune a SQL statement from AWR history (no longer in cursor cache)
DECLARE
  l_task_name VARCHAR2(100);
  l_db_id     NUMBER;
  l_snap_id   NUMBER;
BEGIN
  SELECT dbid INTO l_db_id FROM v$database;

  -- Get a recent snapshot ID
  SELECT MAX(snap_id) INTO l_snap_id FROM dba_hist_snapshot;

  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    begin_snap  => l_snap_id - 4,   -- snap range covering the poor performance
    end_snap    => l_snap_id,
    sql_id      => '7x2kkv8407k1d',
    scope       => 'COMPREHENSIVE',
    time_limit  => 600,
    task_name   => 'TUNE_FROM_AWR_01',
    description => 'Tuning from AWR snapshot range'
  );
  DBMS_OUTPUT.PUT_LINE('AWR-based task: ' || l_task_name);
END;
/

EXECUTE_TUNING_TASK

Runs the advisor analysis. This may take time depending on time_limit.

-- Syntax
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name IN VARCHAR2);

-- Execute and monitor
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_ORDER_QUERY_01');
  DBMS_OUTPUT.PUT_LINE('Execution complete.');
END;
/

-- Monitor task status during execution
SELECT
  task_name,
  status,
  execution_start,
  execution_end,
  ROUND((SYSDATE - execution_start) * 86400) AS elapsed_seconds
FROM dba_advisor_tasks
WHERE task_name = 'TUNE_ORDER_QUERY_01';

-- Task status values: INITIAL, EXECUTING, COMPLETED, ERROR, INTERRUPTED, CANCELLED
-- Monitor all tuning tasks
SELECT
  task_name,
  owner,
  status,
  created,
  last_modified,
  description
FROM dba_advisor_tasks
WHERE advisor_name = 'SQL Tuning Advisor'
ORDER BY created DESC
FETCH FIRST 20 ROWS ONLY;

REPORT_TUNING_TASK

Generates a human-readable report of findings and recommendations.

-- Syntax
DBMS_SQLTUNE.REPORT_TUNING_TASK(
  task_name     IN VARCHAR2,
  type          IN VARCHAR2 := 'TEXT',  -- 'TEXT' or 'HTML'
  level         IN VARCHAR2 := 'TYPICAL',  -- 'BASIC', 'TYPICAL', 'ALL'
  section       IN VARCHAR2 := 'ALL',   -- 'SUMMARY', 'FINDINGS', 'PLAN', 'INFORMATION', 'ALL'
  object_id     IN NUMBER   := NULL,
  result_limit  IN NUMBER   := NULL
) RETURN CLOB;

-- Print full text report
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_ORDER_QUERY_01') FROM dual;

-- Print just the findings summary
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
  task_name => 'TUNE_ORDER_QUERY_01',
  level     => 'TYPICAL',
  section   => 'FINDINGS'
) FROM dual;

-- Print the recommended execution plan
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
  task_name => 'TUNE_ORDER_QUERY_01',
  section   => 'PLAN'
) FROM dual;

-- HTML report (for saving to file or web display)
DECLARE
  l_report CLOB;
  l_file   UTL_FILE.FILE_TYPE;
BEGIN
  l_report := DBMS_SQLTUNE.REPORT_TUNING_TASK(
    task_name => 'TUNE_ORDER_QUERY_01',
    type      => 'HTML',
    level     => 'ALL'
  );

  l_file := UTL_FILE.FOPEN('REPORT_DIR', 'tune_report.html', 'w', 32767);
  -- Write the CLOB in chunks
  DECLARE
    l_offset INTEGER := 1;
    l_amount BINARY_INTEGER := 32767;
    l_buffer VARCHAR2(32767);
  BEGIN
    WHILE l_offset <= DBMS_LOB.GETLENGTH(l_report) LOOP
      DBMS_LOB.READ(l_report, l_amount, l_offset, l_buffer);
      UTL_FILE.PUT(l_file, l_buffer);
      l_offset := l_offset + l_amount;
    END LOOP;
  END;
  UTL_FILE.FCLOSE(l_file);
END;
/

Querying Findings Programmatically

-- Query advisor findings without reading the full report
SELECT
  f.task_name,
  f.finding_id,
  f.type,
  f.message,
  f.benefit
FROM dba_advisor_findings f
WHERE f.task_name = 'TUNE_ORDER_QUERY_01'
ORDER BY f.benefit DESC NULLS LAST;

-- Get specific recommendations
SELECT
  r.task_name,
  r.rec_id,
  r.benefit,
  r.annotation
FROM dba_advisor_recommendations r
WHERE r.task_name = 'TUNE_ORDER_QUERY_01'
ORDER BY r.benefit DESC NULLS LAST;

-- Get the DDL for recommended indexes
SELECT
  a.task_name,
  a.attr1  AS ddl_statement
FROM dba_advisor_actions a
WHERE a.task_name = 'TUNE_ORDER_QUERY_01'
  AND a.command = 'CREATE INDEX'
ORDER BY a.rec_id;

ACCEPT_SQL_PROFILE

Accepts a SQL Profile recommendation from a completed tuning task. The profile is stored in the data dictionary and applied automatically whenever the optimizer parses the target SQL.

-- Syntax
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name     IN VARCHAR2,
  object_id     IN NUMBER   := NULL,  -- NULL accepts first/best profile
  task_owner    IN VARCHAR2 := NULL,
  name          IN VARCHAR2 := NULL,  -- custom profile name
  description   IN VARCHAR2 := NULL,
  category      IN VARCHAR2 := 'DEFAULT',
  status        IN VARCHAR2 := 'ENABLED',
  replace       IN BOOLEAN  := FALSE,
  force_match   IN BOOLEAN  := FALSE   -- TRUE: match by normalised SQL text
);

-- Accept the SQL Profile from a completed task
BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name   => 'TUNE_ORDER_QUERY_01',
    name        => 'PROF_ORDER_QUERY_01',
    description => 'SQL Profile for order summary report',
    status      => 'ENABLED',
    replace     => TRUE
  );
  DBMS_OUTPUT.PUT_LINE('SQL Profile accepted.');
END;
/

-- Verify the profile was created
SELECT
  name,
  sql_text,
  status,
  force_matching,
  type,
  category,
  created,
  last_modified
FROM dba_sql_profiles
WHERE name = 'PROF_ORDER_QUERY_01';

-- Confirm the profile is being used by a SQL statement
SELECT
  s.sql_id,
  s.sql_profile,
  s.executions,
  ROUND(s.elapsed_time / NULLIF(s.executions, 0) / 1e6, 3) AS avg_elapsed_sec,
  s.plan_hash_value
FROM v$sql s
WHERE s.sql_profile = 'PROF_ORDER_QUERY_01';

-- Disable/enable a profile without dropping it
BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(
    name        => 'PROF_ORDER_QUERY_01',
    attribute_name => 'STATUS',
    value       => 'DISABLED'
  );
END;
/

-- Drop a profile when no longer needed
BEGIN
  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROF_ORDER_QUERY_01');
END;
/

DROP_TUNING_TASK

Removes a tuning task and all associated findings. Always clean up tasks after review.

BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'TUNE_ORDER_QUERY_01');
  DBMS_OUTPUT.PUT_LINE('Task dropped.');
END;
/

-- Drop all advisor tasks older than 30 days
BEGIN
  FOR r IN (
    SELECT task_name
    FROM dba_advisor_tasks
    WHERE advisor_name = 'SQL Tuning Advisor'
      AND created < SYSDATE - 30
      AND status IN ('COMPLETED', 'ERROR', 'INTERRUPTED')
  ) LOOP
    BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK(r.task_name);
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Could not drop: ' || r.task_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

SQL Tuning Sets (STS)

A SQL Tuning Set is a named collection of SQL statements with their execution context. It is the standard way to tune a workload rather than individual statements.

Creating and Loading an STS

-- Create an empty STS
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'WORKLOAD_STS',
    description => 'Top 50 SQL by elapsed time - peak period'
  );
END;
/

-- Load top SQL from the cursor cache
DECLARE
  l_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM TABLE(
      DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
        basic_filter   => 'elapsed_time > 1000000',  -- > 1 second elapsed
        ranking_measure1 => 'elapsed_time',
        result_limit   => 50
      )
    ) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_name     => 'WORKLOAD_STS',
    populate_cursor => l_cursor,
    load_option     => 'MERGE',
    update_option   => 'ACCUMULATE'
  );
  CLOSE l_cursor;
END;
/

-- Load top SQL from AWR
DECLARE
  l_cursor   DBMS_SQLTUNE.SQLSET_CURSOR;
  l_begin_snap NUMBER;
  l_end_snap   NUMBER;
BEGIN
  SELECT MIN(snap_id), MAX(snap_id)
  INTO   l_begin_snap, l_end_snap
  FROM   dba_hist_snapshot
  WHERE  begin_interval_time > SYSDATE - 1;  -- last 24 hours

  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM TABLE(
      DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
        begin_snap     => l_begin_snap,
        end_snap       => l_end_snap,
        basic_filter   => 'elapsed_time > 5000000',  -- > 5 seconds
        ranking_measure1 => 'elapsed_time',
        result_limit   => 100
      )
    ) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_name     => 'WORKLOAD_STS',
    populate_cursor => l_cursor,
    load_option     => 'MERGE'
  );
  CLOSE l_cursor;
END;
/

-- View contents of an STS
SELECT
  sql_id,
  ROUND(elapsed_time / 1e6, 2)   AS elapsed_sec,
  executions,
  ROUND(elapsed_time / NULLIF(executions, 0) / 1e6, 3) AS avg_elapsed_sec,
  cpu_time,
  buffer_gets,
  disk_reads,
  SUBSTR(sql_text, 1, 100) AS sql_preview
FROM TABLE(
  DBMS_SQLTUNE.SELECT_SQLSET('WORKLOAD_STS')
)
ORDER BY elapsed_time DESC;

-- Create a tuning task over the entire STS
DECLARE
  l_task VARCHAR2(100);
BEGIN
  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sqlset_name => 'WORKLOAD_STS',
    scope       => 'COMPREHENSIVE',
    time_limit  => 3600,   -- 1 hour for a full workload
    task_name   => 'TUNE_WORKLOAD_STS',
    description => 'Workload-level tuning - top 100 SQL by elapsed time'
  );
  DBMS_OUTPUT.PUT_LINE('STS task: ' || l_task);
END;
/

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('TUNE_WORKLOAD_STS');

-- Drop the STS when done
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET('WORKLOAD_STS');
END;
/

Automatic SQL Tuning Task

Oracle’s background SYS_AUTO_SQL_TUNING_TASK runs automatically during the maintenance window. You can query its findings without running manual tasks.

-- View findings from the automatic SQL tuning task
SELECT
  f.task_name,
  f.execution_name,
  f.type,
  f.message,
  f.benefit
FROM dba_advisor_findings f
WHERE f.task_name = 'SYS_AUTO_SQL_TUNING_TASK'
ORDER BY f.benefit DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY;

-- Check automatic SQL profiles created
SELECT
  name,
  sql_text,
  status,
  type,
  created,
  last_modified
FROM dba_sql_profiles
WHERE type = 'AUTO'
ORDER BY created DESC;

-- Review unimplemented recommendations from auto task
SELECT
  f.task_name,
  f.execution_name,
  f.message,
  a.command,
  a.attr1 AS recommended_action
FROM dba_advisor_findings f
JOIN dba_advisor_actions a
  ON a.task_name = f.task_name
  AND a.rec_id = f.finding_id
WHERE f.task_name = 'SYS_AUTO_SQL_TUNING_TASK'
  AND a.command IN ('CREATE INDEX', 'GATHER_STATS', 'ACCEPT_SQL_PROFILE')
ORDER BY f.benefit DESC NULLS LAST
FETCH FIRST 25 ROWS ONLY;

-- Check auto-tuning task schedule
SELECT
  job_name,
  enabled,
  state,
  last_start_date,
  last_run_duration,
  next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'AUTO_SQL_TUNING_PROG';

-- Configure auto tuning task time limit (default 3600 seconds)
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL
  );
  -- Or adjust time limit per window using DBMS_SCHEDULER
END;
/

Comparing Execution Plans

-- Compare current plan against the plan SQL Tuning Advisor recommends
-- First, get the SQL handle and plan hash from DBA_SQL_PROFILES

-- View current execution plan for a SQL ID
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7x2kkv8407k1d', NULL, 'ALLSTATS LAST'));

-- Compare two plan hash values side by side using AWR plans
SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_AWR(
    sql_id          => '7x2kkv8407k1d',
    plan_hash_value => 3792681213,
    db_id           => NULL,
    format          => 'TYPICAL'
  )
);

-- View all stored plans for a SQL ID in AWR
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = '7x2kkv8407k1d'
ORDER BY plan_hash_value;

-- Compare plan stability across AWR snapshots
SELECT
  s.sql_id,
  s.plan_hash_value,
  COUNT(DISTINCT s.snap_id)                            AS snap_count,
  MIN(sn.begin_interval_time)                          AS first_seen,
  MAX(sn.end_interval_time)                            AS last_seen,
  ROUND(AVG(s.elapsed_time_delta /
        NULLIF(s.executions_delta, 0)) / 1e6, 3)      AS avg_elapsed_sec,
  SUM(s.executions_delta)                             AS total_executions
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn ON sn.snap_id = s.snap_id
WHERE s.sql_id = '7x2kkv8407k1d'
  AND s.executions_delta > 0
  AND sn.begin_interval_time > SYSDATE - 30
GROUP BY s.sql_id, s.plan_hash_value
ORDER BY avg_elapsed_sec;

Best Practices

Scoping Analysis

Use scope => 'COMPREHENSIVE' for critical SQL — limited scope skips index recommendations.
Set realistic time_limit values. A 60-second limit is enough for simple SQL; complex queries with many join combinations may need 10–30 minutes.
Filter STS loads with basic_filter to avoid analysing trivial fast-running SQL. Focus on statements consuming meaningful CPU or elapsed time.

SQL Profiles

Test profiles in a non-production environment before accepting in production. A profile can improve or worsen performance depending on data distribution at acceptance time.
Use force_match => TRUE cautiously. It matches SQL by normalised text, ignoring literal values — useful for reducing cursor proliferation but can apply a profile to queries with very different selectivity.
Review profiles periodically. As data volumes change, an accepted profile may eventually produce a suboptimal plan. Check DBA_SQL_PROFILES and compare with current execution stats.

Task Hygiene

Drop completed tasks after review. Tasks consume space in the SYSAUX tablespace via the ADVISOR component.
Do not accumulate duplicate STS entries. Use load_option => 'MERGE' and update_option => 'ACCUMULATE' to combine execution statistics rather than creating duplicates.
Monitor V$ONLINE_REDEF and DBA_ADVISOR_TASKS to detect stuck or long-running tuning tasks.

Required Privileges

Action	Privilege
Create and execute tuning tasks for own SQL	`ADVISOR` system privilege
Tune SQL in other schemas	`DBA` role or `SELECT ANY DICTIONARY` + `ADVISOR`
Accept SQL profiles	`ADMINISTER SQL TUNING SET` + `ADVISOR`
Access automatic tuning task	`DBA` role
Create SQL Tuning Sets	`CREATE ANY SQL TUNING SET`

Oracle AWR - Source for SQL IDs and execution history to feed into tuning tasks
DBMS_STATS - Fix stale statistics issues identified by SQL Tuning Advisor
Performance Analysis Scripts - Scripts for identifying top SQL candidates for tuning

DBMS_SQLTUNE - Oracle SQL Tuning Advisor Guide

DBMS_SQLTUNE - Oracle SQL Tuning Advisor Guide

Overview

What SQL Tuning Advisor Analyzes

Typical Workflow

CREATE_TUNING_TASK

Create Tuning Task by SQL ID

Create Tuning Task by SQL Text

Create Tuning Task from AWR

EXECUTE_TUNING_TASK

REPORT_TUNING_TASK

Querying Findings Programmatically

ACCEPT_SQL_PROFILE

DROP_TUNING_TASK

SQL Tuning Sets (STS)

Creating and Loading an STS

Automatic SQL Tuning Task

Comparing Execution Plans

Best Practices

Scoping Analysis

SQL Profiles

Task Hygiene

Required Privileges

Related Topics