簡(jiǎn)單介紹：
1.A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes
2.?SQL Performance Analyzer
You can use the SQL Performance Analyzer to analyze the impact on SQL?performance of any type of system changes, including:
■ Database upgrade
■ Configuration changes to the operating system or hardware
■ Schema changes
■ Changes to database initialization parameters
■ Refreshing optimizer statistics
■ SQL tuning actions
-------------------------------------------------------------------------------------------------------------------------------------------
SQL tuning set 可以基于Cursor Cache,?queries with a specific query string,?AWR Snapshots,?AWR Baseline,?another SQL Tuning Set. 生成?
本文模擬通過兩次取基于AWR Snapshots生成的SQL Tuning Set，利用SQL Performance Analyzer對(duì)比sql性能。這兩次之間只模擬了參數(shù)修改。

--1.Create?a?SQL?Tuning?Set 1:

BEGIN?dbms_sqltune.create_sqlset('DJANGO_SET1');?END; --Load?SQL?into?the?STS(From?AWR?Snapshots)? select?snap_id,?begin_interval_time,?end_interval_time?from?dba_hist_snapshot?order?by?1;--取snap_idcd 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; /
--2.Create?a?SQL?Tuning?Set 2: BEGIN?dbms_sqltune.create_sqlset('DJANGO_SET2');?END; --Load?SQL?into?the?STS(From?AWR?Snapshots)? select?snap_id,?begin_interval_time,?end_interval_time?from?dba_hist_snapshot?order?by?1;--取snap_id參考 DECLARE? CUR?SYS_REFCURSOR; BEGIN OPEN?CUR?FOR SELECT?VALUE(P) FROM?TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP?=>?93, END_SNAP???=>?123))?P; DBMS_SQLTUNE.LOAD_SQLSET('DJANGO_SET2',?CUR); CLOSE?CUR; END; /

--3.創(chuàng)建sql spa 分析任務(wù) DECLARE ANAME?VARCHAR2(30); BEGIN
ANAME?:=?'compare_2sets';--compare_s2s aname?:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(TASK_NAME?=>?ANAME); DBMS_OUTPUT.put_line(aname); END; /
--4.執(zhí)行如下分析任務(wù)DJANGO_SET1，官方如是說：The content of the SQL tuning set will not be?duplicated by the SQL Performance Analyzer task. Instead, a reference to the SQL?tuning set is recorded in association to the new SQL trial


BEGIN DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME???????????=>?'compare_2sets', EXECUTION_TYPE???=>?'convert?sqlset', EXECUTION_NAME???=>?'third?trial', EXECUTION_PARAMS?=>?DBMS_ADVISOR.ARGLIST('sqlset_name', 'DJANGO_SET1', 'sqlset_owner', 'HNYD')); END; /
--5.同上。DJANGO_SET1 BEGIN DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME???????????=>?'compare_2sets', EXECUTION_TYPE???=>?'convert?sqlset', EXECUTION_NAME???=>?'fourth?trial', EXECUTION_PARAMS?=>?DBMS_ADVISOR.ARGLIST('sqlset_name', 'DJANGO_SET2', 'sqlset_owner', 'HNYD')); END; / --6.比較這兩個(gè)SQL trials（SQL tuning sets）
BEGIN DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(TASK_NAME????????=>?'compare_2sets', EXECUTION_TYPE???=>?'compare', EXECUTION_NAME???=>?'comparison', EXECUTION_PARAMS?=>?DBMS_ADVISOR.ARGLIST('workload_impact_threshold', 0, 'sql_impact_threshold', 0)); END; /

--7.結(jié)果獲取
SELECT?DBMS_SQLPA.REPORT_ANALYSIS_TASK('compare_2sets', 'HTML', 'TYPICAL', 'ALL', NULL, 100, NULL, NULL, NULL) FROM?DUAL;
結(jié)果展示：


后記：可以根據(jù)報(bào)告，給參數(shù)修改、升級(jí)、配置變更、系統(tǒng)遷移等帶來的影響做參考，對(duì)sql等做相應(yīng)優(yōu)化。
本處傳參數(shù)沒有用到比較維度（Comparison Metric），默認(rèn)是ELAPSED_TIME。
可比較維度：elapsed_time (default),cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.，參考如下方式指定。 DBMS_ADVISOR.ARGLIST('sqlset_name',? DJANGO_SET1',?'sqlset_owner',?'HNYD',?'comparion_metric',?'buffer_gets');

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/27349469/viewspace-2029270/，如需轉(zhuǎn)載，請(qǐng)注明出處，否則將追究法律責(zé)任。

轉(zhuǎn)載于:http://blog.itpub.net/27349469/viewspace-2029270/

總結(jié)

以上是生活随笔為你收集整理的利用SPA（SQL Performance Analyzer）对比两个SQL Tuning Set的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。