ITPub博客

首页 > 数据库 > Oracle > Oracle 11g中dynamic sampling自动调节(auto-adjusted)机制

Oracle 11g中dynamic sampling自动调节(auto-adjusted)机制

Oracle 作者:haiross 时间:2015-01-19 11:48:13 0 删除 编辑

 

Oracle而言,基于成本优化器CBO工作的基础是系统、对象统计量和CPU成本计算公式。而大多数情况下,收集统计量是一个异步单独的过程。无论是在9i,还是目前主流11g,发挥CBO作用都需要我们有一个统计量收集或者分析的过程。

9i时代,CBORBO是交替工作的。Oracle是不会“主动”的将对象统计量进行收集(那个时期也成为分析analyze)。系统调优人员也大都是解决统计量缺失、CBO优化器缺陷问题,目前能看到的很多hint也大都在9i时期编写系统。

到了10g之后,两个情况让CBO彻底取代RBO。一个是CBO成为Oracle默认的优化器使用类型,SQL语句默认情况下就使用CBO进行解析。另一个是自动统计量夜间收集作业的推出,Oracle可以自动找时间进行统计量收集。应该说,大部分的SQL语句从10g之后都是使用CBO进行执行计划生成。

但是,无论如何,都存在统计量不及时的情况。一个新创建的数据表或者发生大量变化的数据集合,如果没有手工的数据收集,CBO总是工作在统计量缺失或者陈旧统计量的情况下。

 

1、从Dynamic Sampling谈起

 

为了应对统计量缺失的情况,Oracle推出了Dynamic Sampling12c之后称为Dynamic Statistic)动态采样收集的技术策略。简单点说,如果数据表等对象没有统计量存在,Oracle有需要统计量生成执行计划,数据库会进行一次临时性的数据收集动作。根据不同的采用比例,Oracle实时的采集部分数据块,应用查询条件进行小规模试算。最后根据统计规则,将结果集合放大后,作为结果集合统计量和row source纳入到CBO体系里面。

SQL执行的角度看,Dynamic SamplingOracle用户带来两个方面好处:

ü  关联条件SQL语句精准估算。对于常见的统计量,如选择率、数据分布,都是以单列为中心进行计数的。如果SQL语句中对应的是两个相关联的where条件,那么单列统计量计算出来的结果集合往往是偏小。如果采用Dynamic Sampling,采用动作是实时进行计算的,可以消除关联where条件影响;

ü  CBO普遍应用。应该说,没有统计量,CBO是没有任何工作的可能的!Dynamic Sampling虽然不能给Oracle提供出最精确的统计量,但是起码可以让CBO运行提供基本条件;

从不利的方面看,Dynamic Sampling也带来一些问题。比如,和驻留在数据字典中的统计量不同,Dynamic Sampling并不会将收集采用值保存在数据字典里面,而是一次SQL就进行一次收集动作。采样比例如果高,就意味着parse前的动态收集动作消耗资源高。如果采样比例低,性能虽然可以好一些,但是存在着“低效”执行计划生成的纪律。

这其实是一个矛盾。如果数据表比较小,采样比例小一些,执行计划效率低一些其实也没有过多问题。但是如果数据表很大,SQL语句属于关键SQL,这样低效的执行计划其实就意味着风险。

Oracle中,是通过optimizer_dynamic_sampling设置采样比例。11gR2中,默认为2

 

SQL> show parameter dyn

NAME                                 TYPE        VALUE

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

optimizer_dynamic_sampling           integer     2

 

为了应对由于动态采样带来的问题,11g里面针对特殊的数据对象和SQL场景,Oracle CBO采取了自动调节采样率的优化策略。

下面我们进行相应的实验。

 

2、环境介绍

 

我们使用Oracle 11gR2版本进行测试,默认的采样控制参数是2

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

SQL> show parameter dyn

NAME                                 TYPE        VALUE

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

optimizer_dynamic_sampling           integer     2

 

创建一个大数据表,体积为2G

 

SQL> select tablespace_name, bytes/1024/1024/1024 from dba_segments where owner='SYS' and segment_name='T';

 

TABLESPACE_NAME                BYTES/1024/1024/1024

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

TESTTBL                                 2.099609375

 

Owner列上面创建索引IDX_T_OWNER

 

3、自动sampling adjusted

 

当前采用取值为2,由于数据表比较大,采用并行查询策略。

 

SQL> explain plan for select /*+ parallel(t, 2) */count(*) from t;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3126468333

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

| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    T

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

|   0 | SELECT STATEMENT       |          |     1 | 41414   (1)| 00:08:17 |

|   1 |  SORT AGGREGATE        |          |     1 |            |          |

|   2 |   PX COORDINATOR       |          |       |            |          |

|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,

|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,

|   5 |      PX BLOCK ITERATOR |          |    23M| 41414   (1)| 00:08:17 |  Q1,

|   6 |       TABLE ACCESS FULL| T        |    23M| 41414   (1)| 00:08:17 |  Q1,

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

Note

-----

   - dynamic sampling used for this statement (level=5)

 

17 rows selected

 

新创建的数据表,没有显示的进行数据收集动作。所以Oracle会去选择动态采样Dynamic Sampling策略。在使用parallel和动态采样的SQL语句中,我们使用explain plan生成了执行计划,但是在末尾的note中,发现了不确定的部分。

当前dynamic sampling设置参数为2,但是在执行计划上显示的是5。取值越高,意味着更高的采样比例,进而意味着更准确地执行计划生成。

从其他的场景,比如非并行开启或者小数据表查询,我们都没有看到这样的现象。说明:这个是针对Oracle特殊SQL场景下的一种优化措施。研究CBO行为最好的方式是使用10053等待事件进行跟踪处理。下面利用这种方法进行检查。

 

410053事件跟踪

 

我们在sqlplus中调用跟踪事件。跟踪文件名称如下。

 

SQL> select value from v$diag_info where name='Default Trace File';

VALUE

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

/u01/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_5602.trc

 

SQL> show parameter dyn

NAME                                 TYPE        VALUE

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

optimizer_dynamic_sampling           integer     2

 

启用跟踪过程。

 

SQL> alter session set events '10053 trace name context forever, level 2';

会话已更改。

 

SQL> select /*+ parallel(t, 2) */count(*) from t;

  COUNT(*)

----------

  19360512

 

SQL> alter session set events '10053 trace name context off';

会话已更改。

 

从跟踪文件中,我们找到了CBODynamic Sampling工作的片段内容。首先,我们发现了动态采样动作调整。

 

Registered qb: SEL$1 0x783a28 (PARSER)

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

QUERY BLOCK SIGNATURE

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

  signature (): qb_name=SEL$1 nbfros=1 flg=0

    fro(0): flg=4 objn=78697 hint_alias="T"@"SEL$1"

 

SPM: statement not found in SMB

Dynamic sampling level auto-adjusted from 5 to 5

 

**************************

Automatic degree of parallelism (ADOP)

**************************

Automatic degree of parallelism is disabled: Parameter.

 

在跟踪文件中出现了auto-adjusted的调节语句动作。但是还有一个疑惑,就是原有的采样level2,如果发生调节也应该是“from 2 to 5”。但是在语句里面,我们看到的是“from 5 to 5”。这个问题等后面介绍。

不存在统计量的信息列举。

 

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table:  T  Alias:  T  (NOT ANALYZED)

    #Rows: 22418465  #Blks:  274467  AvgRowLen:  100.00  ChainCnt:  0.00

Index Stats::

  Index: IDX_T_OWNER  Col#: 1    (NOT ANALYZED)

    LVLS: 1  #LB: 25  #DK: 100  LB/K: 1.00  DB/K: 1.00  CLUF: 800.00

Access path analysis for T

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for T[T]

 

启动采样SQL执行。

 

*** 2014-02-17 10:38:49.540

** Performing dynamic sampling initial checks. **

** Dynamic sampling initial checks returning TRUE (level = 5). –启用level=5的策略

** Dynamic sampling updated index stats.: IDX_T_OWNER, blocks=45795

** Dynamic sampling updated table stats.: blocks=274467

 

*** 2014-02-17 10:38:49.555

** Generated dynamic sampling query:

    query text :

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),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM "SYS"."T" SAMPLE BLOCK (0.022954 , 1) SEED (1) "T") SAMPLESUB

 

采样操作结果如下:

 

*** 2014-02-17 10:38:49.585

** Executed dynamic sampling query:

    level : 5

    sample pct. : 0.022954

    actual sample size : 5398

    filtered sample card. : 5398

    orig. card. : 22418465

    block cnt. table stat. : 274467

    block cnt. for sampling: 274467

    max. sample block cnt. : 64

    sample block cnt. : 63

    min. sel. est. : -1.00000000

** Using dynamic sampling card. : 23517030 –估算计算后的值

 

补全最后的采样统计量。

 

** Dynamic sampling updated table card.

  Table: T  Alias: T

    Card: Original: 23517029.619048  Rounded: 23517030  Computed: 23517029.62  Non Adjusted: 23517029.62

  Access Path: TableScan

    Cost:  74544.99  Resp: 41413.88  Degree: 0

      Cost_io: 74337.00  Cost_cpu: 3527554500

      Resp_io: 41298.33  Resp_cpu: 1959752500

  Best:: AccessPath: TableScan

         Cost: 41413.88  Degree: 2  Resp: 41413.88  Card: 23517029.62  Bytes: 0

 

最后生成的执行计划:

 

============

Plan Table

============

------------------------------------------+-----------------------------------+-------------------------+

| Id  | Operation               | Name    | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |

------------------------------------------+-----------------------------------+-------------------------+

| 0   | SELECT STATEMENT        |         |       |       |   40K |           |      |      |           |

| 1   |  SORT AGGREGATE         |         |     1 |       |       |           |      |      |           |

| 2   |   PX COORDINATOR        |         |       |       |       |           |      |      |           |

| 3   |    PX SEND QC (RANDOM)  | :TQ10000|     1 |       |       |           |:Q1000| P->S |QC (RANDOM)|

| 4   |     SORT AGGREGATE      |         |     1 |       |       |           |:Q1000| PCWP |           |

| 5   |      PX BLOCK ITERATOR  |         |   22M |       |   40K |  00:08:17 |:Q1000| PCWC |           |

| 6   |       TABLE ACCESS FULL | T       |   22M |       |   40K |  00:08:17 |:Q1000| PCWP |           |

------------------------------------------+-----------------------------------+-------------------------+

Predicate Information:

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

6 - access(:Z>=:Z AND :Z<=:Z)

 

Content of other_xml column

===========================

  db_version     : 11.2.0.3

  parse_schema   : SYS

  dynamic_sampling: 5

  plan_hash      : 3126468333

  plan_hash_2    : 1826541471

  Outline Data:

  /*+

    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      DB_VERSION('11.2.0.3')

      OPT_PARAM('optimizer_dynamic_sampling' 5)

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      FULL(@"SEL$1" "T"@"SEL$1")

    END_OUTLINE_DATA

  */

 

这个过程,确实证明了Oracle在这个SQL中采用了调节dynamic sample比例的过程,将原来的默认2变为5。从而形成更高的采样比例。

根据目前的资料:Oracle11g开始应用一种自动调节的动态收集机制。如果一个SQL对应数据表很大,而且应该用并行策略,同时有没有统计量。这个时候,Oracle会自己调节采样比例,到一个比较大的取值。

至于说trace文件中那个诡异的“from 5 to 5”,在MOS中有对应的记录,这个是一个未公布的bug,编号为:452863  ER: USE DYNAMIC SAMPLING FOR KEY CHOICES WHEN ROW ESTIMATES ARE DUBIOUS。但是,调节动作是存在的。

 

5、结论

 

通过上面的实验,我们可以了解Oracle11g之后对CBO动态采样机制的一种补充优化。应该说这样的策略是合理的。因为在特别的场景下,特别是并行,我们是比较关注的SQL才会选择应用的。Oracle选择这样的激发条件,也是有所依据的。


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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-05-24

  • 博文量
    16
  • 访问量
    34507