Creating SQL Tuning Sets

In this recipe, we will see how we can store a group of SQL statements along with their execution context and statistics, obtaining a so-called SQL Tuning Set.

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...