DBMS_SQLTUNE + SQL Performance Analyzer 实战小记

根据上一篇文章中生产的SPA任务， 通过DBMS_SQLTUNE提出的建议优化sql

一. dbms_sqltune.create_tuning_task可以基于sql text,sql_id, workload repository,sqlset,SPA Task。本处以前面实验用的SPA Task为例。
--根据spa task_name创建任务(通过其他方式创建任务理应更简单)
DECLARE
  L_SQL_TUNE_TASK_ID VARCHAR2(100);
BEGIN
  L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK(SPA_TASK_NAME    => 'compare_2sets2',
                                                        SPA_TASK_OWNER   => 'HNYD',
                                                        SPA_COMPARE_EXEC => NULL,
                                                        BASIC_FILTER     => NULL,
                                                        TIME_LIMIT       => 18000,
                                                        TASK_NAME        => 'T_SPA_TASK1',
                                                        DESCRIPTION      => 'Test create_tuning_task - SPA Task format');
  DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
END;
/
----上面发现DJANGO_SET1，DJANGO_SET2以及compare_2sests不存在，这与个人清理掉system和sysaux表空间的操作有关，用上篇中类似方式重新创建即哥。
---而后发现超时（执行上面的过程不会报错，最后sqltune的结果展示不全，内容显示错误），因为整个sql tune sets 内容太多，我第一次超时时间只设置了3分钟，后面重新设置较长时间后成功获取整个spa任务比较中所有sql。
2.DBMS_SQLTUNE常见利用
    begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'T_SPA_TASK1'); end;
 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('T_SPA_TASK1')  FROM dual;
--结果展示
 GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : T_SPA_TASK1
Tuning Task Owner                 : HNYD
Workload Type                     : SQL Tuning Set
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 18000
Completion Status                 : COMPLETED
Started at                        : 03/05/2016 22:41:51
Completed at                      : 03/05/2016 23:15:21
SQL Tuning Set (STS) Name         : DJANGO_SET3
SQL Tuning Set Owner              : HNYD
Number of Statements in the STS   : 604


-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 232
Number of SQLs in the Report                 : 138
Number of SQLs with Findings                 : 79
Number of SQLs with Statistic Findings       : 1
Number of SQLs with Alternative Plan Findings: 29
Number of SQLs with SQL profiles recommended : 30
Number of SQLs with Index Findings           : 1
Number of SQLs with SQL Restructure Findings : 14
Number of SQLs with Errors                   : 59


-------------------------------------------------------------------------------
    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
       155 3gjhh2hgfnnb2                      99.94%                           
       176 24bu9cpx0wmxq                      99.94%                           
        68 bn4b3vjw2mj3u                      99.62%                           
        30 g5m0bnvyy37b1                      99.52%                          1
        60 7aw21806wpgzp                      99.50%                          1
       138 a7a75aupxfgq1                      96.63%                           
        13 0mdckw03m07v3                                     95.00%            
       163 3nc51h4uv661j                      89.40%                           
        20 g5m0bnvyy37b1                      89.37%                          1
        45 aamdkbbaajjgz                      84.03%                           
       203 1tgukkrqj3zhw                      82.42%                           
        15 f5yun5dynkskv                      81.85%                           
        99 22yqbyxqx2fd6                      81.62%                           
......
对很多sql都给了建议，结果过长，贴一小部分。
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。


  Recommendation (estimated benefit: 99.62%)
  ------------------------------------------
  - 考虑接受推荐的 SQL 概要文件。
    execute dbms_sqltune.accept_sql_profile(task_name => 'T_SPA_TASK1',
            object_id => 68, task_owner => 'HNYD', replace => TRUE);


2- Alternative Plan Finding
---------------------------
  通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。


  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.


  id plan hash  last seen            elapsed (s)  origin          note            
  -- ---------- -------------------- ------------ --------------- ----------------
   1 1781002567  2016-03-05/18:25:58        0.006 AWR                             
   2 2672219561              unknown        0.011 SPA             original plan   
   3  930046576  2016-03-01/17:00:34        0.016 AWR                             
   4 3270362138  2016-03-05/18:25:58        0.043 AWR                             


  Recommendation
  --------------
  - 请考虑使用最佳平均用时为计划创建 SQL 计划基线。
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'T_SPA_TASK1',
            object_id => 68, owner_name => 'HNYD', plan_hash_value =>
            1781002567);


-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 此 SQL 语句至少缺少一个重要绑定值。指导分析的准确性可能取决于提供的所有重要绑定值。


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original With Adjusted Cost
------------------------------
Plan hash value: 2672219561


-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                            | 90001 |    13M|       |  3124   (1)| 00:00:38 |
|   1 |  SORT ORDER BY                         |                            | 90001 |    13M|    16M|  3124   (1)| 00:00:38 |
|*  2 |   FILTER                               |                            |       |       |       |            |          |
……

二、由于分析整个任务中的所有sql太耗时，且spa任务用到的sql tune sets也没有过滤，针对性不强，下面以一个sql为例，同样是 DBMS_SQLTUNE With SQL Performance Analyzer
--  --过滤条件 BASIC_FILTER     => 'sql_id=''67255mtgs6xv4''', TASK_NAME        => 'T_SPA_TASK_ONESQL', 实用性相对上面较强  DECLARE
  L_SQL_TUNE_TASK_ID VARCHAR2(100);
BEGIN
  L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK(SPA_TASK_NAME    => 'compare_2sets2',
                                                        SPA_TASK_OWNER   => 'HNYD',
                                                        SPA_COMPARE_EXEC => NULL,
                                                        BASIC_FILTER     => 'sql_id=''67255mtgs6xv4''',
                                                        TIME_LIMIT       => 1800,
                                                        TASK_NAME        => 'T_SPA_TASK_ONESQL',
                                                        DESCRIPTION      => 'Test create_tuning_task - SPA Task format');
  DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
END;
/

begin DBMS_SQLTUNE.execute_tuning_task(task_name => 'T_SPA_TASK_ONESQL',execution_name => 'exec_onesql'); end;

 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('T_SPA_TASK_ONESQL')  FROM dual;

结果展示：

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : T_SPA_TASK_ONESQL
Tuning Task Owner                 : HNYD
Workload Type                     : SQL Tuning Set
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 03/05/2016 23:32:23
Completed at                      : 03/05/2016 23:32:29
SQL Tuning Set (STS) Name         : DJANGO_SET3
SQL Tuning Set Owner              : HNYD
Number of Statements in the STS   : 604


-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 2
Number of SQLs in the Report                 : 1
Number of SQLs with Findings                 : 1
Number of SQLs with Statistic Findings       : 1
Number of SQLs with Alternative Plan Findings: 1


-------------------------------------------------------------------------------
    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
         2 67255mtgs6xv4          1                                            
         3 67255mtgs6xv4          1                
……
------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- Statistics Finding
---------------------
  表 "HNYD"."DB_GROUP_SCHEDULERS" 的优化程序统计信息已失效。


  Recommendation
  --------------
  - 考虑收集此表及其索引的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'HNYD', tabname =>
            '表名保密..', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO', cascade => TRUE);


  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要此表及其索引的最新统计信息。


2- Alternative Plan Finding
---------------------------
  通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。


  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.


  id plan hash  last seen            elapsed (s)  origin          note            
  -- ---------- -------------------- ------------ --------------- ----------------
   1  838008028              unknown        0.022 SPA                             
   2  776620345  2016-03-05/20:00:11        0.061 AWR             original plan   


  Recommendation
  --------------
  - 请考虑使用最佳平均用时为计划创建 SQL 计划基线。
    execute dbms_sqltune.create_sql_plan_baseline(task_name =>
            'T_SPA_TASK_ONESQL', object_id => 2, owner_name => 'HNYD',
            plan_hash_value => 838008028);


-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 优化程序不能合并位于执行计划的行 ID 12 处的视图。. 优化程序不能合并包含 "ORDER BY" 子句的视图, 除非此语句为 "DELETE"
  或 "UPDATE", 并且父查询为此语句中的顶级查询。.
- 优化程序不能合并位于执行计划的行 ID 5 处的视图。. 优化程序不能合并包含 "ORDER BY" 子句的视图, 除非此语句为 "DELETE" 或
  "UPDATE", 并且父查询为此语句中的顶级查询。.
- 优化程序不能合并位于执行计划的行 ID 2 处的视图。. 优化程序不能合并包含 "ROWNUM" 伪列的视图。.

结果后面列出了各个计划及按推荐方式处理的变化方式。可做性能优化参考。

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

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