• Arun Kumar

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within 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.


DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  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);
END;
/


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;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('fxdbrc4jhqn5r_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24



Drop tuning task


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


BEGIN
  DBMS_SQLTUNE.drop_tuning_task (task_name => 'fxdbrc4jhqn5r_tuning_task');
END;
/


1,151 views

Recent Posts

See All

Reclaim Unused Space in Oracle

Over a period of time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space i

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Generate Execution Plan in Oracle

While working on SQL performance tuning, you must know what execution plan optimiser is generating. This execution plan defines how the SQL statement will be executed. There are multiple methods to ge