Oracle Database 11gR2 Performance Tuning Cookbook

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