SQL Tuning Advisor in Oracle Database  

Step 1: Connect to the Database

sql
-- Connect as a user with appropriate privileges
CONNECT username/password@database

Step 2: Create a SQL Tuning Task

sql
-- Method 1: Create a tuning task for a specific SQL ID
DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => 'your_sql_id',
    scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit  => 3600,
    task_name   => 'SQL_TUNE_TASK_' || 'your_sql_id',
    description => 'Tuning task for SQL ID: your_sql_id'
  );
  
  DBMS_OUTPUT.PUT_LINE('SQL Tuning Task created: ' || l_sql_tune_task_id);
END;
/

Step 3: Execute the SQL Tuning Task

sql
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
    task_name => 'SQL_TUNE_TASK_' || 'your_sql_id'
  );
END;
/

Step 4: Check the Status of the Tuning Task

sql
-- Monitor the status of the task
SELECT task_name, status, advisor_name, created, last_modified
FROM dba_advisor_tasks
WHERE task_name = 'SQL_TUNE_TASK_' || 'your_sql_id';

Step 5: Generate and Review the Recommendations

sql
-- Get the tuning recommendations
SET LONG 100000
SET PAGESIZE 1000
SET LINESIZE 130
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
  task_name => 'SQL_TUNE_TASK_' || 'your_sql_id',
  section   => 'ALL',
  level     => 'ALL',
  type      => 'TEXT'
) AS recommendations
FROM DUAL;

Step 6: Implement the Recommendations (Optional)

sql
-- Implement a specific recommendation (if desired)
BEGIN
  -- Accept SQL profile recommendation
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
    task_name  => 'SQL_TUNE_TASK_' || 'your_sql_id',
    name       => 'PROF_your_sql_id',
    force_match => TRUE  -- Will use this profile for similar statements
  );
END;
/

Step 7: Clean Up (Optional)

sql
-- Drop the tuning task when you're done
BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK(
    task_name => 'SQL_TUNE_TASK_' || 'your_sql_id'
  );
END;
/

Additional Options

To Run Tuning Advisor on Multiple SQL Statements

sql
DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    begin_snap  => 123,  -- Beginning snapshot ID
    end_snap    => 456,  -- Ending snapshot ID
    top_sql     => 'ALL',  -- Or specify a number like 10
    task_name   => 'WORKLOAD_TUNING_TASK',
    description => 'Tuning task for top SQL statements in workload repository'
  );
  
  DBMS_OUTPUT.PUT_LINE('SQL Tuning Task created: ' || l_sql_tune_task_id);
END;
/