How to run SQL tuning advisor manually in Oracle Database

by

How to run SQL tuning advisor manually in Oracle Database| In Oracle Database, the SQL Tuning Advisor is a powerful tool that helps optimize the performance of SQL queries. It analyzes SQL statements and provides recommendations to improve their execution plans. Here’s a step-by-step guide on how to run the SQL Tuning Advisor manually:

Table of Contents

Run SQL tuning advisor manually

SQL tuning advisor
  1. Connect to the Database:

    First, connect to your Oracle Database using a SQL client tool such as SQL*Plus, SQL Developer, or SQLcl. You will need appropriate privileges (e.g., DBA or ADMIN) to execute the SQL Tuning Advisor.
  2. Identify the SQL Statement:

    Choose the SQL statement that you want to tune. You can get the SQL_ID of the statement by querying the V$SQL or V$SQLAREA view, or by capturing it from the application logs to run sql tuning advisor for sql_id.
  3. Create a SQL Tuning Task:

    To run the SQL Tuning Advisor manually, you need to create a SQL Tuning Advisor Task. This task will be associated with the SQL statement you want to tune. Execute the following PL/SQL block to create the task:
DECLARE
  my_task_name VARCHAR2(30) := 'My_Tuning_Task';
  sql_id VARCHAR2(13) := 'YOUR_SQL_ID_HERE';
  task_id NUMBER;
BEGIN
  task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id          => sql_id,
    scope           => DBMS_SQLTUNE.SCOPE_COMPUTE,
    time_limit      => 60,
    task_name       => my_task_name,
    description     => 'Tuning Task for SQL_ID: ' || sql_id
  );
  DBMS_OUTPUT.PUT_LINE('Task ID: ' || task_id);
END;
/

Replace 'YOUR_SQL_ID_HERE' with the SQL_ID of your chosen SQL statement. You can also modify the time_limit parameter to set the maximum time (in seconds) allowed for the tuning task to execute.

  1. Execute the SQL Tuning Task: Once the Task is created, you can execute it using the following PL/SQL block:
DECLARE
  task_name VARCHAR2(30) := 'My_Tuning_Task'; -- Use the task name you specified in the previous step
  execution_id NUMBER;
BEGIN
  execution_id := DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
  DBMS_OUTPUT.PUT_LINE('Execution ID: ' || execution_id);
END;
/

SQL Tuning Advisor recommendations.

The SQL Tuning Advisor will now analyze the SQL statement and generate tuning recommendations.

  1. Monitor the Tuning Task:

    You can monitor the progress of the tuning task using the DBA_ADVISOR_LOG view. This view provides information about the various stages of the tuning process, including any findings and recommendations.

    SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘task_name’) AS report FROM DUAL;
  2. View the Recommendations:

    Once the tuning task is complete, you can view the tuning recommendations by querying the DBA_ADVISOR_FINDINGS view. This view contains the details of the recommendations, including statistics and execution plans that may improve the performance of the SQL statement.

  3. Implement the Recommendations:

    Based on the tuning recommendations, you can choose to implement the changes in your SQL statement or review them for applicability in your specific context.
  4. Complete or Drop the Task (Optional):

    After implementing the recommendations, you can mark the sql tuning task as complete using the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER procedure. Alternatively, you can drop the task using the DBMS_SQLTUNE.DROP_TUNING_TASK procedure.

Note: Running the SQL Tuning Advisor manually gives you more control over the tuning process. However, Oracle Enterprise Manager (OEM) also provides a user-friendly interface to run the SQL Tuning Advisor and view recommendations through the SQL Tuning Advisor GUI.

Remember to test the recommendations in a test environment before applying them to production to ensure they positively impact the performance of your SQL statements.

Leave a Comment