Improving the Oracle Optimizer - Oracle Database 11gR2 Performance Tuning - page 322

Getting ready

To create a SQL Tuning Set, we need the ADMINISTER SQL TUNING SET privilege, so
we grant this privilege to SH user, which will be used in this recipe.

CONNECT / AS SYSDBA
GRANT ADMINISTER SQL TUNING SET TO sh;

How to do it...

The following steps will demonstrate how to create and use SQL Tuning Sets:

1. Connect to SH schema:

CONNECT sh@TESTDB/sh

2. Execute some queries to populate the cursor cache:

SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_CITY
FROM CUSTOMERS
ORDER BY CUST_CITY;

SELECT * FROM (
SELECT
CUST_ID, CUST_FIRST_NAME,
CUST_LAST_NAME, CUST_YEAR_OF_BIRTH
FROM CUSTOMERS
ORDER BY CUST_YEAR_OF_BIRTH DESC
)
WHERE ROWNUM < 11;

SELECT C.CUST_FIRST_NAME, C.CUST_LAST_NAME, N.COUNTRY_NAME
FROM CUSTOMERS C, COUNTRIES N
WHERE N.COUNTRY_ID BETWEEN C.COUNTRY_ID
AND C.COUNTRY_ID + 10;

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 (10000, 11000, 15000));

SELECT prod_id, cust_id, time_id
FROM sh.SALES
ORDER BY amount_sold desc;