使用SQL tuning advisor(STA)自动优化SQL

      Oracle 10g之后的优化器支持两种模式,一个是normal模式,一个是tuning模式。在大多数情况下,优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划,因为它需要在尽可能短的时间,通常是几秒或毫秒级来对当前的SQL语句进行解析并生成执行计划。因此并不能保证SQL语句每次都是使用最佳的执行计划。而tuning模式则将高负载的SQL语句直接扔给优化器,优化器来自动对其进行详细的分析,调试并给出建议,这就是Oracle 提供的Automatic Tuning Optimizer,即自动调整优化器。Oracle 自动调整优化器通过SQL调优建议器(SQL tuning advisor)来体现。

1、SQL tuning的基本步骤      a、鉴别需要调整的高负载SQL或者Top SQL      b、寻找可改进的执行计划      c、实施能够改进的执行计划以提高SQL效率 2、如何tuning SQL      a、检查是否为优化器设置了合理的参数(optimizer_mode,optimizer_index_caching,optimizer_index_cost_adj,以及相关cache size)      b、检查SQL语句所涉及的对象是否存在过时的统计信息或者倾斜列是否缺少直方图等      c、通过添加提示来引导SQL语句使用正确的访问路径,以及连接方式等      d、重构等价的SQL语句以使得SQL更高效(如最小化基表及中间结果集,避免列运算,列上的函数,null值,不等运算使得索引失效)      e、添加合理的索引或物化视图以及移除冗余索引,分散I/O等 3、Automatic Tuning Optimizer 做什么?      a、分析统计信息          优化器执行计划产生期间记录当前SQL语句涉及对象的统计信息的类型以及哪些被使用或哪些是需要的          当统计信息记录完成后自动调整优化器会比对与查询相关的这些对象的统计信息是否可用或过时或非均衡列缺少直方图等          针对上述的操作之后得到哪些对象没有统计信息以及哪些对象缺少统计信息以及额外的统计信息用于生成report           b、分析访问路径          优化器会分析当前SQL所使用的访问路径是否合理,也就是分析基于表的访问方式,如全表扫描,索引扫描等          自动调整优化器会基于谓词尝试假设性的推断来创建合理的索引,也就是建议通过添加或修改相应的索引来提高性能      c、SQL结构分析          优化器会建议对于一些具有较大影响的SQL语句作结构性调整及转换(基于内部规则),如未嵌套的子查询,重写物化视图,视图合并等          基于语法以及语义结构的分析与调整,如谓词列上的运算,UNION与UNION ALL的使用,NOT IN, NOT EXIST之间替换等          对中间结果集以及连接方式等实现一些预估的分析      d、SQL profiling          SQL profiling 内置于优化器,就是一个剖析工具,基于上述得到的信息对当前的SQL进行剖析,以检查出导致性能糟糕的故障点          所有上述分析得到的结果以及辅助信息最后以sql profile的形式表现出来,供用户来判断是否接受          当用户接受这些profile,下次处于normal模式时,相同的sql语句会使用这个profile          可以对profile进行启用,停用,以及修改,因此即使表发生较大的变化,profile依旧能使得SQL受益

4、Automatic Tuning Optimizer与SQL tuning advisor结构图 

5、STA可tuning的方式      STA提供OEM图形界面以及API方式进行tuning,本文主要描述API即dbms_sqltune.create_tuning_task方式      下面是可被create_tuning_task接受的API方式        a、直接提供SQL语句文本        b、引用共享池中的SQL语句(sql_id)        c、引用awr自动工作负载中的SQL语句(sql_id)        d、建议SQL调优集(批量tuning) 6、演示SQL tuning 

--环境
scott@ORA11G> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--创建演示表 
scott@ORA11G> CREATE TABLE t
  2  NOLOGGING
  3  AS
  4     SELECT *
  5       FROM dba_source,
  6            (    SELECT *
  7                   FROM DUAL
  8             CONNECT BY ROWNUM < 5);

Table created.

--执行SQL 语句
scott@ORA11G> SELECT COUNT (*)
  2    FROM t a
  3   WHERE a.ROWID > (SELECT MIN (b.ROWID)
  4                      FROM t b
  5                     WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE AND a.line = b.line);

  COUNT(*)
----------
   1872756

1 row selected.

--开始SQL自动调整并报告结果
--脚本tune_last_sql.sql中包含了创建调优任务、开始执行调优、以及报告调优成果。脚本内容见文章尾部
scott@ORA11G> @tune_last_sql

RECS
-----------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_833
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 05/22/2013 15:06:06
Completed at       : 05/22/2013 15:07:17

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 44tg722u0ypqh
SQL Text   : SELECT COUNT (*)
               FROM t a
              WHERE a.ROWID > (SELECT MIN (b.ROWID)
                                 FROM t b
                                WHERE a.owner = b.owner AND a.name = b.name
             AND a.TYPE = b.TYPE AND a.line = b.line)

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SCOTT"."T" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T',
            estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>
            'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

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

1- Original
-----------
Plan hash value: 1985065416

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |   134 |       | 42648   (1)| 00:08:32 |
|   1 |  SORT AGGREGATE       |         |     1 |   134 |       |            |          |
|*  2 |   HASH JOIN           |         |   129K|    16M|   195M| 42648   (1)| 00:08:32 |
|   3 |    TABLE ACCESS FULL  | T       |  2590K|   165M|       | 11596   (1)| 00:02:20 |
|   4 |    VIEW               | VW_SQ_1 |  2590K|   165M|       | 11674   (1)| 00:02:21 |
|   5 |     HASH GROUP BY     |         |  2590K|   165M|       | 11674   (1)| 00:02:21 |
|   6 |      TABLE ACCESS FULL| T       |  2590K|   165M|       | 11596   (1)| 00:02:20 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND
              "A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")
       filter("A".ROWID>"MIN(B.ROWID)")

--上面的report总共分为3个部分,分别是SQL调优的基本信息、SQL调优的建议findings、以及SQL对应的执行计划部分
--在基本信息部分包含了SQL调优的任务名称,状态,执行,完成时间,对应的SQL完整语句等
--在finding部分则给出本次调优所得到的成果,如本次是提示缺少统计信息
--在执行计划部分则给出了当前SQL语句的执行计划以及谓词信息

-->接下来根据建议来收集统计信息
scott@ORA11G> BEGIN
  2     DBMS_STATS.gather_table_stats (ownname            => 'SCOTT',
  3                                    tabname            => 'T',
  4                                    estimate_percent   => DBMS_STATS.auto_sample_size,
  5                                    method_opt         => 'FOR ALL COLUMNS SIZE AUTO');
  6  END;
  7  /

PL/SQL procedure successfully completed.

-->对原SQL语句增加order提示并执行
scott@ORA11G> SELECT /*+ ordered */COUNT (*)
  2    FROM t a
  3   WHERE a.ROWID > (SELECT MIN (b.ROWID)
  4                      FROM t b
  5                     WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE AND a.line = b.line);

  COUNT(*)
----------
   1872756

1 row selected.

--再次调优SQL语句
scott@ORA11G> @tune_last_sql

RECS
-----------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_849
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 05/22/2013 21:26:07
Completed at       : 05/22/2013 21:26:42

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : fsp3852n56gf8
SQL Text   : SELECT /*+ ordered */COUNT (*)
             FROM t a
             WHERE a.ROWID > (SELECT MIN (b.ROWID) from t b
             WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE
             AND a.line = b.line)

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 67.95%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_849',
            task_owner => 'SCOTT', replace => TRUE);

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

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

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |     1 |       |       |   218K  (1)| 00:43:47 |
|   1 |  SORT AGGREGATE        |           |     1 |       |       |            |          |
|   2 |   VIEW                 | VM_NWVW_2 |   551K|       |       |   218K  (1)| 00:43:47 |
|*  3 |    FILTER              |           |       |       |       |            |          |
|   4 |     HASH GROUP BY      |           |   551K|    51M|  1197M|   218K  (1)| 00:43:47 |
|*  5 |      HASH JOIN         |           |    11M|  1031M|   145M| 37646   (1)| 00:07:32 |
|   6 |       TABLE ACCESS FULL| T         |  2497K|   116M|       | 11596   (1)| 00:02:20 |
|   7 |       TABLE ACCESS FULL| T         |  2497K|   116M|       | 11596   (1)| 00:02:20 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A".ROWID>MIN("B".ROWID))
   5 - access("A"."OWNER"="B"."OWNER" AND "A"."NAME"="B"."NAME" AND
              "A"."TYPE"="B"."TYPE" AND "A"."LINE"="B"."LINE")

2- Using SQL Profile
--------------------
Plan hash value: 1985065416

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |   116 |       | 70117   (1)| 00:14:02 |
|   1 |  SORT AGGREGATE       |         |     1 |   116 |       |            |          |
|*  2 |   HASH JOIN           |         |  2025K|   224M|   145M| 70117   (1)| 00:14:02 |
|   3 |    TABLE ACCESS FULL  | T       |  2497K|   116M|       | 11596   (1)| 00:02:20 |
|   4 |    VIEW               | VW_SQ_1 |  2497K|   159M|       | 41851   (1)| 00:08:23 |
|   5 |     HASH GROUP BY     |         |  2497K|   116M|   153M| 41851   (1)| 00:08:23 |
|   6 |      TABLE ACCESS FULL| T       |  2497K|   116M|       | 11596   (1)| 00:02:20 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND
              "A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")
       filter("A".ROWID>"MIN(B.ROWID)")

-------------------------------------------------------------------------------

--针对上述的SQL语句,SQL调优器找到了一个更为高效的执行计划,并提示我们接受该执行计划,如下
--A potentially better execution plan was found for this statement.
--Recommendation (estimated benefit: 67.95%)
--Consider accepting the recommended SQL profile

--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612

--接受SQL profile
scott@ORA11G> exec DBMS_SQLTUNE.accept_sql_profile (task_name => 'TASK_849', task_owner => 'SCOTT', REPLACE => TRUE);

PL/SQL procedure successfully completed.

--当接受SQL profile后,我们再次来执行原来带order提示的SQL语句
scott@ORA11G> set autot trace exp;
scott@ORA11G> SELECT /*+ ordered */COUNT (*)
  2               FROM t a
  3               WHERE a.ROWID > (SELECT MIN (b.ROWID) from t b
  4               WHERE a.owner = b.owner AND a.name = b.name AND a.TYPE = b.TYPE
  5               AND a.line = b.line);

Execution Plan
----------------------------------------------------------
Plan hash value: 1985065416

-----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |   116 |       | 70117   (1)| 00:14:02 |
|   1 |  SORT AGGREGATE       |         |     1 |   116 |       |            |          |
|*  2 |   HASH JOIN           |         |  2025K|   224M|   145M| 70117   (1)| 00:14:02 |
|   3 |    TABLE ACCESS FULL  | T       |  2497K|   116M|       | 11596   (1)| 00:02:20 |
|   4 |    VIEW               | VW_SQ_1 |  2497K|   159M|       | 41851   (1)| 00:08:23 |
|   5 |     HASH GROUP BY     |         |  2497K|   116M|   153M| 41851   (1)| 00:08:23 |
|   6 |      TABLE ACCESS FULL| T       |  2497K|   116M|       | 11596   (1)| 00:02:20 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="ITEM_1" AND "A"."NAME"="ITEM_2" AND
              "A"."TYPE"="ITEM_3" AND "A"."LINE"="ITEM_4")
       filter("A".ROWID>"MIN(B.ROWID)")

Note
-----
   - SQL profile "SYS_SQLPROF_013ecc70b5f70000" used for this statement

scott@ORA11G> set autot off;

--上面的autotrace中,最后一部分表明当前的SQL语句使用了存储的SQL profile的执行计划

7、相关视图      DBA_ADVISOR_LOG      DBA_ADVISOR_TASKS      DBA_ADVISOR_FINDINGS      DBA_ADVISOR_RECOMMENDATIONS      DBA_ADVISOR_RATIONALE      DBA_SQLTUNE_STATISTICS      DBA_SQLTUNE_BINDS      DBA_SQLTUNE_PLANS

8、演示用到的脚本

SET ECHO OFF TERMOUT ON FEEDBACK OFF VERIFY OFF  
SET SCAN ON
SET LONG 1000000 LINESIZE 180
COL recs FORMAT a135

VARIABLE tuning_task VARCHAR2(30)

DECLARE
  l_sql_id v$session.prev_sql_id%TYPE;
BEGIN
  SELECT prev_sql_id INTO l_sql_id
  FROM v$session
  WHERE audsid = userenv('SESSIONID');
  
  :tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
  dbms_sqltune.execute_tuning_task(:tuning_task);
END;
/

SELECT dbms_sqltune.report_tuning_task(:tuning_task) as recs 
FROM dual;

SET VERIFY ON FEEDBACK ON 

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏前端桃园

知识体系解决迷茫的你

最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

22240
来自专栏华章科技

穿越十年后看互联网+:家电行业的金矿在哪里?

现在市场上炒得火热的智能家居未来出路在何方?做智能家居的创业者应该注意哪些机会?传统家电厂商又到底如何借助互联网进行转型?本文以智能空调为例,用故事的形式,提前...

8410
来自专栏Ken的杂谈

【系统设置】CentOS 修改机器名

18230
来自专栏web前端教室

你可以从面试中学到什么?

讲一下我对面试的一些。。。“偏见”,哈哈,熟悉我的同学们一定要批判的读接下来的内容哈。

12300
来自专栏非著名程序员

「我真的没有改需求」

12010
来自专栏FSociety

SQL中GROUP BY用法示例

GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

5.2K20
来自专栏非著名程序员

这是对付产品经理的一副毒药,程序员慎入

程序员和产品经理的日常就像是一对天生的冤家,为了需求的实现,几乎天天在争吵。这不,就在昨天各大技术和产品群里一个程序员暴打产品经理的视频火了,被广泛传播。

12520
来自专栏haifeiWu与他朋友们的专栏

复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

29940
来自专栏腾讯大讲堂的专栏

白底黑字or黑底白字,眼睛更喜欢哪一个?

12310
来自专栏腾讯NEXT学位

今天我就说三句话

11620

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励