【Oracle 优化器】动态统计(Dynamic Statistics)

勤奋不是嘴上说说而已,而是实际的行动,在勤奋的苦度中持之以恒,永不退却。业精于勤,荒于嬉;行成于思,毁于随。在人生的仕途上,我们毫不迟疑地选择勤奋,她是几乎于世界上一切成就的催产婆。只要我们拥着勤奋去思考,拥着勤奋的手去耕耘,用抱勤奋的心去对待工作,浪迹红尘而坚韧不拔,那么,我们的生命就会绽放火花,让人生的时光更加的闪亮而精彩。

导读:本篇文章讲解 【Oracle 优化器】动态统计(Dynamic Statistics),希望对大家有帮助,欢迎收藏,转发!站点地址:www.bmabk.com,来源:原文

概述

为了解决因为统计信息缺失或者统计不够准确而引起的问题,从9iR2的版本开始Oracle推出了动态采样(Dynamic Sampling)功能,使SQL文在硬解析过程中动态地收集统计信息。
该功能在以后的版本上得到更进一步的增强,从11.2.0.4版本改称为动态统计(Dynamic Statistics 以后简称DS )。

关于动态统计,对于有一些ORACLE数据库从业经验的人员来说,也许并不陌生,因此在阅读本章内容时,我希望大家先回顾以下几个问题,如果某方面有些不足可以重点进行阅读。

关于动态统计(DS )的几个问题:

1.什么是动态统计(Dynamic Statistics)或者动态采样(Dynamic Sampling)?
2.为什么要使用动态统计?
3.动态统计都有哪些级别,各个级别都有什么区别?
4.如何确认采用了动态统计功能?
5.动态统计在各个版本上有什么区别?
6.如何禁用动态统计和12c的自动动态统计?
7.动态统计的利弊,常见问题?

下面章节将针对以上的问题进行解答和说明:

什么是动态统计(Dynamic Statistics)或者动态采样(Dynamic Sampling)?

动态采样是Oracle从9iR2的版本开始推出的一项优化技术,通过执行一些递归SQL(recursive SQL )来随机访问表块,收集一些采样信息,来预估谓词的选择率(selectivities)。

9i以后的版本中不断进行功能加强,在11.2.0.4版本改称为动态统计(Dynamic Statistics)。
在12c版本后进一步推出自动动态统计(Automatic Dynamic Statistics)。

为什么要使用动态统计?

如同前面叙述的,如果由于选择执行计划的Object统计信息缺失、过期或者不足时,CBO优化器选择的执行计划就有可能不是最优的甚至是最差的。

所以为了保证优化器在SQL硬解析时选择到最优的执行计划,根据数据库动态统计的设置级别,进行动态收集Object的统计信息。

动态统计都有哪些级别,各个级别都有什么区别?

数据库动态统计的级别主要通过参数OPTIMIZER_DYNAMIC_SAMPLING的值(同级别)来决定。

关于OPTIMIZER_DYNAMIC_SAMPLING的值(同级别)和动态统计启用的条件可以参考下表:

Teacherwhat

※其中样本大小的数据块数可以由隐含参数_optimizer_dyn_smp_blks来控制(默认值为32)。

如何确认采用了动态统计功能?

我们可以通过以下的方法来确认一个查询是否使用了动态统计功能:

1.利用DBMS_XPLAN.DISPLAY_CURSOR功能查看是否使用了DS
2.利用Optimizer Trace(10053 Trace)查看是否使用了DS
3.利用SQL Trace(10046 Trace)查看是否使用了DS

我们通过以下的例子,来看一下查看某个查询是否使用动态统计。
(在12.1.0.2版本上进行测试)

---首先我们准备查询用的数据
SQL> conn SH/SH
Connected.
---查看optimizer_dynamic_sampling的级别
SQL> SHOW PARAMETERS optimizer_dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2

---使统计信息缺失,以便采用动态统计
SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('sh');

PL/SQL procedure successfully completed.

---设定Optimizer Trace(10053 Trace)和SQL Trace(10046 Trace)
SQL> alter session set tracefile_identifier='sampling_10053_10046';

Session altered.

SQL> alter session set statistics_level=all;

Session altered.

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Session altered.

---执行SQL文
SQL> SELECT /* statis sampling1 */
  2         p.prod_name, COUNT(*)
  3    FROM sales s,
  4         products p
  5   WHERE s.time_id >= TO_DATE(' 1997-07-01 00:00:00',
  6      'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
  7     AND s.time_id < TO_DATE(' 1998-04-01 00:00:00',
  8      'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
  9     AND s.prod_id = p.prod_id
 10   GROUP BY
 11         p.prod_name;

PROD_NAME                                            COUNT(*)
-------------------------------------------------- ----------
...
60 rows selected.

---通过dbms_xplan.display_cursor查看执行计划。
SQL> SET PAGES 2000 LIN 300 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));
SQL_ID  62054vzrc3rcr, child number 0
-------------------------------------
SELECT /* statis sampling1 */        p.prod_name, COUNT(*)   FROM sales
s,        products p  WHERE s.time_id >= TO_DATE(' 1997-07-01
00:00:00',     'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
AND s.time_id < TO_DATE(' 1998-04-01 00:00:00',     'SYYYY-MM-DD
HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    AND s.prod_id = p.prod_id
GROUP BY        p.prod_name

Plan hash value: 1538978877

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                |      1 |        |     60 |00:00:00.64 |    2339 |       |       |          |
|   1 |  HASH GROUP BY                               |                |      1 |  44240 |     60 |00:00:00.64 |    2339 |   974K|   974K| 3016K (0)|
|*  2 |   HASH JOIN                                  |                |      1 |  44240 |  43687 |00:00:00.57 |    2339 |  1185K|  1185K| 1598K (0)|
|   3 |    TABLE ACCESS FULL                         | PRODUCTS       |      1 |     72 |     72 |00:00:00.01 |       4 |       |       |          |
|   4 |    PARTITION RANGE ITERATOR                  |                |      1 |  44240 |  43687 |00:00:00.33 |    2335 |       |       |          |
|   5 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |      2 |  44240 |  43687 |00:00:00.21 |    2335 |       |       |          |
|   6 |      BITMAP CONVERSION TO ROWIDS             |                |      1 |        |  43687 |00:00:00.06 |       4 |       |       |          |
|*  7 |       BITMAP INDEX RANGE SCAN                | SALES_TIME_BIX |      1 |        |     90 |00:00:00.01 |       4 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("S"."PROD_ID"="P"."PROD_ID")
   7 - access("S"."TIME_ID">=TO_DATE(' 1997-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<TO_DATE(' 1998-04-01 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2) ★
   - this is an adaptive plan

dbms_xplan.display_cursor

我们可以看到,通过dbms_xplan.display_cursor输出的执行计划有如下的Note内容:
采用了级别为2的动态统计。

Note
-----
   - dynamic statistics used: dynamic sampling (level=2) 

10053

然后我们再看一看Optimizer Trace(10053 Trace)中关于动态统计的部分输出,并有包含/* OPT_DYN_SAMP */ 提示的递归调用SQL文:
(详细记录了动态采样的过程)

*** 2016-05-18 20:48:36.534
** Performing dynamic sampling initial checks. **★
  Column (#3): TIME_ID(DATE)  Part#: 3  NO STATISTICS (using defaults)
    AvgLen: 9 NDV: 250 Nulls: 0 Density: 0.003998
  Column (#3): TIME_ID(DATE)  Part#: 4  NO STATISTICS (using defaults)
    AvgLen: 9 NDV: 250 Nulls: 0 Density: 0.003998
  Column (#3): TIME_ID(DATE)  NO STATISTICS (using defaults)
    AvgLen: 9 NDV: 250 Nulls: 0 Density: 0.004000
** Dynamic sampling initial checks returning TRUE (level = 2).★
** Dynamic sampling updated index stats.: SALES_CHANNEL_BIX, blocks=119
** Dynamic sampling updated index stats.: SALES_CUST_BIX, blocks=574
** Dynamic sampling updated index stats.: SALES_PROD_BIX, blocks=105
** Dynamic sampling updated index stats.: SALES_PROMO_BIX, blocks=87
** Dynamic sampling updated index stats.: SALES_TIME_BIX, blocks=127
** Dynamic sampling index access candidate : SALES_TIME_BIX
** Dynamic sampling updated table stats.: blocks=97

*** 2016-05-18 20:48:36.534
** Generated dynamic sampling query: ★
    query text : 
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
... 
*** 2016-05-18 20:48:36.695
** Executed dynamic sampling query:★
    level : 2
    sample pct. : 63.917526
    total partitions : 28
      partitions for sampling : 2
      partitioning pct. : 7.142857
    actual sample size : 28277
    filtered sample card. : 28277
    orig. card. : 8005
    block cnt. table stat. : 97
    block cnt. for sampling: 97
    max. sample block cnt. : 64
    sample block cnt. : 62
    ndv C3 : 57
        scaled : 57.00
    nulls C4 : 0
        scaled : 0.00
    min. sel. est. : 0.00250000
** Dynamic sampling col. stats.:
  Column (#1): PROD_ID(NUMBER)  Part#: 0
    AvgLen: 22 NDV: 57 Nulls: 0 Density: 0.017544
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
   Scaled NDVs using cardinality = 619358.

10046

我们可以通过查看SQL Trace(10046 Trace)中的一些以SELECT /* OPT_DYN_SAMP */… 开头的动态采样递归SQL来判断是否采用了动态统计功能。

例如:
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 
  NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 
  NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00"), 
  NVL(SUM(C2),:"SYS_B_01"), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL 
  THEN :"SYS_B_02" ELSE :"SYS_B_03" END),:"SYS_B_04") 
FROM
 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("S") FULL("S") 
  NO_PARALLEL_INDEX("S") */ :"SYS_B_05" AS C1, CASE WHEN "S"."TIME_ID">=
  TO_DATE(:"SYS_B_06", :"SYS_B_07") AND "S"."TIME_ID"<TO_DATE(:"SYS_B_08", 
  :"SYS_B_09") THEN :"SYS_B_10" ELSE :"SYS_B_11" END AS C2, "S"."PROD_ID" AS 
  C3 FROM "SH"."SALES" SAMPLE BLOCK (:"SYS_B_12" , :"SYS_B_13") SEED 
  (:"SYS_B_14") "S" WHERE "S"."TIME_ID">=TO_DATE(:"SYS_B_15", :"SYS_B_16") 
  AND "S"."TIME_ID"<TO_DATE(:"SYS_B_17", :"SYS_B_18")) SAMPLESUB

版权声明:本文为博主原创文章,转载必须注明出处,本人保留一切相关权力!http://blog.csdn.net/lukeunique

动态统计在各个版本上有什么区别?

下面我们再介绍一下在各个版本上,动态统计有什么特点和那些比较大的演进:

9iR2

在9iR2的版本上推出,称为动态采样(Dynamic Sampling)。

DB 11.2.0.1

在11.2.0.1 的版本上,推出了自动调整(AUTO-ADJUST)功能。
即,11.2.0.1以后的版本,如果动态采样采用的级别是默认级别2,Oracle就有可能根据查询语句的特点自动调整(AUTO-ADJUST)动态采样的级别;调整后的级别范围为4~8.
当然根据需要,你也可以下面的语句关闭这项功能。

SQL> alter session set "_fix_control" = '7452863:off'; 

DB 11.2.0.4

在11.2.0.4 的版本上,推出了一个新的级别11,并且正式改名为动态统计(Dynamic Statistics)
即,如果把级别设为11,将由Oracle优化器自行决定动态采样范围和采样大小。

12c

在12c的版本上,推出了自动动态统计(Automatic Dynamic Statistics)功能。
即,当满足下面任何的一个条件时,Oracle优化器自行决定动态采样范围和采样大小。

  ・级别为默认级别2时 或者 级别设为11
  ・指定SQL HINT(dynamic_sampling)启用动态统计
  ・并行查询
  ・执行过的查询语句并且其执行履历还是可用的(如在内存中,AWR中等)。

当然还有由于如自适应执行计划,统计反馈,SQL计划指令等自适应查询优化(Adaptive Query Optimization)触发的一些情况。

※可以通过以下设定,使并行查询时的12c自动动态统计无效。

    SQL> alter system set "_fix_control"= '12914055:off'

如何禁用动态统计和12c的自动动态统计?

你可以通过如下几种方法禁用动态统计:

OPTIMIZER_DYNAMIC_SAMPLING

你可以通过设置初始化参数OPTIMIZER_DYNAMIC_SAMPLING为0可以彻底地使动态统计功能无效。

例:

SQL> conn /as sysdba
Connected.
SQL>---系统级别    
SQL> show parameter OPTIMIZER_DYNAMIC_SAMPLING

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
SQL> alter system set optimizer_dynamic_sampling=0;

System altered.

SQL> show parameter OPTIMIZER_DYNAMIC_SAMPLING

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     0

--会话级别
SQL> alter session set optimizer_dynamic_sampling=0;

Session altered.

SQL> show parameter OPTIMIZER_DYNAMIC_SAMPLING

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     0

HINT

可以通过dynamic_sampling HINT,针对某些特定SQL使动态统计无效。

select /*+ dynamic_sampling(0) */ ...

也可以通过dynamic_sampling HINT,针对某些特定语句中的特定表,使动态统计无效。

SELECT /*+ dynamic_sampling(<tab/alias name> 0) */

关于12c版本上的自动动态统计

在12c版本上,自动动态统计(ADS)属于自动查询优化(Adaptive query optimization)的一部分,所以通过参数OPTIMIZER_ADAPTIVE_FEATURES也可以对自动动态统计(ADS)进行控制。
但是需要明确的是,参数OPTIMIZER_ADAPTIVE_FEATURES仅仅能控制12c ADS的使用,并不能控制默认的动态统计功能。即:

  当OPTIMIZER_ADAPTIVE_FEATURES = TRUE 并且OPTIMIZER_DYNAMIC_SAMPLING = 2 时,自动动态统计(ADS)有效。

  当OPTIMIZER_ADAPTIVE_FEATURES = FALSE 并且OPTIMIZER_DYNAMIC_SAMPLING = 2 时,自动动态统计(ADS)无效,但是默认的动态统计(Level 2)依然会有效。

  另外,当OPTIMIZER_DYNAMIC_SAMPLING = 11时,无论OPTIMIZER_ADAPTIVE_FEATURES的值是TRUE 和FALSE ,12c ADS都会有效。

在12c版本上的自动动态统计有效的情况下,动态收集的统计信息还会保存在内存的结果缓存(Result Cache)中,以便供其他相关的查询使用。
我们可以通过 参数_optimizer_ads_use_result_cache来控制是否保存统计结果到结果缓存(Result Cache)中。

动态统计的利弊?

我们知道任何新功能都是一把双刃剑,有优点也有缺点,对于动态统计也一样。
动态统计最大的优点是,在优化器选择执行计划时,对统计信息缺失或者统计不够准确的对象,能够动态地收集统计信息,从而获得相对好的执行计划。
并且在12c中优化器还能够自行决定统计收集级别和在内存中保存统计结果,供其他操作共享。
而其也有着缺点,就是使硬解析时候的解析时间可能变得更长,而且如果大量的动态收集操作发生时可能影响到数据库全体性能。

版权声明:本文为博主原创文章,转载必须注明出处,本人保留一切相关权力!http://blog.csdn.net/lukeunique

欢迎关注技术文章首发平台微信订阅号:TeacherWhat
这里写图片描述

参考

Database SQL Tuning Guide
https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL341

Dynamic Statistics

https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer

Dynamic sampling and its impact on the Optimizer

https://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#GUID-DEE2AF8B-5F4B-4FE7-9F0E-7D188921EBCC

About Dynamic Statistics Levels

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由半码博客整理,本文链接:https://www.bmabk.com/index.php/post/141626.html

(0)

相关推荐

发表回复

登录后才能评论
半码博客——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!