Script to run the Sql tuning advisor from backend

Most of the time we will be in situation to tune a long running query to increase the performance. One of easiest way to do that is to run the sql tuning advisor and implement the suggestion.Let us discus about the easiest way of running the sql tuning advisor in the back-end. You can find the sql tuning advisor script in the following directory.

$ORACLE_HOME/rdbms/admin/sqltrpt.sql

Once you run the above sciprt it wil ask you for the sql_id for which you wanna run the tuning advisor.Provide the sql_id and wait sometime as it takes 5-10 minutes to create and run the tuning task.Most of the time suggestion would be either index or sql profile. Please find the example for sql profile suggestion.

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.96%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_10388',
            task_owner => 'SYS', replace => TRUE);

Please find the example for index suggestion.

1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 83.25%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index APPLSYS.IDX$$_A7CD00001 on
    APPLSYS.WF_LOCAL_ROLES("NAME","ORIG_SYSTEM_ID");

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.  If you choose to create the
    recommended index, consider dropping the index
    "APPLSYS"."WF_LOCAL_ROLES_N2" because it is a prefix of the recommended
    index.
    create index APPLSYS.IDX$$_A7CD00002 on
    APPLSYS.WF_LOCAL_ROLES("DISPLAY_NAME","ORIG_SYSTEM_ID");

Implement the solution based on estimated benefit percentage and cost of the query.