In this recipe, we will see how to use the SQL Tuning Advisor to tune our queries.
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;
The following steps will demonstrate the SQL Tuning Advisor:
Connect to the
SH
schema:CONNECT sh@TESTDB/sh
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...