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

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

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

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

 

1、从Dynamic Sampling谈起

 

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

从SQL执行的角度看，Dynamic Sampling给Oracle用户带来两个方面好处：

ü  关联条件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等待事件进行跟踪处理。下面利用这种方法进行检查。

 

4、10053事件跟踪

 

我们在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';

会话已更改。

 

从跟踪文件中，我们找到了CBO和Dynamic 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的调节语句动作。但是还有一个疑惑，就是原有的采样level是2，如果发生调节也应该是“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。从而形成更高的采样比例。

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

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

 

5、结论

 

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