How to Run the SQL Tuning Advisor

🧱 How to Run SQL Tuning Advisor (STA) in Oracle

The SQL Tuning Advisor (STA) is a powerful tool in Oracle that helps database administrators identify and resolve performance issues in SQL statements. This guide provides a step-by-step approach to using STA effectively.

In this guide, we'll walk you through a clean and safe method to create a new tuning task, execute it, check its status, and view the recommendations.

🔧 Step-by-Step Instructions

1️⃣ Create the Tuning Task for the Given SQL_ID

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'abc123xyz7890',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'abc123xyz7890_tuning_task',
                          description => 'Tuning task for statement abc123xyz7890');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Note: Replace 'abc123xyz7890' with your actual SQL_ID.

2️⃣ Execute the Tuning Task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'abc123xyz7890_tuning_task');

3️⃣ Check the Status of the Tuning Task

SELECT TASK_NAME, STATUS 
FROM DBA_ADVISOR_LOG 
WHERE TASK_NAME = 'abc123xyz7890_tuning_task';

4️⃣ View the Tuning Advice

SET LONG 65536
SET LONGCHUNKSIZE 65536
SET LINESIZE 999
SET PAGESIZE 999

SELECT DBMS_SQLTUNE.report_tuning_task('abc123xyz7890_tuning_task') FROM dual;

Note: Share this output with the team requesting the STA analysis.

⚙️ Additional Options

🗑️ Drop a Tuning Task

EXEC DBMS_SQLTUNE.drop_tuning_task('abc123xyz7890_tuning_task');

⏸️ Interrupt a Tuning Task

EXEC DBMS_SQLTUNE.interrupt_tuning_task(task_name => 'abc123xyz7890_tuning_task');

▶️ Resume a Tuning Task

EXEC DBMS_SQLTUNE.resume_tuning_task(task_name => 'abc123xyz7890_tuning_task');

⚠️ Handling Common Errors

❌ Error: ORA-13780 – SQL Statement Does Not Exist

This error occurs when the SQL_ID isn't present in the cursor cache but exists in the AWR snapshots. To address this, identify the relevant snapshot IDs and create the tuning task using them:

DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap => 51164,
                          end_snap   => 51165,
                          sql_id     => 'abc123xyz7890',
                          scope      => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 3600,
                          task_name  => 'abc123xyz7890_tuning_task',
                          description=> 'Tuning task for statement abc123xyz7890');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Note: Replace 51164 and 51165 with your actual snapshot IDs.

⏱️ Error: ORA-13639 – Operation Interrupted Due to Timeout

This indicates that the tuning task exceeded its time limit. To resolve this, increase the time_limit parameter when creating the tuning task:

DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap => 51164,
                          end_snap   => 51165,
                          sql_id     => 'sample123sqlid',
                          scope      => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 7200, -- Increased time limit in seconds
                          task_name  => 'sample123sqlid_tuning_task',
                          description=> 'Tuning task for statement sample123sqlid');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Adjust the time_limit value depending on the complexity of the SQL statement.

🎯 This method is an efficient way for Oracle DBAs to run the SQL Tuning Advisor without having to manually intervene and continuously adjust settings.

DBA NAVIGATOR

Search This Blog