Automatic SQL Tuning in Oracle
Automate SQL performance tuning with Oracle's tools.
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
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;
/