Edited at

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

More than 3 years have passed since last update.

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