动态采样这个名词可能都听惯不怪了，熟悉或者知道的可以把这三个问题给自己提一下：
动态采样是什么？
工作原理是什么？
怎么使用的呢？
拓展：扩展列统计信息是什么？应用场景是什么？
一、动态采样
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表扩展列统计信息
- 扩展统计列
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估算错返回行，走错执行计划
- 扩展统计信息只能用于等值查询，不能用于非等值查询
800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
