利用SPA（SQL Performance Analyzer）对比两个SQL Tuning Set

简单介绍：
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对比sql性能。这两次之间只模拟了参数修改。

--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.创建sql spa 分析任务

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.执行如下分析任务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.比较这两个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.结果获取

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('compare_2sets',

                                       'HTML',

                                       'TYPICAL',

                                       'ALL',

                                       NULL,

                                       100,

                                       NULL,

                                       NULL,

                                       NULL)

  FROM DUAL;

结果展示：


后记：可以根据报告，给参数修改、升级、配置变更、系统迁移等带来的影响做参考，对sql等做相应优化。
本处传参数没有用到比较维度（Comparison Metric），默认是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/，如需转载，请注明出处，否则将追究法律责任。

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