SQL Access Advisor(Tuning a single sql)

SQL Access Advisor Overview

The SQL Access Advisor gives you the recommendations about performance tuning. For example it may recommend you to create materialized views, partitions, and/or indexes..You should just give it sql/sqls which are somehow problematic.For example sql which needs big amount of time to execute.

Now  I will discuss Quick Tune, because it is a simple way to tune sqls and also it is widely used . But its “disadvantage” may be counted that it tunes just one sql than multiple sqls(which can be performed by workloads).

Privileges Needed to Use the SQL Access Advisor

You need to have the ADVISOR privilege to manage or use the SQL Access Advisor.

If that user does not have SELECT privileges to a particular table, the SQL Access Advisor neglects  the statement referencing the table. To avoid missing recommendations, the current database user must have SELECT privileges on the tables.

Performing a Quick Tune

QUICK_TUNE procedure accepts as its input a task_name and a SQL statement text or id. It will then create a task and workload and execute that task.

BEGIN
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,
                        'MY_QUICKTUNE_TASK',
                        'SELECT COUNT(*)
                         FROM CLIENTS');
END;

To see the result:

SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('MY_QUICKTUNE_TASK')
FROM dual;