Oracle 11g's SQL Performance Analyzer

create table gark
  (id number not null);
 
 begin
 for i in 1..10000 loop
 insert into gark(id)
  values (i);
 end loop;
 commit;
 end;
 /

set serveroutput off
 
 col id format 99999
 select a.id, b.id
  from gark a, gark b
  where a.id=b.id
  and b.id=500;
 
 select * from table(
  dbms_xplan.display_cursor);

-------------------------------------
 SQL_ID 683kdkrs2dmrk, child number 0
 -------------------------------------
 select a.id, b.id from gark a, gark b where a.id=b.id
 and b.id=500
 
 Plan hash value: 2625395012
 
 ----------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
 ----------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 15 (100)|
 |* 1 | HASH JOIN | | 1 | 26 | 15 (7)|
 |* 2 | TABLE ACCESS FULL| GARK | 1 | 13 | 7 (0)|
 |* 3 | TABLE ACCESS FULL| GARK | 1 | 13 | 7 (0)|
 ----------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - access("A"."ID"="B"."ID")
 2 - filter("A"."ID"=500)
 3 - filter("B"."ID"=500)
 
 Note
 -----
 - dynamic sampling used for this statement

• Firstly, create a new STS:

  BEGIN
   DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'gark_sts',
    description => 'STS for SPA demo');
   END;
   /

• Secondly, add the query to the STS by querying the Cursor Cache:

  accept sql_id prompt "Enter value for sql_id: "
   683kdkrs2dmrk
   
   DECLARE
    l_cursor DBMS_SQLTUNE.sqlset_cursor;
   BEGIN
    OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM TABLE (
    DBMS_SQLTUNE.select_cursor_cache (
    'sql_id = ''&sql_id''', -- basic_filter
    NULL, -- object_filter
    NULL, -- ranking_measure1
    NULL, -- ranking_measure2
    NULL, -- ranking_measure3
    NULL, -- result_percentage
    1) -- result_limit
    ) p;
    DBMS_SQLTUNE.load_sqlset (
    sqlset_name => 'gark_sts',
    populate_cursor => l_cursor);
   END;
   /
   
   PL/SQL procedure successfully completed.

• Finally, you can query the STS content to make sure it is properly registered:

  col sql format a50
   set lines 120
   SELECT sql_id,
    substr(sql_text, 1, 50) sql
    FROM TABLE(
    DBMS_SQLTUNE.select_sqlset (
    'gark_sts'));
   SQL_ID SQL
   ------------- ---------------------
   683kdkrs2dmrk select a.id, b.id
    from gark a, gark b
    where a.i

• Create a SQLPA analysis task that reference the STS:

var v_out char(50)
 begin
  :v_out:=dbms_sqlpa.create_analysis_task(
  sqlset_name => 'gark_sts',
  task_name => 'gark_spa_task');
 end;
 /
 print v_out
 
 V_OUT
 -------------
 gark_spa_task

• Check the task has been created:

col TASK_NAME format a14
 col ADVISOR_NAME format a24
 select TASK_NAME,
  ADVISOR_NAME,
  created
  from DBA_ADVISOR_TASKS
  where task_name='gark_spa_task';
 
 TASK_NAME ADVISOR_NAME CREATED
 -------------- ------------------------ ---------
 gark_spa_task SQL Performance Analyzer 15-AUG-07

• Run the SQLPA Analysis Task:

begin
  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => 'gark_spa_task',
  execution_type => 'TEST EXECUTE',
  execution_name => 'gark_spa_task_before');
 end;
 /

• Monitor the task and its status until it is completed:

col TASK_NAME format a20
 select execution_name,
  status,
  execution_end
  from DBA_ADVISOR_EXECUTIONS
  where task_name='gark_spa_task'
  order by execution_end;
 
 EXECUTION_NAME STATUS EXECUTION
 ------------------------------ ----------- ---------
 gark_spa_task_before COMPLETED 15-AUG-07

create unique index
  gark_idx on gark(id);
 
 Index created.

• Run the SQLPA Analysis Task:

begin
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => 'gark_spa_task',
  execution_type => 'TEST EXECUTE',
  execution_name => 'gark_spa_task_after');
 end;
 /

• Monitor the task and its status until it is completed:

col TASK_NAME format a20
 select execution_name,
  status,
  execution_end
  from DBA_ADVISOR_EXECUTIONS
  where task_name='gark_spa_task'
  order by execution_end;
 
 EXECUTION_NAME STATUS EXECUTION
 ------------------------------ ----------- ---------
 gark_spa_task_before COMPLETED 15-AUG-07
 gark_spa_task_after COMPLETED 15-AUG-07

begin
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => 'gark_spa_task',
  execution_type => 'COMPARE PERFORMANCE',
  execution_name => 'gark_spa_task_compare',
  execution_params => dbms_advisor.arglist(
  'comparison_metric',
  'buffer_gets'));
 end;
 /
 PL/SQL procedure successfully completed.

variable rep CLOB;
 begin
  :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name=>'gark_spa_task',
  type=>'HTML',
  level=>'ALL',
  section=>'ALL');
 end;
 /
 
 SET LONG 100000
 set LONGCHUNKSIZE 100000
 set LINESIZE 200
 set head off
 set feedback off
 set echo off
 spool sts_changes.html
 PRINT :rep
 spool off
 set head on

You'll find the full demo report here.

• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_SQLPLANS
• DBA_ADVISOR_SQLSTATS

begin
  dbms_sqlpa.reset_analysis_task(task_name=>'gark_spa_task');
 end;
 /
 col TASK_NAME format a20
 select execution_name,
  status,
  execution_end
  from DBA_ADVISOR_EXECUTIONS
  where task_name='gark_spa_task'
  order by execution_end;
 
 no rows selected

begin
  dbms_sqlpa.drop_analysis_task(task_name=>'gark_spa_task');
 end;
 /
 
 col TASK_NAME format a14
 col ADVISOR_NAME format a24
 select TASK_NAME,
  ADVISOR_NAME,
  created
  from DBA_ADVISOR_TASKS
  where task_name='gark_spa_task';
 
 no rows selected

• cancel a running analysis task with the cancel_analysis_task procedure
• interrupt a running analysis task with the interrupt_analysis_task procedure
• resume an interrupted analysis task with the resume_analysis_task procedure

drop table gark cascade constraints purge;

• You can easily capture the STS queries from AWR.
• You don't need to rebuild the test system (only the "SELECT" part of the INSERT/UPDATE/DELETE is executed).
• The queries in a STS are only a sample of a real application workload.

• Oracle 11g Performance Tuning Guide - 23.SQL Performance Analyzer
• Oracle 11g PL/SQL Types and Packages Reference - DBMS_SQLPA
• Oracle 11g Reference