oracle-动态采样与扩展列统计信息

原创张洋华 2020-06-29

1607

动态采样这个名词可能都听惯不怪了，熟悉或者知道的可以把这三个问题给自己提一下：
动态采样是什么？
工作原理是什么？
怎么使用的呢？
拓展：扩展列统计信息是什么？应用场景是什么？

一、动态采样

1.1 动态采样是什么？

动态采样:可以将这个词分成两个词动态和采样；
动态 oracle对没有收集过统计信息的表，在执行SQL时候估算where条件的选择率从而确保SQL执行计划的正确性，可以说是实时的过程；
采样默认情况下，oarcle会对满足条件的表采样一定量数据块（采样数据块数量由动态采样的级别决定的），然后对这部分采样出来的数据块实际执行了目标SQL，CBO可以根据结果来准确的估算出整改where条件的选择率，选择正确的执行计划。

测试一：

TEST@rac1>create table test_dy as select * from dba_objects; --创建测试表

  COUNT(*)
----------
     86629

TEST@rac1>set lin 999 pagesize 999
TEST@rac1>set autot trace --打开autotrace
TEST@rac1>select count(*) from test_dy; 


Execution Plan
----------------------------------------------------------
Plan hash value: 4050699584

----------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |   338   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_DY | 81308 |   338   (1)| 00:00:05 |
----------------------------------------------------------------------

Note   
-----
   - dynamic sampling used for this statement (level=2)
   -这里有标识了是否使用了动态采样和动态采样的级别。

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1240  consistent gets
       1237  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
复制

dynamic sampling used for this statement (level=2)

1.2 怎么开启动态采样？

1）设置optimizer_dynamic_sampling参数

alter system set optimizer_dynamic_sampling=3;
复制

_optimizer_dyn_smp_blks默认采样数据块数量，默认值是32。

level 0：不启用动态采样。
level 1：当表（非分区表）没有收集过统计信息并且这个表要与另外的表进行关联（不能是单表访问），同时该表没有索引，表的数据块必须大于32个，满足这些条件的时候，Oracle会随机扫描表中32个数据块，然后评估返回的Rows。
level 2：对没有收集过统计信息的表启用动态采样，采样的块数为64个，如果表的块数小于64个，表有多少个块就会采样多少个块。
level 3：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是优化器不能准确地估算出返回的Rows，而是靠猜，比如 WHERE SUBSTR(owner,1,3)，这时会随机扫描 64 个数据块进行采样。
level 4：对没有收集过统计信息的表启用动态采样，采样的块数为 64 个。如果表已经收集过统计信息，但是表有两个或者两个以上过滤条件（AND/OR），这时会随机扫描64个数据块进行采样，相关列问题就必须启用至少level 4进行动态采样。level4 采样包含了level 3 的
采样数据。
level 5：收集满足level 4采样条件的数据，采样的块数为128 个。
level 6：收集满足level 4采样条件的数据，采样的块数为256 个。
level 7：收集满足level 4采样条件的数据，采样的块数为512 个。
level 8：收集满足level 4采样条件的数据，采样的块数为1024 个。
level 9：收集满足level 4采样条件的数据，采样的块数为4086 个。
level 10：收集满足 level 4 采样条件的数据，采样表中所有的数据块。
level 11： Oracle 自动判断如何采样，采样的块数由 Oracle 自动决定。

2）添加hint启用动态采样
使用/* dynamic_sampling( tablename, level ) */

TEST@rac1>select /*+ dynamic_sampling(3) */ * from test_dy where substr(owner,4,3)='LIC';

34017 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1626711708

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 33065 |  6684K|   338   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TEST_DY | 33065 |  6684K|   338   (1)| 00:00:05 |
-----------------------------------------------------------------------------

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

   1 - filter(SUBSTR("OWNER",4,3)='LIC')

Note
-----
   - dynamic sampling used for this statement (level=3)
   - 这里使用的level 3的动态采样

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       3543  consistent gets
       1237  physical reads
          0  redo size
    1688010  bytes sent via SQL*Net to client
      25456  bytes received via SQL*Net from client
       2269  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      34017  rows processed
复制

注：如果表收集过统计信息，优化器能够直接根据统计信息估算出比较准确的返回行，即使添加了hint，也不会启用动态采样。

1.3 什么时候使用动态采样呢？

1、OLAP或者数据仓库的环境下，SQL的执行消耗要大于SQL的解析，这时候让解析多小号一点资源来做动态采样分析，少量的资源消耗换来正确的最优的执行计划是非常划算的。动态采样level设置成4就能解决大部分问题，最多设置到6。
2、没有收集过统计信息的对象，涉及这些对象的sql可以使用动态采样。
3、大量的使用到临时表，通常来说，因为临时表数据是临时性的，临时表的数据是不被分析；为了SQL跟准确的执行，只能依赖动态采样对临时表的分析了。

二、扩展列统计信息

实验部分摘至罗炳生的《SQL优化核心思想》

当where条件中出现多个谓词过滤条件的时候，这些过滤条件是互相存在一定关系的，这个时候需要使用DBMS_STATS.CREATE_EXTENDED_STATS包收集扩展统计信息来使优化器估算出比较准确的行数。（通过实验来讲讲吧）

2.1、实验

创建一个测试t表。

 CREATE TABLE t AS
SELECT LEVEL AS id,
                LEVEL || 'a' AS a,
                         LEVEL || LEVEL || 'b' AS b
FROM dual CONNECT BY LEVEL < 100;
复制

插入重复数据，反复加重复数据

DECLARE
   str Varchar(10); 
BEGIN
   for i in 1 .. 15 loop
      insert into t select * from t;
      sys.dbms_output.put_line(str || i);
   end loop;
   commit;
END;
/
复制

在t表中能通过A列的值来确定B列的值，所以A和B列是较做相关列。

对t表收集统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 2,
cascade => TRUE);
END;
 /
复制

查看t表的统计信息

select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'T';
COLUMN_NAME                      NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM       NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
ID                                3244032          99           0 FREQUENCY                99
A                                 3244032          99           0 FREQUENCY                99
B                                 3244032          99           0 FREQUENCY                99
复制

创建索引

create index idx1 on t(a); --a列创建索引

create index idx2 on t(a,b); --a,b列创建复合索引
复制

SQL 1 执行计划如下

select * from t where a='1a' and b='11b';

32768 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2303463401

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |   331 |  4303 |    84   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |   331 |  4303 |    84   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX2 |   331 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("A"='1a' AND "B"='11b')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      11848  consistent gets
         85  physical reads
          0  redo size
     898436  bytes sent via SQL*Net to client
      24543  bytes received via SQL*Net from client
       2186  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      32768  rows processed
复制

优化器走的idx2复合索引，估算返回了331行数据，但是实际上返回了32768行数据（这按照实际返回行数走全表的效率高于走索引）；估算行数和实际返回的行数相差巨大，是由于优化器不知道A和B的关系，估算的公式=（A的选择率*B的选择率）*总行数。

select round(1/99/99*3244032) from dual;
                  round(1/99/99*3244032)
                   ---------------------
                                  331
复制

由于A列可以决定B列，其实上面SQL1返回结果等于SQL2.

select * from t where a='1a';

32768 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 32768 |   416K|  7014   (1)| 00:01:25 |
|*  1 |  TABLE ACCESS FULL| T    | 32768 |   416K|  7014   (1)| 00:01:25 |
--------------------------------------------------------------------------

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

   1 - filter("A"='1a')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      28008  consistent gets
          0  physical reads
          0  redo size
     564216  bytes sent via SQL*Net to client
      24543  bytes received via SQL*Net from client
       2186  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      32768  rows processed
复制

只有一个条件的时候，优化器正确的估算出返回的rows，从而选择正确的执行计划。

思考

当遇到这种数据量很大的相关列问题的时候，在不改SQL（删掉相关列条件）的情况，有两种解决方案：

将动态采样的level设置到4，这样优化器能采样到更多数据块，从而相关列的关心，估算正确的返回数据。
收集DBMS_STATS.CREATE_EXTENDED_STATS扩展列信息，让CBO更准确的知道相关列关系

2.2 收集扩展列统计信息

创建t表A，B列扩展统计列

TEST@rac1>SELECT DBMS_STATS.CREATE_EXTENDED_STATS('TEST', 'T', '(A, B)') FROM DUAL;


DBMS_STATS.CREATE_EXTENDED_STATS('TEST','T','(A,B)')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X
复制

收集扩展列统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
tabname => 'T',
estimate_percent => 100,
method_opt => 'for columns SYS_STUNA$6DVXJXTP05EH56DTIR0X size skewonly',
no_invalidate => FALSE,
degree => 2,
cascade => TRUE);
END;
 /
复制

查看t表的统计信息

COLUMN_NAME                      NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM       NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
ID                                3244032          99           0 FREQUENCY                99
A                                 3244032          99           0 FREQUENCY                99
B                                 3244032          99           0 FREQUENCY                99
SYS_STUNA$6DVXJXTP05EH56DTIR0X    3244032          99           0 FREQUENCY                99
复制

再次执行SQL 1 就cbo就能准确的估算返回行并选择正确的执行计划

TEST@rac1>select * from t where a='1a' and b='11b';

32768 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 32768 |   416K|  7016   (1)| 00:01:25 |
|*  1 |  TABLE ACCESS FULL| T    | 32768 |   416K|  7016   (1)| 00:01:25 |
--------------------------------------------------------------------------

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

   1 - filter("A"='1a' AND "B"='11b')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      28008  consistent gets
          0  physical reads
          0  redo size
     564216  bytes sent via SQL*Net to client
      24543  bytes received via SQL*Net from client
       2186  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      32768  rows processed
复制

注：扩展统计信息只能用于等值查询，不能用于非等值查询。

三、总结

其实将动态采样和扩展列统计信息放在一块整理是有原因的，主要是这两个功能都是为了解决CBO选择更准确更优的执行计划，不过是作用领域不同罢了。简单总结一下：
动态采样：

未收集过统计信息的对象
临时表对象（数据库不会对它收集统计信息）
OLAP和数据仓库可以大规模使用，OLTP慎用！

扩展列统计信息：

where谓词条件涉及多个相关列，导致cbo估算错返回行，走错执行计划
扩展统计信息只能用于等值查询，不能用于非等值查询

oracle

最后修改时间：2020-07-01 11:50:51

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者