SPA（SQL Performance Analyzer）实践小记 step-by-step

最新推荐文章于 2021-04-06 08:12:38 发布

cpyz30116

最新推荐文章于 2021-04-06 08:12:38 发布

阅读量244

点赞数

文章标签：数据库

--1. Create a SQL Tuning Set:

BEGINdbms_sqltune.create_sqlset('DJANGO_SET1');END;

--2.Load SQL into the STS(From AWR Snapshots,load all the queries between two snapshots) --测试环境，SQL不足，加载两个快照间所有sql
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
DECLARE
CUR SYS_REFCURSOR;
BEGIN
OPEN CUR FOR
SELECT VALUE(P)
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP => 84,
END_SNAP => 115)) P;
DBMS_SQLTUNE.LOAD_SQLSET('DJANGO_SET1', CUR);
CLOSE CUR;
END;

--3. CREATE ADVISOR TASK AND SET PARAMETERS( PL/SQL Packages and Types Reference DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER)
DECLARE
STS_TASK VARCHAR2(4000);
BEGIN
STS_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => 'DJANGO_SET1',
TASK_NAME => 'DJANGO_SPA_TASK1');
DBMS_OUTPUT.PUT_LINE(STS_TASK);--DJANGO_SPA_TASK1
END;
/
--4.设置任务参数
BEGIN
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(TASK_NAME => 'DJANGO_SPA_TASK1',
PARAMETER => 'APPLY_CAPTURED_COMPILENV',
VALUE => 1);

DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(TASK_NAME => 'DJANGO_SPA_TASK1',
PARAMETER => 'COMPARISON_METRIC',
VALUE => 'cpu_time + buffer_gets * 10)');
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(TASK_NAME => 'DJANGO_SPA_TASK1',
PARAMETER => 'DEFAULT_EXECUTION_TYPE',
VALUE => 'execute');
END;
/
--5.1执行任务
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_TYPE => 'EXECUTE',
EXECUTION_NAME => 'beore_change');--before
END;
/
--.本处测试修改optimizer_features_enable和optimizer_index_cost_adj参数
SQL> alter system set optimizer_features_enable='10.2.0.4' scope=memory; --11.2.0.4
System altered.
SQL> alter system set optimizer_index_cost_adj=25 scope=memory;--100
System altered.
--5.2系统参数，修改参数后再次执行任务
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_TYPE => 'EXECUTE',
EXECUTION_NAME => 'after_change');
END;
--5.3 细分各维度
/*
elapsed_time (default),cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.
*/
--BUFFER_GETS
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_NAME => 'compare_bg',
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',
'BUFFER_GETS',
'EXECUTION_NAME1',
'beore_change',
'EXECUTION_NAME2',
'after_change'),
EXECUTION_DESC => 'change optimizer_features_enable and optimizer_index_cost_adj');
END;
/
--elapsed_time
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_NAME => 'compare_elapsed_time1',
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',
'elapsed_time',
'EXECUTION_NAME1',
'beore_change',
'EXECUTION_NAME2',
'after_change'),
EXECUTION_DESC => 'change optimizer_features_enable and optimizer_index_cost_adj');
END;
/
-- cpu_time
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_NAME => 'compare_cpu_time1',
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',
'cpu_time',
'EXECUTION_NAME1',
'beore_change',
'EXECUTION_NAME2',
'after_change'),
EXECUTION_DESC => 'change optimizer_features_enable and optimizer_index_cost_adj');
END;
/
-- disk_r eads
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_NAME => 'compare_disk_reads1',
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',
'disk_reads',
'EXECUTION_NAME1',
'beore_change',
'EXECUTION_NAME2',
'after_change'),
EXECUTION_DESC => 'change optimizer_features_enable and optimizer_index_cost_adj');
END;
/
-- direct_writes
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_NAME => 'compare_direct_writes1',
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',
'direct_writes',
'EXECUTION_NAME1',
'beore_change',
'EXECUTION_NAME2',
'after_change'),
EXECUTION_DESC => 'change optimizer_features_enable and optimizer_index_cost_adj');
END;
/
-- optimizer_cost
BEGIN
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME => 'DJANGO_SPA_TASK1',
EXECUTION_NAME => 'compare_optimizer_cost1',
EXECUTION_TYPE => 'COMPARE PERFORMANCE',
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC',
'optimizer_cost',
'EXECUTION_NAME1',
'beore_change',
'EXECUTION_NAME2',
'after_change'),
EXECUTION_DESC => 'change optimizer_features_enable and optimizer_index_cost_adj');
END;
/

--6.获取报告
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1',
'HTML',
'ALL',
'ALL',
EXECUTION_NAME => 'after_change')
FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1',
'HTML',
'ALL',
'ALL',
EXECUTION_NAME => 'beore_change')
FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_elapsed_time1'))
.GETCLOBVAL(0, 0)
FROM DUAL;
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_cpu_time1'))
.GETCLOBVAL(0, 0)
FROM DUAL;
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_disk_reads1'))
.GETCLOBVAL(0, 0)
FROM DUAL;

SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_direct_writes1'))
.GETCLOBVAL(0, 0)
FROM DUAL;
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('DJANGO_SPA_TASK1', 'html', 'typical', 'all', NULL, 100, 'compare_optimizer_cost1'))
.GETCLOBVAL(0, 0)
FROM DUAL;
--7.报告展示示例

参考：

1.How to Load Queries into a SQL Tuning Set (STS) (文档 ID 1271343.1)
2.SQL Performance Analyzer (An Oracle White Paper November 2007)
3.Oracle Database Real Application Testing User’s Guide 11.2
4.Oracle Database PL/SQL Packages and Types Reference 11.2
5.http://www.askmaclean.com/archives/spa-script.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27349469/viewspace-2021799/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27349469/viewspace-2021799/