SQL Performance AnalyzerによるSQLパフォーマンス比較実行例(API)

Oracle Learning Libraryにあるチュートリアルの一つである
Assessing Impact of Change on Workload Using SQL Performance Analyzer
の操作を、Enterprise Managerの代わりにAPIで実行した場合の例です
SQL*Plusから実行することを前提に書いています

conn system/Password@orcl

--create an STS
variable sts_name varchar2(20)
EXEC :sts_name := 'HR_WORKLOAD_API';

BEGIN
        dbms_sqltune.create_sqlset(
                sqlset_name => :sts_name,
                sqlset_owner =>'APPS'
        );
END;
/

--load sql to STS
DECLARE
 sqlset_cur dbms_sqltune.sqlset_cursor;
BEGIN
 OPEN sqlset_cur FOR
  SELECT VALUE(P)
   FROM TABLE(
    DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
     attribute_list => 'typical', --required in 11.1 and lower
     basic_filter => 'UPPER(PARSING_SCHEMA_NAME) = ''APPS'' AND UPPER(SQL_TEXT) LIKE ''SELECT /% MY_QUERY%'''
    )
   ) P;
 DBMS_SQLTUNE.LOAD_SQLSET(
  sqlset_name=> :sts_name,
  populate_cursor=> sqlset_cur,
  load_option => 'MERGE',
  update_option => 'ACCUMULATE',
  sqlset_owner=>'APPS');
END;
/

--check if the sql are loaded as expected
set pagesize 100
col name for a20
col owner for a15
SELECT name, owner, statement_count FROM DBA_SQLSET;


--Create analysis task
variable spa_task  VARCHAR2(64)
EXEC :spa_task := 'SQLPA_TEST_API';
variable stmt_task VARCHAR2(64)

BEGIN
 :stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
   task_name => :spa_task,
   sqlset_name => :sts_name,
   sqlset_owner => 'APPS'
 );
END;
/

--First trial
BEGIN
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => :spa_task,
  execution_type => 'test execute',
  execution_name => 'first trial'
 );
END;
/

--Make changes here

--Second trial
BEGIN
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => :spa_task,
  execution_type => 'test execute',
  execution_name => 'second trial'
 );
END;
/

--Compare the two trials
BEGIN
 DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(
  task_name => :spa_task,
  parameter => 'comparison_metric',
  value => 'buffer_gets'
 );
END;
/

BEGIN
 DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  task_name => :spa_task,
  execution_type => 'compare performance',
  execution_name => 'compared result1',
  execution_params => dbms_advisor.arglist(
   'execution_name1',
   'first trial',
   'execution_name2',
   'second trial',
   'workload_impact_threshold',
   0,
  'sql_impact_threshold',
   0)
 );
END;
/

--Print report
set pagesize 0
set linesize 150
set long 1000000
set trimspool on
var rep clob
col rep for a150

BEGIN
 :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
  task_name => :spa_task,
  type => 'TEXT',
  level => 'TYPICAL',
  section => 'SUMMARY',
  execution_name => 'compared result1'
 );
END;
/

spool /home/oracle/work/report/spa_basic.txt
print :rep
spool off