使用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

 

更多参考

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

时间： 2024-09-13 11:41:31

使用SQL tuning advisor(STA)自动优化SQL的相关文章

Begin automatic SQL Tuning Advisor run for special tuning task &amp;quot;SYS_AUTO_SQL_TUNING_TASK&amp;quot;

hu May 29 22:00:00 2014 Setting Resource Manager plan SCHEDULER[0x310A]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Thu May 29 22:00:00 2014 Starting background process VKRM Thu M

STA(SQL Tuning Advisor) SQL调优顾问简介

原文转自:http://blog.csdn.net/tianlesoftware/article/details/5630888 在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计划,加hint,分析统计信息等等.在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor :STA),它是新的DBMS_SQLTUNE包.使用STA一定要保证优化器是CBO模式下. 执行DBMS_SQLTUNE包进行sql优化需要有advis

深入了解SQL Tuning Advisor

1.前言:一直以来SQL调优都是DBA比较费力的技术活,而且很多DBA如果没有从事过开发的工作,那么调优更是一项头疼的工作,即使是SQL调优很厉害的高手,在SQL调优的过程中也要不停的分析执行计划.加HINT.分析统计信息等等.从ORACLE 10G开始,数据库采取了很多智能化的管理工作,其中SQL优化器(SQL Tuning Advisor:STA),大大的提高了DBA进行SQL优化的效率:   2.原理介绍: When SQL statements are executed by the O

Oracle中SQL Tuning Advisor的使用实例

在oracle10g之前,想要优化一个sql语句是比较麻烦,但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具,能大大减少sql调优的工作量,不过要想使用SQL Tuning Advisor,一定要保证你的 优化器是CBO模式. 1.首先需要创建一个用于调优的用户bamboo,并授予advisor给创建的用户 SQL> create user bamboo identified by bamboo; User created. SQL> grant connec

Oracle智能之SQL诊断：SQL Tuning Advisor推荐执行计划

编辑手记:在前一段,一篇智能数据库优化的论文引起广泛的关注,其实在 Oracle 数据库中,已经引入了大量自动化和智能化的方法去进行自动调节,包括在 SQL 层面的智能诊断分析和建议. 张大朋(Lunar)Oracle 资深技术专家 Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于 ORACLE Sales Consultant 部门,负责的产品主要是 Exadata,Golden Gate,Database 等. 本文的测试目的,

[20130626]11GR2 SQL Tuning Advisor.txt

[20130626]11GR2 SQL Tuning Advisor.txt 11GR2加入了sql tuning advisor,缺省是打开的,我发现一些dba建议安装11G后,直接关闭它,好像因为消耗资源. SQL> @verBANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11

通过分析SQL语句的执行计划优化SQL

如何干预执行计划 - - 使用hints提示 基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担.但有时它也聪明反被聪明误,选择了很差的执行计划,使某个语句的执行变得奇慢无比.此时就需要DBA进行人为的干预,告诉优化器使用我们指定的存取路径或连接类型生成执行计划,从而使语句高效的运行.例如,如果我们认为对于一个特定的语句,执行全表扫描要比执行索引扫描更有效,则我们就可以指示优化器使用全表扫描.在Oracle中,是通过为语句添加hints(提示)来实现干预优化器优

通过分析SQL语句的执行计划优化SQL(二)

优化|语句|执行 第5章 ORACLE的执行计划 背景知识:        为了更好的进行下面的内容我们必须了解一些概念性的术语: 共享sql语句    为了不重复解析相同的SQL语句(因为解析操作比较费资源,会导致性能下降),在第一次解析之后,ORACLE将SQL语句及解析后得到的执行计划存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个

Oracle SQL tuning 数据库优化步骤图文教程

  SQL Turning 是Quest公司出品的Quest Central软件中的一个工具.Quest Central是一款集成化.图形化.跨平台的数据库管理解决方案,可以同时管理 Oracle.DB2 和 SQL server 数据库. 一.SQL Tuning for SQL Server简介 SQL语句的优化对发挥数据库的最佳性能非常关键.然而不幸的是,应用优化通常由于时间和资源的因素而被忽略.SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能,提出智能