Databases Reference
In-Depth Information
Defining the SQL Tuning Set helps in monitoring database performance after changes in
configuration, for example, or to test in a separate database—transporting the tuning set
to a different database—the effects of a change to a well-defined set of queries extracted
from the typical workload.
See also
F
The
Using
the
SQL
Tuning
Advisor
and
Configuring
and
using
SQL
Baselines
recipe in this chapter.
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))';
