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