Friday, November 15, 2013

How to enable automatically implemented SQL profiles recommended by the SQL Tuning Advisor

During the maintanace window of an Oracle 11g database, several out-of-the box jobs are scheduled to run. One of them is the Automatic SQL Tuning Advisor, which will come up with recommendations on how to improve the performance of your most performance intensive SQL statements.

One of the findings of the Automatic SQL Tuner may be that a profile can improve the performance of the SQL statement.
You can set up oracle to automatically implement the SQL Profile for you. Here is how:

First, make sure the required parameters are set:

alter system set optimizer_use_sql_plan_baselines=true scope=both; (default)
alter system set optimizer_capture_sql_plan_baselines=true scope=both;
Check the status of the Automatic Tuning job:
SELECT client_name, status, consumer_group, window_group
FROM   dba_autotask_client
WHERE  client_name = 'sql tuning advisor';
If not enabled already, enable the automatic tuning job:
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'sql tuning advisor',
    operation => NULL,
    window_name => NULL);
END;
/
Then customize so that profiles are automatically implemented:
BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'SYS_AUTO_SQL_TUNING_TASK',
    parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
Done!
Note: This feature is not necessarily beneficial for your particular database. Try it out carefully and observe the overall results of the peformance.

To disable automatic SQL tuning, use the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor', 
    operation => NULL, 
    window_name => NULL);
END;
/

No comments:

Post a Comment