^_^小麦苗^_^

小麦苗博客,实用经典,简单易懂,接地气!---技术应用型博客,主打实用性,只讲实用的,不讲没用的,多做实验,多思考!

  • 博客访问: 6064398
  • 博文数量: 953
  • 用 户 组: 普通用户
  • 注册时间: 2012-09-23 17:46
  • 认证徽章:
个人简介

QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(953)

文章存档

2017年(399)

2016年(313)

2015年(168)

2014年(73)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
Oracle动态采样分析 2017-05-18 16:00:58

分类: Oracle

Oracle动态采样分析



动态采样概念

 

动态采样(Dynamic Sampling)是在ORACLE 9i Release 2中开始引入的一个技术,引入它的目的是为了应对数据库对象没有分析(统计信息缺失)的情况下,优化器生成更好的执行计划。简单的说,在数据库段(表、索引、分区)对象没有分析的情况下,为了使CBO优化器得到足够多的信息以保证优化器做出正确执行计划而发明的一种技术。它会分析一定数量段对象上的数据块获取CBO需要的统计信息。动态采样技术仅仅是统计信息的一种补充,它不能完全替代统计信息分析。

Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.
 
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.
 

动态采样在Oracle 11g之前称为 Dynamic Sampling, ORACLE 12c之后改名为Dynamic Statistic.

 

动态采样介绍

如果要理解动态采样,最好从鲜活的例子开始,向来理论都是枯燥乏味的。创建一个test表,总共有50319行数据。如下所示

SQL> create table test
  2 as 
  3 select owner, object_type
  4 from dba_objects;
 
Table created.
 
SQL> select count(1) from test;
 
  COUNT(1)
----------
     50319

我们使用dynamic_sampling(test 0)提示(hints)来禁用动态采样(稍后动态采样级别中介绍),从下面的执行计划可以看出,在表对象没有做分析情况下,如果禁用了动态采样,CBO优化器唯一可以使用的信息为该表存储在数据字典的一些信息,比如多少个extent,多少个block等,这些信息往往不够。此时优化器估计表test的行数为11027(如下所示), 跟实际的表记录行数50319还是有蛮大的偏差。在复杂环境下,就很有可能导致CBO优化器做出错误的执行计划。

SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11027 |   301K|    31   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST | 11027 |   301K|    31   (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set autotrace off;

clip_image001

如果启用动态采样(默认情况下,动态采样级别为2),优化器根据动态采样得到一些数据信息猜测、估计表TEST的记录行数为48054,已经接近实际记录行数50319了。比不做动态采样分析要好很多了。当然你不能指望动态采样获取完全准确的信息,因为它只是采样了一些数据块。

SQL> set autotrace traceonly explain;
SQL> select * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 48054 |  1313K|    32   (4)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST | 48054 |  1313K|    32   (4)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
 - dynamic sampling used for this statement
 
SQL> set autotrace off;

clip_image002

如果我们将动态采样的级别提高为3,如下所示,发现优化器根据动态采样得到的信息比默认(默认情况下,动态采样级别为2)情况获得的信息更准确。优化器估计表TEST的行数为51463,比48054又接近实际情况一步了。

 
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 3) */ * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 51463 |   703K|    32   (4)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST | 51463 |   703K|    32   (4)| 00:00:01 |
--------------------------------------------------------------------------
SQL> set autotrace off;

clip_image003

在Tom的这篇文章中提到,在没有动态采样的情况下,如果删除了该表数据,CBO优化器估算的结果集和没有删除之前是一样的。这是因为当一个表的数据被删除后,这个表所分配的extent和block是不会自动回收的(高水位线不变),所以CBO如果没有采样数据块做分析,只是从数据字典中获取extend等信息,就会误认为任然还有那么多数据。

SQL> delete from test;
 
50319 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> set autotrace traceonly explain;
SQL> select /*+ dynamic_sampling(test 0) */ * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 11027 |   301K|    31   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST | 11027 |   301K|    31   (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |    31   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST |     1 |    28 |    31   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
 - dynamic sampling used for this statement
 
SQL> 

clip_image004

 

什么时候使用动态采样?

如下所示,我们使用包dbms_stats.gather_table_stats收集表Test的统计信息过后,你会发现“dynamic sampling used for this statement”不见了,其实也就是说优化器发现有表TEST有分析过,它就不会使用动态采样技术。其实开篇的时候已经叙说过“应对数据库对象没有分析(统计信息缺失)的情况下,才会用到动态采样技术“

SQL> set autotrace trace exp;
SQL> select * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |    31   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST |     1 |    28 |    31   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
 - dynamic sampling used for this statement
 
SQL> exec dbms_stats.gather_table_stats(user, 'test');
 
PL/SQL procedure successfully completed.
 
SQL> select * from test;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |    31   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| TEST |     1 |    28 |    31   (0)| 00:00:01 |
--------------------------------------------------------------------------

clip_image005

第二种情况:当表TEST即使被分析过,如果查询脚本里面包含临时表,就会使用动态采样技术。因为临时表是不会被分析,它是没有统计信息的。如下所示

SQL> drop table test;
SQL> create table test
  2 as 
  3 select owner, object_type
  4 from dba_objects;
 
Table created.
 
SQL> exec dbms_stats.gather_table_stats(user, 'test');
 
PL/SQL procedure successfully completed.
 
 
SQL> create global temporary table tmp
  2  (object_type varchar2(19));
 
Table created.
 
SQL> insert into tmp
  2 select distinct object_type from dba_objects;
 
41 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> set autotrace traceonly explain;
 
SQL> select t.owner, l.object_type
  2 from test t inner join tmp l on t.object_type =l.object_type;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 19574435
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    25 |    35   (6)| 00:00:01 |
|*  1 |  HASH JOIN |      |     1 |    25 |    35   (6)| 00:00:01 |
|   2 | TABLE ACCESS FULL| TMP  |     1 |    11 |     2   (0)| 00:00:01 |
|   3 | TABLE ACCESS FULL| TEST | 49422 |   675K|    32   (4)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T"."OBJECT_TYPE"="L"."OBJECT_TYPE")
 
Note
-----
 - dynamic sampling used for this statement
 
SQL> 

clip_image006

动态采样还有一个独特能力,可以对不同列之间的相关性做统计。表统计信息都是相对独立的。当查询涉及列之间的相关性时,统计信息就显得有些不足了,请看Tom的例子

创建一个特殊的表t,然后对字段flag1、flag2创建索引t_idx,然后分析收集统计信息

SQL> create table t
  2 as select decode(mod(rownum,2),0,'N', 'Y') flag1,
  3               decode(mod(rownum,2),0,'Y', 'N') flag2, a.*
  4 from all_objects a;
 
Table created.
 
SQL> create index t_idx on t(flag1, flag2);
 
Index created.
 
SQL> begin
  2   dbms_stats.gather_table_stats(user, 'T',      
  3        method_opt =>'for all indexed columns size 254');
  4 end;
  5  /
 
PL/SQL procedure successfully completed.

关于表t的行数情况如下所示,大家先不要纠结为什么查询获取NUM_ROWS数据

SQL> select num_rows, num_rows/2, num_rows/2/2
  2 from user_tables
  3 where table_name='T';
 
  NUM_ROWS NUM_ROWS/2 NUM_ROWS/2/2
---------- ---------- ------------
     49875    24937.5     12468.75

首先看看对flag1过滤条件的SQL语句,CBO优化器猜测、估计的行数24757, 相当接近24937.5记录数了。

SQL> set autotrace traceonly explain;
SQL> select * from t where flag1='N';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24757 |  2345K|   161   (2)| 00:00:02 |
|*  1 | TABLE ACCESS FULL| T    | 24757 |  2345K|   161   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG1"='N')

首先看看对flag2过滤条件的SQL语句,CBO优化器猜测、估计的行数25118, 相当接近24937.5记录数了。

SQL> select * from t where flag2='N';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 25118 |  2379K|   161   (2)| 00:00:02 |
|*  1 | TABLE ACCESS FULL| T    | 25118 |  2379K|   161   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG2"='N')

如果条件flag1 = 'N' and flag2 = 'N',我们根据逻辑推理判断这样的记录肯定是不存在的,这也是苦心构造这个特例的初衷。下面看看CBO优化器怎么探测、预测的

SQL> select * from t where flag1 = 'N' and flag2 = 'N';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12468 |  1181K|   160   (2)| 00:00:02 |
|*  1 | TABLE ACCESS FULL| T    | 12468 |  1181K|   160   (2)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG1"='N' AND "FLAG2"='N')

CBO估计的记录数为12468,和实际情况相差非常远。其实是CBO优化器这样估算来的:

flag1=‘N' 的记录数占总数的1/2

flag2= 'N' 的记录数占总数的1/2

根据NUM_ROWS/2/2 =12468.这样显然是不合理的。下面我们通过提升动态采样级别,来看看动态采样是否能避免CBO的错误

SQL> select /*+ dynamic_sampling(t 3) */ * from t where flag1 = 'N' and flag2 = 'N';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   388 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T     |     4 |   388 |     2   (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN          | T_IDX |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("FLAG1"='N' AND "FLAG2"='N')
 
Note
-----
 - dynamic sampling used for this statement
 
SQL> 

clip_image007

 

动态采样级别

ORACLE为动态采样划分了11个级别,详情请见ORACLE 11g官方文档http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94760

Table 13-10 Dynamic Statistics Levels

Level

When the Optimizer Uses Dynamic Statistics

Sample Size (Blocks)

0

Do not use dynamic statistics

不做动态采样分析

n/a

1

Use dynamic statistics for all tables that do not have statistics, but only if the following criteria are met:

· There is at least 1 nonpartitioned table in the query that does not have statistics.

· This table has no indexes.

· This table has more blocks than the number of blocks that would be used for dynamic statistics of this table.

Oracle 对没有分析的表进行动态采样,但需要同时满足以下3个条件。

(1) SQL中至少有一个未分析的表(非分区表)

(2) 未分析的表没有索引

(3) 未分析的表占用的数据块要大于动态采样的数据块(32个)

32

2

Use dynamic statistics if at least one table in the statement has no statistics. This is the default setting.

对所有的未分析表做分析,动态采样的数据块是默认数据块数为64

64

3

Use dynamic statistics if any of the following conditions is true:

·

· The statement meets level 2 criteria.

·

· The statement has one or more expressions used in the WHERE clause predicates, for example, WHERE SUBSTR(cust_last_name,1,3).

采样的表包含满足Level 2定义的所有表,同时包括,那些谓词有可能潜在地需要动态采样的表,这些动态采样的数据块为默认数据块,对没有分析的表,动态采样的默认块为默认数据块数量。

64

4

Use dynamic statistics if any of the following conditions is true:

·

· The statement meets level 3 criteria.

·

· The statement uses complex predicates (an OR or AND operator between multiple predicates on the same table).

采样的表包含满足Level 3定义的表,同时还包括一些表,他们包含一个单表的谓词会引用另外的2个列或者更多的列;采样的块数是动态采样默认数据块数;对没有分析的表,动态采样的数据块为默认数据块的1倍。

64

5

Use dynamic statistics if the statement meets level 4 criteria.

采样的表包含满足Level 4定义的表,同时分别使用动态采样默认数据块的2倍的数量来做动态分析。

128

6

Use dynamic statistics if the statement meets level 4 criteria.

采样的表包含满足Level 4定义的表,同时分别使用动态采样默认数据块的4倍的数量来做动态分析。

256

7

Use dynamic statistics if the statement meets level 4 criteria.

采样的表包含满足Level 4定义的表,同时分别使用动态采样默认数据块的8倍的数量来做动态分析。

512

8

Use dynamic statistics if the statement meets level 4 criteria.

采样的表包含满足Level 4定义的表,同时分别使用动态采样默认数据块的32 倍的数量来做动态分析。

1024

9

Use dynamic statistics if the statement meets level 4 criteria.

采样的表包含满足Level 4定义的表,同时分别使用动态采样默认数据块的128倍的数量来做动态分析。

4086

10

Use dynamic statistics if the statement meets level 4 criteria.

采样的表包含满足Level 4定义的表,同时分别使用动态采样对所有数据块做动态分析。

All blocks

11

Use dynamic statistics automatically whenever the optimizer deems it necessary.

当优化器探测到需要的采样时,对段段对象自动采样

Automatically determined

采样级别越高,采样的数据块越多,得到的分析数据就越接近于真实,但同时伴随着资源消耗的开销也增加了。这时一个需要权衡考虑的东西。ORACLE 10 g & 11g的默认采样级别都为2,如下所示,一般使用在会话中使用dynamic_sampling提示来修改动态采样级别。

SQL> show parameter optimizer_dynamic_sampling
 
NAME                               TYPE VALUE
------------------------------ ----------- -----------
optimizer_dynamic_sampling integer 2
SQL> 

另外一个方式就是通过提示hints里修改动态采样的级别。这个非常灵活、有用。

 

动态采样注意事项

凡事有利必有弊,动态采样也不是神器。它采样的数据块越多,系统开销就越大,这样会增加SQL硬解析的时间,如果是数据库仓库(DW、OLAP)环境,SQL执行时间相当长,硬解析时间只占整个SQL执行时间的一小部分,那么可以适当的提高动态采样级别,这样是有利于优化器获取更加正确的信息。一般设置为3或4比较合适。

但是在并发比较严重的OLTP系统中,每秒中有成千上万的SQL语句执行,它要求SQL语句短小、执行时间短,所以在OLTP系统中应该减低动态采样级别或不用动态采样。可以参考下面Tom的原文


When should I use dynamic sampling?” is a tricky question. As with any other feature, there are times to use it and times to avoid it. So far I’ve concentrated on the “goodness” of dynamic sampling, and based on that, it seems that you should set the level to 3 or 4 and just let the optimizer always use dynamic sampling to validate its guesses.

That makes sense in an environment in which you spend most of your time executing SQL and very little of your overall time hard-parsing the SQL. That is, the SQL you are executing runs for a long time and the parse time is a small portion of the overall execution time, such as in a data warehousing environment. There, dynamic sampling at levels above the default makes complete sense. You are willing to give the optimizer a little more time during a hard parse (when sampling takes place) to arrive at the optimal plan for these complex queries.

That leaves the other classic type of environment: the online transaction processing (OLTP) system. Here, in general, you are executing queries thousands of times per second and spend very little time executing a given query—the queries are typically small and fast. Increasing the parse time in an OLTP system might well cause you to spend more time parsing than executing SQL. You do not want to increase the parse times here, so higher levels of dynamic sampling would not be advisable


参考资料

http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html

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

[让Oracle跑得更快]---谭怀远




http://blog.csdn.net/tianlesoftware/article/details/5845028

之前在说Oracle Optimizer中的CBO时讲到,当表没有做分析的时候,Oracle 会使用动态采样来收集统计信息。 获取准确的段对象(表,表分区,索引等)的分析数据,是CBO存在的基石,CBO的机制就是收集尽可能多的对象信息和系统信息,通过对这些信息进行计算,分析,评估,最终得出一个成本最低的执行计划。 所以对于CBO,数据段的分析就非常重要。

 

Oracle Optimizer CBO RBO

http://blog.csdn.net/tianlesoftware/archive/2010/08/19/5824886.aspx

 

一.         先演示一个示例,来理解分析的作用

 

1.1创建表

SQL> create table t as select object_id,object_name from dba_objects where 1=2;

表已创建。

SQL> create index index_t on t(object_id);

索引已创建。

SQL> insert into t select object_id,object_name from dba_objects;

已创建72926行。

SQL> commit;

提交完成。

 

1.2查看分的分析及执行计划

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED

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

 

SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED

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

         0           0             0 25-8 -10

 

从查询结果看出,表的行数,行长,占用的数据块数及最后的分析时间都是空。 索引的相关信息也没有,说明这个表和说因都没有被分析,如果此时有一条SQL 对表做查询,CBO 由于无法获取这些信息,很可能生成错误的执行计划,如:

 

SQL> set linesize 200

SQL> set autot trace exp;

SQL> select /*+dynamic_sampling(t 0) */ * from t where object_id>30;

执行计划

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

Plan hash value: 80339723

 

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

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

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

|   0 | SELECT STATEMENT            |         |     4 |   316 |    0   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T       |     4 |   316 |     0   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | INDEX_T |     1 |       |     0  (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID">30)

SQL>

 

Oracle 10g以后,如果一个表没有做分析,数据库将自动对它做动态采样分析,所以这里采用hint的方式将动态采样的级别设置为0,即不使用动态采样。

 

         从这个执行计划,看书CBO 估计出表中满足条件的记录为4条,索引使用了索引。 我们对表做一下分析,用结果比较一下。

 

1.3 分析表及查看分析之后的执行计划

分析可以通过两中方式:

一种是analyze 命令,如:

analyze table tablename compute statistics for all indexes;

         还有一种就是通过DBMS_STATS包来分析,从9i 开始,Oracle 推荐使用DBMS_STATS包对表进行分析操作,因为DBMS_STATS 提供了更多的功能,以及灵活的操作方式。

        

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from user_indexes where table_name='T';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZED

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

         1         263         72926 25-8 -10

SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where table_name='T';

  NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED

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

     72926          29        345 25-8 -10

 

从上面的结果,可以看出DBMS_STATS.gather_table_stats已经对表和索引都做了分析。 现在我们在来看一下执行计划。

 

SQL> set autot trace exp;

SQL> select * from t where object_id>30;

执行计划

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 72899 |  2064K|    96   (2)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL| T    | 72899 |  2064K|    96   (2)| 00:00:02 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("OBJECT_ID">30)

 

从这个计划,我们看出CBO 估算出的结果是72899 条记录,与实际的72926很近。 此时选择全表扫描更优。 通过这个例子,我们也看出了分析对执行计划的重要性。

 

 




 

 

四.         动态采样

 

4.1 什么是动态采样

         动态采样(Dynamic Sampling)技术的最初提出是在Oracle 9i R2,在段(表,索引,分区)没有分析的情况下,为了使CBO 优化器得到足够的信息以保证做出正确的执行计划而发明的一种技术,可以把它看做分析手段的一种补充。

         当段对象没有统计信息时(即没有做分析),动态采样技术可以通过直接从需要分析的对象上收集数据块(采样)来获得CBO需要的统计信息。

 

一个简单的例子:

 

创建表:

SQL> create table t

  2  as

  3  select owner,object_type from all_objects;

表已创建。

 

查看表的记录数:

SQL> select count(*) from t;

COUNT(*)

----------

72236  -- 记录数

 

这里创建了一张普通表,没有做分析,我们在hint中用0级来限制动态采样,此时CBO 唯一可以使用的信息就是表存储在数据字典中的一些信息,如有多少个extent,有多少个block,但是这些信息是不够的。

 

SQL> set autot traceonly explain

SQL> select /*+dynamic_sampling(t 0) */ * from t;

 

执行计划

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

Plan hash value: 1601196873

 

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

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

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

|   0 | SELECT STATEMENT  |      | 15928 |   435K|    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    | 15928 |   435K|    55   (0)| 00:00:01 |
--------------------------------------------------------------------------


在没有做动态分析的情况下,CBO 估计的记录数是15928条,与真实的72236 相差甚远。

 

我们用动态分析来查看一下:

SQL> select * from t;

执行计划

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

Plan hash value: 1601196873

 

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

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

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

|   0 | SELECT STATEMENT  |      | 80232 |  2193K|    56   (2)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    | 80232 |  2193K|    56   (2)| 00:00:01 |

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

 

Note

-----

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

 

Oracle 10g中默认对没有分析的段做动态采样,上面的查询结果显示使用了Level 2级的动态采样,CBO 估计的结果是80232 72236 很接近了。

 

注意一点:

         在没有动态采样的情况下,对于没有分析过的段,CBO也可能错误地将结果判断的程度扩大话。 如:

SQL> delete from t;

已删除72236行。

SQL> commit;

提交完成。

SQL> select /*+dynamic_sampling(t 0) */ * from t;

执行计划

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      | 15928 |   435K|    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    | 15928 |   435K|    55   (0)| 00:00:01 |

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

 

SQL> select * from t;

执行计划

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

Plan hash value: 1601196873

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    28 |    55   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| T    |     1 |    28 |    55   (0)| 00:00:01 |

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

Note

-----

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

 

如果细心一点,可能看出2个执行计划的差别。 在没有采用动态分析的情况下,CBO t表估计的还是15928行记录,但是用动态分析就显示1条记录。 而表中的数据在查询之前已经删除掉了。  出现这种情况的原因是因为高水位。 虽然表的数据已经删除,但是表分配的extent block 没有被回收,所以在这种情况下CBO 依然认为有那么多的数据在那。

        

         通过这一点,我们可以看出,此时CBO能够使用的信息非常有限,也就是这个表有几个extent,有几个block。 但动态采样之后,Oracle 立即发现,原来数据块中都是空的。

 

关于Oracle 高水位,参考我的blogOracle 高水位(HWM)

http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4707900.aspx

 

动态采样有两方面的作用:

(1)       CBO 依赖的是充分的统计分析信息,但是并不是每个用户都会非常认真,及时地去对每个表做分析。 为了保证执行计划都尽可能地正确,Oracle 需要使用动态采样技术来帮助CBO 获取尽可能多的信息。

(2)       全局临时表。 通常来讲,临时表的数据是不做分析的,因为它存放的数据是临时性的,可能很快就释放了,但是当一个查询关联到这样的临时表时,CBO要想获得临时表上的统计信息分析数据,就只能依赖于动态采样了。

 

动态采样除了可以在段对象没有分析时,给CBO提供分析数据之外,还有一个独特的能力,它可以对不同列之间的相关性做统计。

 

相对的,表分析的信息是独立的。 如:

(1)       表的行数,平均行长。

(2)       表的每个列的最大值,最小值,重复率,也可能包含直方图。

(3)       索引的聚合因子,索引叶的块数目,索引的高度等。

 

尽管看到动态采样的优点,但是它的缺点也是显而易见,否则Oracle 一定会一直使用动态采样来取代数据分析:

(1)       采样的数据块有限,对于海量数据的表,结果难免有偏差。

(2)       采样会消耗系统资源,特别是OLTP数据库,尤其不推荐使用动态采样。

 

 

4.2 动态采样的级别

         Oracle 为动态采样划分了11个级别,在Oracle 的官网上详细的介绍。

                   13.5.7.4 Dynamic Sampling Levels

             http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/stats.htm#PFGRF94760

 

The sampling levels are as follows if the dynamic sampling level used is from a cursor hint or from theOPTIMIZER_DYNAMIC_SAMPLING initialization parameter:

Level 0: Do not use dynamic sampling.

Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for a predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is twice the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

 

The sampling levels are as follows if the dynamic sampling level for a table is set using theDYNAMIC_SAMPLING optimizer hint:

Level 0: Do not use dynamic sampling.

Level 1: The number of blocks sampled is the default number of dynamic sampling blocks (32).

Levels 2, 3, 4, 5, 6, 7, 8, and 9: The number of blocks sampled is 2, 4, 8, 16, 32, 64, 128, or 256 times the default number of dynamic sampling blocks respectively.

Level 10: Read all blocks in the table.

4.2.1 Level 0

         不做动态分析

 

4.2.2 Level 1

         Oracle 对没有分析的表进行动态采样,但需要同时满足以下4个条件。

(1)       SQL中至少有一个未分析的表

(2)       未分析的表出现在关联查询或者子查询中

(3)       未分析的表没有索引

(4)       未分析的表占用的数据块要大于动态采样默认的数据块(32个)

 

4.2.3 Level 2

         对所有的未分析表做分析,动态采样的数据块是默认数据块的2倍。

 

4.2.4 Level 3

         采样的表包含满足Level 2定义的所有表,同时包括,那些谓词有可能潜在地需要动态采样的表,这些动态采样的数据块为默认数据块,对没有分析的表,动态采样的默认块为默认数据块的2倍。

 

4.2.5 Level 4

         采样的表包含满足Level 3定义的表,同时还包括一些表,他们包含一个单表的谓词会引用另外的2个列或者更多的列;采样的块数是动态采样默认数据块数;对没有分析的表,动态采样的数据块为默认数据块的2倍。

 

4.2.6 Level 56789

         采样的表包含满足Level 4定义的表,同时分别使用动态采样默认数据块的24832128 倍的数量来做动态分析。

 

4.2.7 Level 10

         采样的表包含满足Level 9定义的所有表,同时对表的所有数据进行动态采样。

 

 

采样的数据块越多,得到的分析数据就越接近与真实,但同时伴随着资源消耗的也越大。

 

 

4.3 什么时候使用动态采样

         动态采样也需要额外的消耗数据库资源,所以,如果 SQL 被反复执行,变量被绑定,硬分析很少,在这样一个环境中,是不宜使用动态采样的,就像OLTP系统。 动态采样发生在硬分析时,如果很少有硬分析发生,动态采样的意义就不大。

 

         而在OLAP或者数据仓库环境下,SQL执行消耗的资源要远远大于SQL解析,那么让解析在消耗多一点资源做一些动态采样分析,从而做出一个最优的执行计划是非常值得的。 实际上在这样的环境中,硬分析消耗的资源几乎是可以忽略的。

 

         所以,一般在OLAP 或者数据仓库环境中,将动态采样的level 设置为3或者比较好。 相反,在OLTP系统下,不应该使用动态采样。







 动态采样的增强特性

Different Level for DynamicStatistics (Dynamic Sampling) used than the Level Specified (文档 ID 1102413.1)

High Waits for 'cursor: pin S waiton X' due to Dynamic Sampling Against Parallel Queries (文档 ID 2006145.1)

Bug 17760686 - Bad Cardinalityestimation with dynamic sampling (文档 ID 17760686.8)


其中,文档1102413.1有如下论述:


Changes

The change in dynamic statistics is a result of anenhancement introduced in 11.2.
 --11.2
之后,Oracle对动态采样进行了增强。

In previous versions the default value lead to certain situationswhere the result from using dynamic statistics resulted in bad plans, forexample, when the tables referenced in the query are
very large.

With this enhancement the optimizer itself changes the dynamic value undercertain conditions such as with  large tables and parallelism. Thelevel is automatically adjusted based on the size of the tables in the query.This is done only for queries that will run in parallel which is typical forData Warehouses and large databases. 
--
增强的功能是:在某些条件下,比如大表,开启并行和复杂谓词条件,优化器会自动启动动态采样,并且自行定义动态采样的级别。
The automatic setting of the Dynamic Statistic level is designed such thatoverhead from using Dynamic Statistics is kept to a very minimum yet it willimprove its effectiveness.

While the fix for this unpublished enhancement request 
bug7452863  ER: USE DYNAMIC SAMPLING FOR KEY CHOICES WHEN ROWESTIMATES ARE DUBIOUS,  allows the auto-adjustment of the dynamicstatistic level for some queries, the tracing in 10053 may not correctlyreflect the adjustment made, and you may see a message of the sort:

--此特性是Oracle未公开的增强特性,相关FIX号是7452863


很显然,从我们的执行计划中可以很清晰的看到SQL启用了并行功能,而在11.2之后oracle提供的增强特性中,对于并行/大表的复杂条件,即使表上存在统计信息,它也会开启动态采样的功能,试图来更精准的评估返回结果集的记录数;然而,这里很不幸,遇上了动态采样本身的缺陷,导致动态采样后评估的行数反而大大偏离了实际情况,导致SQL语句执行时间超长;

对于该增强特性,MOS也提供了相应的解决方案:


Solution

1. Use workaround of turning off the following “_fix_control”. To do this,  login assysdba and run the following:

alter system set “_fix_control”=’7452863:OFF';

--使用_fix_control参数关闭此特性

This turns off the automatic decisions about parallelism anddynamic sampling level.

2. Turn off parallelism from the table and indexes or set it to 1:

ALTER TABLE... NOPARALLEL;
ALTER TABLE ... PARALLEL 1;

ALTER INDEX... NOPARALLEL; 
ALTER INDEX ... PARALLEL 1;

--或者不启动并行机制



High Waits for 'cursor: pin S wait on X' due to Dynamic Sampling Against Parallel Queries (文档 ID 2006145.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Information in this document applies to any platform.

SYMPTOMS

  • The top wait event from AWR report shows 'cursor:pin s wait on X' as the top event:
     Event                       Waits  TotalWaitTime(sec)  WaitAvg(ms)  %DBtime 
     cursor: pin S wait on X       346                 18K        52050     64.0 
     library cache lock             64              8978.4       140287     31.9 
     
  • Problem queries are run in parallel
  • Stack from the process shows following functions:
    ksedsts<-ksdxfstk<-ksdxcb<-sspuser<-__sighandler<-__select<-skgpwwait<-kgxWait
    <-kgxSharedExamine<-kxsGetRuntimeLock<-kkscsCheckCursor<-kkscsSearchChildList<-kksfbc
    <-kkspsc0<-kksParseCursor<-opiosq0<-kpoal8




  • This issue may also occur when running Real Application Testing and replaying data. The AWR Compare Period report after the replay may show higher waits for 'cursor:pin s wait on X' compared to the original in the top wait events section

CAUSE

This is due to an unpublished bug that is fixed in 12.2 or later and also included in the 12.1.0.2.0 patchset. 

From Oracle Database 11g Release 2 onwards, the optimizer will automatically decide if dynamic sampling will be useful and what dynamic sampling level will be used for SQL statements executed in parallel. This decision is based on size of the tables in the statement and the complexity of the predicates. Executing the dynamic sampling query for the corresponding parallel query causes multiple slaves to wait to get an exclusive lock, with corresponding performance degradation. If the OPTIMIZER_DYNAMIC_SAMPLING parameter is explicitly set to a non-default value, then that specified value will be honored.

SOLUTION


1. Use workaround of turning off the following “_fix_control”. To do this,  login as sysdba and run the following:

alter system set “_fix_control”=’7452863:OFF';

This turns off the automatic decisions about parallelism and dynamic sampling level.

2. Turn off parallelism from the table and indexes or set it to 1:

ALTER TABLE ... NOPARALLEL;
ALTER TABLE ... PARALLEL 1;

ALTER INDEX ... NOPARALLEL; 
ALTER INDEX ... PARALLEL 1;




Different Level for Dynamic Statistics (Dynamic Sampling) used than the Level Specified (文档 ID 1102413.1)

In this Document

Symptoms
Changes
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
When performing an explain on a query it can be seen that the level used for Dynamic Statistics is higher than the level specified
for the parameter OPTIMIZER_DYNAMIC_SAMPLING

NOTE: Prior to 11.2.0.4, Dynamic Statistics was referred to as Dynamic Sampling

SYMPTOMS

Even though dynamic statistics is set through the parameter OPTIMIZER_DYNAMIC_SAMPLING or  a DYNAMIC_SAMPLING  hint , as shown below, the level shown for the plan may be higher.

SQL>  select /*+ dynamic_sampling (my_table 2) */ * from my_table;

Execution Plan
----------------------------------------------------------
Plan hash value: 3006137970

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |  9408K|  1704M|  4000   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |  9408K|  1704M|  4000   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |  9408K|  1704M|  4000   (2)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| MY_TABLE |  9408K|  1704M|  4000   (2)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

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



In 10053 trace this can be identified by the following statement in the trace file:

Dynamic sampling level auto-adjusted from 2 to 5

 

CHANGES

The change in dynamic statistics is a result of an enhancement introduced in 11.2.
 

In previous versions the default value lead to certain situations where the result from using dynamic statistics resulted in bad plans, for example, when the tables referenced in the query are
very large.
 
With this enhancement the optimizer itself changes the dynamic value under certain conditions such as with  large tables and parallelism. The level is automatically adjusted based on the size of the tables in the query. This is done only for queries that will run in parallel which is typical for Data Warehouses and large databases. 

The automatic setting of the Dynamic Statistic level is designed such that overhead from using Dynamic Statistics is kept to a very minimum yet it will improve its effectiveness.

While the fix for this unpublished enhancement request bug 7452863  ER: USE DYNAMIC SAMPLING FOR KEY CHOICES WHEN ROW ESTIMATES ARE DUBIOUS,  allows the auto-adjustment of the dynamic statistic level for some queries, the tracing in 10053 may not correctly reflect the adjustment made, and you may see a message of the sort:

Dynamic sampling level auto-adjusted from 7 to 7

 This is documented in unpublished bug 10250194 CONFUSING DYNAMIC SAMPLING AUTO-ADJUST TRACE

CAUSE

This is a result of unpublished Enhancement Request filed under unpublished bug 7452863.

SOLUTION

This is expected behaviour

REFERENCES




Bug 17760686 - Bad Cardinality estimation with dynamic sampling (文档 ID 17760686.8)

Bug 17760686  Bad Cardinality estimation with dynamic sampling

 This note gives a brief overview of bug 17760686. 
 The content was last updated on: 16-MAY-2017
 Click here for details of each of the sections below.

Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.2
Versions confirmed as being affected
Platforms affected Generic (all / most platforms affected)

Fixed:

The fix for 17760686 is first included in

Interim patches may be available for earlier versions - click here to check.

Symptoms:

Related To:

Description

A dynamic sampling query may return a little or zero cardinality estimate for a query involving a partitioned table stored on an ASSM tablespace. Workaround Raise the level of dynamic sampling.
 


Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.


References

Bug:17760686 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article




About Me

...............................................................................................................................

● 本文整理自网络,http://www.cnblogs.com/kerrycode/p/3854971.html,http://blog.csdn.net/tianlesoftware/article/details/5845028

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。


DBA笔试面试讲解
欢迎与我联系

阅读(936) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册