Using the SQL Tuning Advisor

In this recipe, we will see how to use the SQL Tuning Advisor to tune our queries.

Getting ready

To use the SQL Tuning Advisor, we need a special privilege; connect as SYSDBA and grant ADVISOR privilege to user SH:

CONNECT / AS SYSDBA
GRANT ADVISOR TO SH;

How to do it...

The following steps will demonstrate the SQL Tuning Advisor:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Define an SQL Tuning Task for a single query:

   DECLARE
    l_task VARCHAR2(30);
    l_sql CLOB;
   BEGIN
    l_sql := 'SELECT AMOUNT_SOLD FROM sh.SALES S ' ||
     'WHERE S.CUST_ID IN ( '||
     'SELECT C.CUST_ID FROM sh.CUSTOMERS C ' ||
     'WHERE C.CUST_CREDIT_LIMIT IN (:l1, :l2, :l3))';
   

    l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
     sql_text => l_sql,
     bind_list => sql_binds(anydata.ConvertNumber(10000),
       anydata.ConvertNumber(11000),
       anydata.ConvertNumber(15000)),
     user_name => 'SH',
     scope => 'COMPREHENSIVE',
     time_limit => 120,
     task_name => 'test_tuning_task',
     description...