动态采样这个名词可能都听惯不怪了,熟悉或者知道的可以把这三个问题给自己提一下:
动态采样是什么?
工作原理是什么?
怎么使用的呢?
拓展:扩展列统计信息是什么?应用场景是什么?
一、动态采样
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估算错返回行,走错执行计划
- 扩展统计信息只能用于等值查询,不能用于非等值查询