Automatic Maintaining SQL Tuning Advisor.

Automatic Maintaining SQL Tuning Advisor.

      It is always enabled by default, but the SQL Profiles are enabled unless that is ACCEPT_SQL_PROFILES modify the parameter.

- We can see the report and change the parameter through ACCEPT_SQL_PROFILES DBMS_AUTO_SQLTUNE package

- Reviewed the report

VARIABLE my_rept CLOB;

BEGIN

  :my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(

    begin_exec   => NULL,

    end_exec     => NULL,

    type         => 'TEXT',

    level        => 'TYPICAL',

    section      => 'ALL',

    object_id    => NULL,

    result_limit => NULL);

END;

/

PRINT :my_rept

- Enable  SQL Profiles automatically

- Before implementation in production you have to insure about Pro.. and Cons...

- Beware of.. doing it on production

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER

(parameter => 'ACCEPT_SQL_PROFILES',

value => 'TRUE'

);

END;

/

- For disable it,  use below command

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER

(parameter => 'ACCEPT_SQL_PROFILES',

 value => 'FALSE'

);

END;

/