SQL Performance Analyzer, Step 1 Create a SQL Tuning Set

I was dealing with a performance issue for a particular application and wanted to partition the table then measure the impact of the partitioning based on the the SQL uses that table.  This got me thinking how could make use of SQL performance Analyzer for this that way I could have the before and after analysis.

First step would be to gather the SQL that executes against the table from the live production system.  We can do this by creating a SQL tuning set and putting that SQL into that SQL tuning set.  We would base this SQL tuning set on tuning set from the cache over a period time to capture the SQL into our SQL tuning set for the SQL that executes on the table. 

Create a SQL Tuning Set

We can use the DBMS_SQLTUNE package to create the SQL tuning set with the SQL that executes against the table.  For example if we want to capture the SQL execute against the table for a 24 hour period, take a sample at 5 minute intervals it would looks like the following.  We create a SQL Tuning Set called STS_SPA_1, then setup the capture of the SQL Tuning Set to come from the cursor cache over a the 24 hour period for just the SQL that contains ORDER for the ORDERS and ORDERS_ITEMS tables.

---------------------------------------------------------------------------

-- Script: spa_create_sts.sql

-- Author; Michael R. Messina, Managing Consultant 

-- Rolta-TUSC, Infrastructure Services

--

-- Setup a SQL Tuning Set to create a for the SQL

-- This will serve SQL Performance Analyzer well or allow us to create profiles.  

-- From there we can make generate before and after pictures

-- Then evaluate the impact of changes.

---------------------------------------------------------------------------

--

set feedback on

---------------------------------------------------------------------------

-- Set up the SQL Set and what sql we want to make

-- part of the sql tuning set

---------------------------------------------------------------------------

BEGIN

    -- Create the sql set

    DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_SPA_1');

    -- Limit the sql in the set to Just on the ORDERS and ORDER_ITEMS

    -- The CAPTURE_CURSOR_CACHE_SQLSET will collect SQL statements

    -- over a peeriod of time from the cursor cache.  This helps build a more realistic

    -- set of SQL load from the system by capturing the live SQL for 24 hours at 5 min

    -- intervals

    DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(

         sqlset_name => 'STS_SPA_1'

        ,basic_filter=> q'#UPPER(sql_text) LIKE '%ORDER%' #'

        ,time_limit  => 86400

        ,repeat_interval => 300

    );

END;

/

** I would show the steps for Grid Control except the blog would never save with the Grid 

Images. 

The next Step will be to use the SQL Tuning Set to compare a before and after our changes, which I will be doing with SQL Performance Analyzer.  To do this we Create an analysis task..... SQL Performance Analyzer, Step 2 Create Analysis Task