top of page
  • Writer's pictureArun Kumar

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within a fraction of seconds.Due to time constraint (under normal query execution), sometimes optimizer will choose wrong execution plan for a query. We can force to run database optimizer to run a tuning mode so that optimizer can perform more analysis on a given SQL.

Note: use sql tuning advisor only on highly resource intensive queries.

Some of the analysis performed by the optimizer when it is running in tuning mode are

  • Statistical analysis

  • Access path analysis

  • SQL structure analysis

Create tuning task

First of all get the sql id of the statement that you would like to tune. You can get this information via AWR report or other database reports

  l_sql_tune_task_id  VARCHAR2(100);
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
            sql_id      => 'fxdbrc4jhqn5r',
            scope       => DBMS_SQLTUNE.scope_comprehensive,
            time_limit  => 60,
            task_name   => 'fxdbrc4jhqn5r_tuning_task',
            description => 'Tuning task for statement fxdbrc4jhqn5r.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

Execute tuning task

Once the task is defined, you must execute it

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

Status of tuning task

Once you execute the tuning task, you should be able to find the status of the task

SELECT task_name, status 
FROM dba_advisor_log 
WHERE task_name like 'fxdbrc4jhqn5r_tuning_task';

Display recommendations

Once the tuning task is executed successfully, the recommendations can be displayed using below

SET LONG 10000;
SELECT DBMS_SQLTUNE.report_tuning_task('fxdbrc4jhqn5r_tuning_task') AS recommendations FROM dual;

Drop tuning task

Once you get the recommendations, you can drop the tuning task

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'fxdbrc4jhqn5r_tuning_task');


Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

The first step is to understand how instance size is configured. There are two main configurations: One Instance Size Separate SGA and PGA One Instance Size Oracle MEMORY_TARGET parameter defines the

bottom of page