12.2统计信息之扩展统计信息

从11g开始引入了对列收集扩展统计信息,扩展统计信息包含了两种额外类型的统计信息:组合列和表达式统计信息。

组合列

对于真实数据来说,一个表中多个字段直接的数据通常具有关联性。比如在CUSTOMERS表中,CUST_STATE_PROVINCE字段会受到COUNTRY_ID字段值的影响,比如湖北只会出现在中国下面,如果只有基本的统计信息,优化器是没有办法知道这些关系的,如果在一个语句中这几个字段条件同时存在的话,优化器就会估算错误。这时如果有收集扩展统计信息的话,优化器就能知道它们之间的关系。

对组合列收集信息后,优化器能更好的计算出基数。可以通过函数DBMS_STATS.CREATE_EXTENDED_STATS去实现多个字段的组合收集。当组合字段的统计信息被收集以后,当这张表收集统计信息时,oracle会自动维护这个组合字段的统计信息,跟普通字段一样。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
ALTER SYSTEM FLUSH SHARED_POOL;

conn xb/xb;

drop table t1 purge;

create table t1 as
select 1 col1,2 col2
from dual
connect by level>=5000
union all
select 2 col1,1 col2
from dual
connect by level>=5000;

select dbms_stats.create_extended_stats(user,'t1','(COL1,COL2)') name from dual;

NAME
--------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH

exec dbms_stats.gather_table_stats(user,'t1');

建完组合列和重新收集统计信息后,会看到一个额外由系统生成名称的列

1
2
3
4
5
6
7
8
9
10
col column_name for a60
select column_name,num_distinct,num_nulls,histogram
from user_tab_col_statistics
where table_name='T1';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------------------------------------------------------ ------------ ---------- ---------------------------------------------
COL1 2 0 NONE
COL2 2 0 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH 2 0 NONE >>>>====新生成的字段名

如果要查用户有哪些其他的扩展统计信息,可以查询视图USER_STAT_EXTENSIONS

1
2
3
4
5
6
7
8
9
10
col table_name for a30
col extension_name for a60
col extension for a60
select table_name,extension_name,extension
from USER_STAT_EXTENSIONS
where creator='USER';

TABLE_NAME EXTENSION_NAME EXTENSION
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
T1 SYS_STUFLHATC5RBD6JHJZWT$X2AAH ("COL1","COL2")

自动组合列检测

尽管组合列统计信息非常有用,能很方便的生成好的执行计划,但是一般来说不容易知道在一个特定的负载下该收集哪些组合列的信息。

自动组合列检测决定了在一个特定的负载状态下,表的哪些组合列的统计信息应该被收集。注意此功能不对函数字段生成扩展信息,只对组合列适用。自动组合列检测由以下三个步骤完成:

种子列使用

为了决定适当的组合列,oracle必须了解具有代表性的负载情况。负载情况可以通过SQL Tuning Set查到或者通过负载监控工具。通过过程DBMS_STATS.SEED_COL_USAGE来显示负载,并告诉oracle这个负载要观察多久。下面这个例子表示对当前系统监控5分钟。

1
2
3
4
begin 
dbms_stats.seed_col_usage(null,null,300);
end;
/

这个监控过程会收集储存在sys.col_usage$当中的信息并储存到sys.col_group_usage$去。包含在这个监控窗口范围内所有执行或解析的sql,当这个监控窗口结束,可以通过一个新的函数DBMS_STATS.REPORT_COL_USAGE去查看到所有表的特定列的使用信息。这个函数会生成一个报告,列出了表所有谓词条件里过滤、关联、group by等操作涉及到的列。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
xb@ORA12C> select count(1) from t1 where col1=1 and col2=2;

COUNT(1)
----------
5000

xb@ORA12C> select dbms_stats.report_col_usage(user,'T1') from dual;

DBMS_STATS.REPORT_COL_USAGE(USER,'T1')
-----------------------------------------------------------------------
LEGEND:
.......

EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................

###############################################################################

COLUMN USAGE REPORT FOR XB.T1
.............................

1. COL1 : EQ >>>>====
2. COL2 : EQ >>>>====
3. SYS_STUFLHATC5RBD6JHJZWT$X2AAH : EQ >>>>====
4. (COL1, COL2) : FILTER >>>>====
###############################################################################

创建组合列

对每张表调用函数DBMS_STATS.CREATE_EXTENDED_STATS,会自动根据之前监控窗口中得到的有用信息创建必要的组合列。当扩展统计信息创建以后,每当表统计信息被收集时,组合列的统计信息也会自动维护。

1
2
3
4
5
6
7
8
9
10
11
xb@ORA12C> select dbms_stats.create_extended_stats(user,'T1') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T1')
-------------------------------------------------------------------------------------
###############################################################################

EXTENSIONS FOR XB.T1
....................

1. (COL1, COL2) : SYS_STUFLHATC5RBD6JHJZWT$X2AAH exists >>>>====我这里提示已存在
###############################################################################

也可以手动指定要组合的列

1
xb@ORA12C> select dbms_stats.create_extended_stats(user,'T1','(COL1,COL2)') from dual;

重新收集统计信息

最后一步就是重新收集表的统计信息,这样新创建的组合列就会拥有统计信息。

1
exec dbms_stats.gather_table_stats(null,'T1')

SQL计划指示和组合列

sql计划指示不仅仅指示用来优化sql执行计划,可以参考12c Adaptive Statistics,同样可以用来决定组合列是否可以有效解决基数估算不准的问题。如果一个sql计划指示创建了,同时优化器认为基数估算不准的问题可以被这个组合列解决,12cR1中oracle会在下次表分析的时候自动创建组合列。

但是到了12cR2的时候这个特性有了变化,自动创建组合列的功能默认是被关闭的,可以通过包DBMS_STATS包来开启或关闭。

1
2
3
exec dbms_stats.set_global_prefs ('AUTO_STAT_EXTENSIONS', 'OFF');  >>>>====表示不自动创建

exec dbms_stats.set_global_prefs ('AUTO_STAT_EXTENSIONS', 'ON'); >>>>====表示会根据SQL计划指示自动创建

如果你的版本是12.1的话,想关闭这个特性可以通过打补丁解决21171382

表达式统计信息

可以对表达式或函数收集扩展统计信息,去帮助优化器去估算那些谓词条件中包含表达式或者函数的情况。比如条件中含有upper(name)=,那么这时候收集upper(name)的扩展统计信息就会非常有帮助。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop table t1 purge;

create table t1 (name varchar2(10));

insert into t1 values('xb');
insert into t1 values('XB');
insert into t1 values('Xb');
commit;

select dbms_stats.create_extended_stats(NULL,'T1','(UPPER(NAME))') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'T1','(UPPER(NAME))')
------------------------------------------------------------------------
SYS_STUMMY#LUDFVUOI56L724JL4ZO

跟组合列一样,定义表达式统计信息以后需要重新收集统计信息。收集完毕后,可以通过USER_TAB_COL_STATISTICS视图查询,同样详细信息也可以通过USER_STAT_EXTENSIONS视图查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
col column_name for a60
select column_name,num_distinct,num_nulls,histogram
from user_tab_col_statistics
where table_name='T1';

COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM
------------------------------------------------------------ ------------ ---------- ---------------------------------------------
NAME 3 0 NONE
SYS_STUMMY#LUDFVUOI56L724JL4ZO 1 0 NONE >>>>====可以看到经过函数以后的估算值是1


col table_name for a30
col extension_name for a60
col extension for a60
select table_name,extension_name,extension
from USER_STAT_EXTENSIONS
where creator='USER';

TABLE_NAME EXTENSION_NAME EXTENSION
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
T1 SYS_STUMMY#LUDFVUOI56L724JL4ZO (UPPER("NAME"))

扩展统计信息的限制

扩展统计信息只适用于那些谓词条件为等于或者IN-LIST。如果列上有直方图信息但是组合列没有,则扩展统计信息不被使用。

12.2统计信息之直方图

介绍

很早之前的版本中,如何执行一个sql语句是由RBO(Rule Based Optimizer)决定的,根据一些设定好的规则来生成执行计划。

后来的版本中CBO(Cost Based Optimizer)被引入进来。CBO检查sql所有可能的执行计划然后选择其中cost最低的一个,cost其实就是反应了执行计划估算的资源消耗。cost越低,这个执行计划也就被认为效率越高。为了能让CBO计算出更准确的cost值,就必须要有sql所有的访问对象的相关信息,包括表、索引等。

这些必要的信息统称为统计信息,理解和管理统计信息对于获得一个好的执行计划是非常重要的。

什么是统计信息

统计信息是用来描述数据库和它其中的对象的数据,这些信息能被优化器使用用于对每个sql生成最好的执行计划。统计信息储存在数据字典当中,能直接通过字典视图来访问,例如USER_TAB_STATISTICS

大部分的统计信息需要定期收集和更新,用来确保当前的信息能真实反映实际储存在数据库中的数据情况。举个例子:部分交易表非常活跃,经常有大量数据的操作,那么这些表就需要经常更新统计信息。而另外一些历史归档表由于历史数据并没有被经常使用,只是时而被查询,那么这些表就不需要定期更新。所以是否需要收集统计信息完全是根据需要来的。

表和列的统计信息

表的统计信息包含有多少行数据,使用了多少个数据块,平均每一行数据使用了多少个byte等等。优化器使用这些信息并且结合其他的一些相关信息,计算出不同执行计划所需的cost,并且估算出各个操作所返回的记录数。比如访问某张表的cost则是用表的数据块数结合参数DB_FILE_MULTIBLOCK_READ_COUNT计算得出。你可以直接通过视图USER_TAB_STATISTICS来查询。

列的统计信息则包括列的唯一值数量(NDV),和列的最大值与最小值,可以通过USER_TAB_COL_STATISTICS视图查询。优化器通过这些信息结合表的相关信息来估算出sql每一步操作会返回的记录数。如果一张表有100行,查询的列上有10个唯一值,那么优化器估算的返回值也为100/10=10。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
drop table t purge;

create table t as
select mod(level,10) id from dual
connect by level>=100;

sys@ORA12C> @grp id t
count id in table t...

ID COUNT(*)
---------- ----------
0 10
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10

sys@ORA12C> select count(1) from t where id=1;


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T | 10 | 130 | 2 (0)| 00:00:01 | >>>>==== ROWs估算为10
---------------------------------------------------------------------------

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

2 - filter("ID"=1)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
6 recursive calls
5 db block gets
19 consistent gets
0 physical reads
0 redo size
542 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

额外列统计信息

表和列的统计信息给了优化器很多信息,但是其无法提供一个机制来告诉优化器表或列当中数据的性质。比如这些统计信息无法提供列的数据是否分布均匀,或者多个列的数据是否有关联关系。这种类型的信息只能通过扩展统计信息来获得。其中包括直方图,组合列和表达式统计信息。如果没有这些,那么优化器会默认数据是分布均匀的,然后所有列之间的数据并无关联关系。

直方图

直方图给优化器提供了列数据的分布情况,根据之前的例子,优化器是默认用总行数/唯一值来估算返回记录,如果列的数据分布不均匀,那么得到的结果就区别很大。为了准确反映一个不均匀的数据分布,就需要对列收集直方图。

oracle会自动决定列是否要收集直方图,取决于列的使用情况(SYS.COL_USAGE$),和数据倾斜的状况。比如当一个唯一列总是以等式出现在谓词条件中时,oracle不会对其自动创建直方图。

目前总共有4种直方图:frequency, top-frequency, height-balanced 和 hybrid(top-frequency和hybrid是12c新引入),oracle自动选择合适的类型。一般取决于列上的唯一值数量,可以通过user_tab_col_statistics视图的histogram字段来查询每一列的直方图类型。

frequency Histograms

当列的唯一值小于最大的桶的数量阀值时,默认创建frequency直方图。桶的最大值默认是254,但是可以通过DBMS_STATS来修改成最高至2048(从12c开始)

将之前的数据做下处理,然后收集直方图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
delete from t where id=1 and rownum >=9;
delete from t where id=2 and rownum >=8;
delete from t where id=3 and rownum >=7;
delete from t where id=4 and rownum >=6;
delete from t where id=5 and rownum >=5;
delete from t where id=6 and rownum >=4;
delete from t where id=7 and rownum >=3;
delete from t where id=8 and rownum >=2;
delete from t where id=9 and rownum >=1;
commit;

sys@ORA12C> @grp id t
count id in table t...

ID COUNT(*)
---------- ----------
0 10
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9

exec dbms_stats.gather_table_stats(USER,'T',method_opt=>'for all columns size auto');

现在来看一下直方图的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select endpoint_value,endpoint_number,endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency
from user_tab_histograms
where table_name='T'
and column_name='ID'
order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ----------
0 10 10
1 11 1
2 13 2
3 16 3
4 20 4
5 25 5
6 31 6
7 38 7
8 46 8
9 55 9

FREQUENCY表示每一个桶值,也就是这个值有多少条记录数。oracle频度直方图将桶称为”endpoint values”,上面的结果就是表示有0-9总共10个桶,ENDPOINT_NUMBER这代表着累计频度,比如endpoint values为1的累计频度就是前后的FREQUENCY的累加值,10+1=11。

注意到ENDPOINT_VALUE为number型,所以对于非number类型的字段收集直方图以后,存储在ENDPOINT_VALUE字段的值也会转码成number型。

1
2
3
4
5
6
7
8
9
10
11
sys@ORA12C> @desc user_tab_histograms
Name Null? Type
------------------------------- -------- ----------------------------
1 TABLE_NAME VARCHAR2(128)
2 COLUMN_NAME VARCHAR2(4000)
3 ENDPOINT_NUMBER NUMBER
4 ENDPOINT_VALUE NUMBER >>>>====这里是number类型
5 ENDPOINT_ACTUAL_VALUE VARCHAR2(4000)
6 ENDPOINT_ACTUAL_VALUE_RAW RAW(2000)
7 ENDPOINT_REPEAT_COUNT NUMBER
8 SCOPE VARCHAR2(7)

当直方图创建完毕以后,优化器就能估算的更加准确。比如id=1时,优化器立刻能知道记录只有1条,就会走索引之类。

高度平衡直方图

与频度直方图相反的是,当列的唯一值大于最大的桶的数量阀值时,默认创建高度平衡直方图。桶的最大值默认是254,但是可以通过DBMS_STATS来修改成最高至2048(从12c开始)。

频度直方图在面对字段有大量唯一值时就比较乏力,因为它每个桶中只存放了一个值,所以随着直方图数量的不断增加,系统在存储、使用和维护这些直方图时就会需要大量的资源。12c开始,在多数情况下oracle采用了新的混合直方图来取代高度平衡直方图,稍后会介绍到混合直方图。

假设一个表含有字段COL1,值从1到1000,下图展示出将数据切分成数个范围。值从1到100的记录数有1000行,值从201到300直接的值突然增高到3000

如果将同样的数据换成高度平衡直方图的话,通过平衡每个范围的大小,可以将每个范围的值调整成近似相等。换句话说,每个垂直条都有近似的高度。比如值1-200有2000行,下一个范围201-267也保持在2000行左右,因为201-300范围的值比较多。

Top-Frequency直方图

传统而言当列的唯一值大于最大的桶的数量阀值时,就会创建高度平衡直方图或者混合直方图。然而有些情况下列中大多数数据都是重复的,其余的相对少量数据却含有大量的唯一值,也就是说数据分布极不均匀。在这种情况下,更适合对表的大多数记录创建频度直方图,忽略对统计信息不重要的一组记录(基数小但是唯一值多)。当选择频度直方图时,数据库必须决定最大的桶数阀值是否足够去准确的计算基数,即使行中的唯一值数量已经超过了这个阀值。它计算列中99.6%的行中有多少个不同的值(99.6%是默认值,但是这个值会根据直方图的数量进行调整)。如果有足够的直方图桶可以用来容纳前N个不同的值,那么将为这些活跃的值创建频率直方图。

在收集统计信息时,只有参数ESTIMATE_PERCENT被设成AUTO_SAMPLE_SIZE(默认值)时,才会创建Top-Frequency直方图,因为必须查看列中的所有值,才能判断是否满足必要的条件(行中的99.6%有254或者更少的唯一值)。

构建一张表包含10000条数据,其中9000条随机1-9,大致上每个值含有1000条数据,其余的值都是唯一值。故总的唯一值数为1009,远超254的阀值。参数METHOD_OPT限制每个桶不超过10

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT CASE
WHEN level >= 9000 THEN TRUNC(DBMS_RANDOM.value(1,10))
ELSE level
END AS id
FROM dual
CONNECT BY level >= 10000;

EXEC DBMS_STATS.gather_table_stats(USER, 't1', method_opt => 'FOR COLUMNS ID SIZE 10');

通过USER_TAB_COLUMNS视图可以查到直方图类型

1
2
3
4
5
6
7
8
9
SELECT column_id,
column_name,
histogram
FROM user_tab_columns
WHERE table_name = 'T1';

COLUMN_ID COLUMN_NAME HISTOGRAM
---------- -------------------- ---------------------------------------------
1 ID TOP-FREQUENCY

下面这个查询展示了Top-Frequency直方图中每个值以及其结束点值和频度,注意到重复度高的值展示的是一个正常的频度直方图,而重复度低的值则被分组成一个低频度的组。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select endpoint_value,endpoint_number, endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency
from user_tab_histograms
where table_name='T1'
and column_name='ID'
order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ----------
1 995 995
2 1981 986
3 2971 990
4 3950 979
5 4960 1010
6 5950 990
7 6932 982
8 7952 1020
9 9000 1048
10000 9001 1
混合直方图

混合直方图类似频度和高度平衡直方图的一个结合,大多数情况下,12c采用混合直方图来取代传统的高度直方图。跟高度平衡直方图不一样的是,混合直方图中结束点值不能跨越桶,除了桶中的最高值外,混合直方图还储存最高值的次数,从而准确的了解其活跃程度,同时也能了解其他结束点值的活跃程度。那么混合直方图如何表示一个活跃的值呢?记录每个结束点值的频率(记录在新字段endpoint_repeat_count),从而提供每个结束点值的活跃程度。

下面这张表有10000行,有5000行随机1-99,其余5000行值唯一。

1
2
3
4
5
6
7
8
9
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT CASE
WHEN MOD(level,2) = 0 THEN TRUNC(DBMS_RANDOM.value(1,100))
ELSE level
END AS id
FROM dual
CONNECT BY level >= 10000;

这里为了触发在正常收集统计信息的时候收集直方图,先用重复度的高的条件查询一遍做硬解析。

1
2
3
4
5
6
7
8
9
sys@ORA12C> select count(1) from t1 where id=1;

COUNT(1)
----------
48

sys@ORA12C> EXEC DBMS_STATS.gather_table_stats(USER, 't1');

PL/SQL procedure successfully completed.

通过USER_TAB_COLUMNS视图可以查到直方图类型

1
2
3
4
5
6
7
8
9
SELECT column_id,
column_name,
histogram
FROM user_tab_columns
WHERE table_name = 'T1';

COLUMN_ID COLUMN_NAME HISTOGRAM
---------- -------------------- ---------------------------------------------
1 ID HYBRID

下面的结果可以看到混合直方图同时具有频度和高度平衡直方图的特性,桶可以包含多个值,终点值存储累计频度。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
select histogram,num_buckets
from user_tab_col_statistics
where table_name='T1'
and column_name='ID';

HISTOGRAM NUM_BUCKETS
--------------------------------------------- -----------
HYBRID 254

select endpoint_value,endpoint_number,endpoint_repeat_count, endpoint_number-lag(endpoint_number,1,0) over(order by endpoint_number) as frequency
from user_tab_histograms
where table_name='T1'
and column_name='ID'
order by 2;

ENDPOINT_VALUE ENDPOINT_NUMBER ENDPOINT_REPEAT_COUNT FREQUENCY
-------------- --------------- --------------------- ----------
1 48 48 48
2 92 44 44
3 146 54 54
4 195 49 49
5 245 50 50
6 301 56 56
7 351 50 50
8 397 46 46
9 440 43 43
10 478 38 38
11 517 39 39
12 565 48 48
13 602 37 37
14 649 47 47
15 704 55 55
16 744 40 40
17 799 55 55
18 856 57 57
19 912 56 56
20 962 50 50
21 1008 46 46
22 1068 60 60
23 1127 59 59
24 1185 58 58
25 1231 46 46
26 1282 51 51
27 1333 51 51
28 1385 52 52
29 1434 49 49
30 1481 47 47
31 1530 49 49
32 1579 49 49
33 1630 51 51
34 1681 51 51
35 1736 55 55
36 1799 63 63
37 1852 53 53
38 1908 56 56
39 1966 58 58
40 2009 43 43
41 2061 52 52
42 2109 48 48
43 2158 49 49
44 2213 55 55
45 2253 40 40
46 2307 54 54
47 2362 55 55
48 2415 53 53
49 2460 45 45
50 2516 56 56
51 2557 41 41
52 2618 61 61
53 2665 47 47
54 2733 68 68
55 2787 54 54
56 2838 51 51
57 2895 57 57
58 2951 56 56
59 3023 72 72
60 3068 45 45
61 3123 55 55
62 3179 56 56
63 3235 56 56
64 3272 37 37
65 3342 70 70
66 3392 50 50
67 3439 47 47
68 3498 59 59
69 3557 59 59
70 3596 39 39
71 3645 49 49
72 3695 50 50
73 3745 50 50
74 3782 37 37
75 3842 60 60
76 3891 49 49
77 3948 57 57
78 3989 41 41
79 4048 59 59
80 4095 47 47
81 4153 58 58
82 4198 45 45
83 4250 52 52
84 4308 58 58
85 4346 38 38
86 4395 49 49
87 4448 53 53
88 4505 57 57
89 4554 49 49
90 4604 50 50
91 4644 40 40
92 4689 45 45
93 4743 54 54
94 4797 54 54
95 4851 54 54
96 4894 43 43
97 4953 59 59
98 5003 50 50
99 5050 47 47
163 5082 1 32
227 5114 1 32
291 5146 1 32
355 5178 1 32
419 5210 1 32
483 5242 1 32
547 5274 1 32
611 5306 1 32
677 5339 1 33
741 5371 1 32
805 5403 1 32
869 5435 1 32
933 5467 1 32
997 5499 1 32
1061 5531 1 32
1125 5563 1 32
1189 5595 1 32
1253 5627 1 32
1317 5659 1 32
1383 5692 1 33
1447 5724 1 32
1511 5756 1 32
1575 5788 1 32
1639 5820 1 32
1703 5852 1 32
1767 5884 1 32
1831 5916 1 32
1895 5948 1 32
1959 5980 1 32
2025 6013 1 33
2089 6045 1 32
2153 6077 1 32
2217 6109 1 32
2281 6141 1 32
2345 6173 1 32
2409 6205 1 32
2473 6237 1 32
2537 6269 1 32
2601 6301 1 32
2665 6333 1 32
2731 6366 1 33
2795 6398 1 32
2859 6430 1 32
2923 6462 1 32
2987 6494 1 32
3051 6526 1 32
3115 6558 1 32
3179 6590 1 32
3243 6622 1 32
3307 6654 1 32
3371 6686 1 32
3437 6719 1 33
3501 6751 1 32
3565 6783 1 32
3629 6815 1 32
3693 6847 1 32
3757 6879 1 32
3821 6911 1 32
3885 6943 1 32
3949 6975 1 32
4013 7007 1 32
4077 7039 1 32
4143 7072 1 33
4207 7104 1 32
4271 7136 1 32
4335 7168 1 32
4399 7200 1 32
4463 7232 1 32
4527 7264 1 32
4591 7296 1 32
4655 7328 1 32
4719 7360 1 32
4785 7393 1 33
4849 7425 1 32
4913 7457 1 32
4977 7489 1 32
5041 7521 1 32
5105 7553 1 32
5169 7585 1 32
5233 7617 1 32
5297 7649 1 32
5361 7681 1 32
5425 7713 1 32
5491 7746 1 33
5555 7778 1 32
5619 7810 1 32
5683 7842 1 32
5747 7874 1 32
5811 7906 1 32
5875 7938 1 32
5939 7970 1 32
6003 8002 1 32
6067 8034 1 32
6131 8066 1 32
6197 8099 1 33
6261 8131 1 32
6325 8163 1 32
6389 8195 1 32
6453 8227 1 32
6517 8259 1 32
6581 8291 1 32
6645 8323 1 32
6709 8355 1 32
6773 8387 1 32
6839 8420 1 33
6903 8452 1 32
6967 8484 1 32
7031 8516 1 32
7095 8548 1 32
7159 8580 1 32
7223 8612 1 32
7287 8644 1 32
7351 8676 1 32
7415 8708 1 32
7479 8740 1 32
7545 8773 1 33
7609 8805 1 32
7673 8837 1 32
7737 8869 1 32
7801 8901 1 32
7865 8933 1 32
7929 8965 1 32
7993 8997 1 32
8057 9029 1 32
8121 9061 1 32
8185 9093 1 32
8251 9126 1 33
8315 9158 1 32
8379 9190 1 32
8443 9222 1 32
8507 9254 1 32
8571 9286 1 32
8635 9318 1 32
8699 9350 1 32
8763 9382 1 32
8827 9414 1 32
8891 9446 1 32
8957 9479 1 33
9021 9511 1 32
9085 9543 1 32
9149 9575 1 32
9213 9607 1 32
9277 9639 1 32
9341 9671 1 32
9405 9703 1 32
9469 9735 1 32
9533 9767 1 32
9599 9800 1 33
9663 9832 1 32
9727 9864 1 32
9791 9896 1 32
9855 9928 1 32
9919 9960 1 32
9983 9992 1 32
9999 10000 1 8

12c Adaptive Plans

12c在优化器方面引入了不少的新特性,其中一个就是Adaptive Plans

在特定的条件下优化器会使用这个新特性,比如sql语句包含了表关联、复杂的谓词条件导致很难精准的估算cardinality,Adaptive Plans使优化器可以等到真正在执行sql语句的时候才决定采用哪种执行计划。

优化器会优先选择一个默认执行计划,同时搭配一个_statistics collectors_,这样就能发现实际的cardinality和估算的cardinality之间是否有比较大的区别,如果有明显区别,那么Adaptive Plans这个新特性就能自动的去选择更优的执行计划。

Adaptive Join Methods

优化器能动态调整连接方法,为整个plan的多个部分预先设定多个子plan。

例如上图,优化器默认选择的计划是PRODUCTS表走索引扫描,然后与ORDERS全表扫描的结果集做嵌套循环,但同时优化器也提供了另一种关联方式,就是两个表都走全表扫描,然后做hash join,然后具体执行的时候该选择那一种就是一个比较复杂的判断了。

sql语句在开始执行之前,statistics collectors会收集必要的信息来提供给下一步的执行计划,而具体要收集哪些信息则是有优化器来决定。首先优化器会计算出一个特定的拐点,作为各个子plan的判断依据。比如假设ORDERS表返回的结果集小于10,这个时候nested loop的COST更优,而结果集大于10时,hash join的COST更好,那么优化器就计算出10这个值作为拐点。优化器之后就会让statistics collectors去统计返回值,如果扫描到10行以上,则从NESTED LOOPS切换到HASH JOIN,否则则维持NESTED LOOPS。statistics collectors主要的作用就是在ORDERS表做全表扫描的过程中进行监控和缓存行,优化器根据statistics collectors得到的信息来决定使用哪个执行计划。

先建两张表

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table t1 purge;
drop table t2 purge;

create table t1 as select object_id ,object_name,owner from dba_objects where object_id is not null;
alter table t1 add primary key(object_id);
create index idx_t1 on t1(object_name);

create table t2 as select object_id ,object_name,owner from dba_objects where object_id is not null;
alter table t2 add primary key(object_id);


EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

做一个两表关联的sql,看执行计划会是什么样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select t1.owner ,t2.owner 
from t1,t2
where t1.object_id=t2.object_id
and t1.object_name='TS$';

-------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)|
| 1 | NESTED LOOPS | | 1 | 55 | 5 (0)|
| 2 | NESTED LOOPS | | 1 | 55 | 5 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 45 | 4 (0)|
|* 4 | INDEX RANGE SCAN | IDX_T1 | 1 | | 3 (0)|
|* 5 | INDEX UNIQUE SCAN | SYS_C007364 | 1 | | 0 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 10 | 1 (0)|
-------------------------------------------------------------------------------------------

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

4 - access("T1"."OBJECT_NAME"='TS$')
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

- this is an adaptive plan >>>>====表示自适应执行计划

可以通过DBMS_XPLAN.DISPLAY_CURSOR的新参数看到一个自适应计划的每一个步骤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

Plan hash value: 970098525

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|- * 1 | HASH JOIN | | 1 | 55 | 5 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 55 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 55 | 5 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 45 | 4 (0)| 00:00:01 |
| * 6 | INDEX RANGE SCAN | IDX_T1 | 1 | | 3 (0)| 00:00:01 |
| * 7 | INDEX UNIQUE SCAN | SYS_C007364 | 1 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 10 | 1 (0)| 00:00:01 |
|- 9 | TABLE ACCESS FULL | T2 | 1 | 10 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - access("T1"."OBJECT_NAME"='TS$')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)

每个ID前面带有’-‘符号的表示这个操作并没有被执行计划所采用。

为了得到真实的执行计划,通常建议采用/*+ GATHER_PLAN_STATISTICS */ hint的方式来执行语句,然后通过allstats last的方式来查询结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner 
from t1,t2
where t1.object_id=t2.object_id
and t1.object_name='TS$';

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2
where t1.object_id=t2.object_id and t1.object_name='TS$'

Plan hash value: 970098525

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 |
|- * 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 |
|- 4 | STATISTICS COLLECTOR | | 1 | | 1 |00:00:00.01 | 4 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 1 |00:00:00.01 | 4 |
| * 6 | INDEX RANGE SCAN | IDX_T1 | 1 | 1 | 1 |00:00:00.01 | 3 |
| * 7 | INDEX UNIQUE SCAN | SYS_C007364 | 1 | 1 | 1 |00:00:00.01 | 2 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 1 | 1 |00:00:00.01 | 1 |
|- 9 | TABLE ACCESS FULL | T2 | 0 | 1 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------

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

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - access("T1"."OBJECT_NAME"='TS$')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

现在对表中的数据做一些处理,看执行计划是否能自适应变更

1
2
3
4
5
6
7
8
9
update t1
set object_name='TS$'
where rownum>60000;
commit;

59999 rows updated.

SYS@ora12c>
Commit complete.

现在T1表的查询条件满足的值不再是一条,而是60000条,这里我并没有重新收集统计信息,看最新的执行计划会是怎样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SQL_ID  49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2
where t1.object_id=t2.object_id and t1.object_name='TS$'

Plan hash value: 970098525

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 59999 |00:00:00.15 | 62438 |
|- * 1 | HASH JOIN | | 1 | 1 | 59999 |00:00:00.15 | 62438 |
| 2 | NESTED LOOPS | | 1 | 1 | 59999 |00:00:00.13 | 62438 |
| 3 | NESTED LOOPS | | 1 | 1 | 59999 |00:00:00.13 | 2439 |
|- 4 | STATISTICS COLLECTOR | | 1 | | 59999 |00:00:00.08 | 1094 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 59999 |00:00:00.03 | 1094 |
| * 6 | INDEX RANGE SCAN | IDX_T1 | 1 | 1 | 59999 |00:00:00.06 | 565 |
| * 7 | INDEX UNIQUE SCAN | SYS_C007364 | 59999 | 1 | 59999 |00:00:00.02 | 1345 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 | 59999 | 1 | 59999 |00:00:00.03 | 59999 |
|- 9 | TABLE ACCESS FULL | T2 | 0 | 1 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------

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

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - access("T1"."OBJECT_NAME"='TS$')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)

可以看到重新执行以后,执行计划并未有什么改变,这是由于最终的执行计划是由第一次运行时所决定了

清除shared pool,强制硬解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SYS@ora12c> alter system flush shared_pool
2 ;

System altered.

SYS@ora12c> @1
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 49n5jxqsqksss, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */t1.owner ,t2.owner from t1,t2
where t1.object_id=t2.object_id and t1.object_name='TS$'

Plan hash value: 925498821

----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 59999 |00:00:00.08 | 1268 | | | |
| * 1 | HASH JOIN | | 1 | 1 | 59999 |00:00:00.08 | 1268 | 4515K| 2259K| 4429K (0)|
|- 2 | NESTED LOOPS | | 1 | 1 | 59999 |00:00:00.04 | 635 | | | |
|- 3 | NESTED LOOPS | | 1 | 1 | 59999 |00:00:00.04 | 635 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 59999 |00:00:00.03 | 635 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 59999 |00:00:00.02 | 635 | | | |
| * 6 | INDEX RANGE SCAN | IDX_T1 | 1 | 1 | 59999 |00:00:00.01 | 224 | | | |
|- * 7 | INDEX UNIQUE SCAN | SYS_C007364 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|- 8 | TABLE ACCESS BY INDEX ROWID | T2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS FULL | T2 | 1 | 1 | 72661 |00:00:00.01 | 633 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------

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

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
6 - access("T1"."OBJECT_NAME"='TS$')
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
- this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.

最终的结果显示完整的执行计划并没有区别,由于硬解析过程中_STATISTICS COLLECTOR_重新收集了数据,所以根据新的统计信息优化器选择了更优的子计划,用hash join代替了之前的nested loop。

Adaptive Parallel Distribution Method

当一个sql语句并发执行时,例如排序、分组或者关联时需要数据分布在多个不同的并发子进程中。优化器选择哪种分布操作取决于有多少并发子进程,多少行数据。如果优化器无法准确的估算出有多少数据,那就不能得到最优的分布操作。

由于新的自适应分布操作特性(HYBRID HASH),优化器可以直到在最终执行sql时才决定采用哪种分布操作,同理会先采用_STATISTICS COLLECTOR_收集一波需要缓存的数据信息,根据得到的rows信息来决定分布操作。同样会计算出一个拐点,根据收集到的值与拐点最比较,最终确定分布操作是用HASH还是广播。跟Adaptive JOIN METHOD只影响第一次执行不同的是,Adaptive Parallel Distribution Method影响sql的每一次执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
EXEC DBMS_STATS.gather_table_stats(USER, 'T1');
EXEC DBMS_STATS.gather_table_stats(USER, 'T2');

SYS@ora12c> alter system flush shared_pool;

System altered.

select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner
from t1,t2
where t1.object_id=t2.object_id
and t1.object_name='TS$';

SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

SQL_ID gjm4v4t32wamn, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS PARALLEL(8)*/t1.owner ,t2.owner from
t1,t2 where t1.object_id=t2.object_id and t1.object_name='TS$'

Plan hash value: 435755347

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 59999 |00:00:00.44 | 40 | | | | |
| 1 | PX COORDINATOR | | 1 | | 59999 |00:00:00.44 | 40 | 73728 | 73728 | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 60537 | 0 |00:00:00.01 | 0 | | | | |
|* 3 | HASH JOIN BUFFERED | | 0 | 60537 | 0 |00:00:00.01 | 0 | 7106K| 3091K| 4581K (1)| 1024 |
| 4 | PX RECEIVE | | 0 | 72661 | 0 |00:00:00.01 | 0 | | | | |
| 5 | PX SEND HYBRID HASH | :TQ10000 | 0 | 72661 | 0 |00:00:00.01 | 0 | | | | | >>>>====新的HYBRID HASH分布操作
| 6 | STATISTICS COLLECTOR | | 0 | | 0 |00:00:00.01 | 0 | | | | |
| 7 | PX BLOCK ITERATOR | | 0 | 72661 | 0 |00:00:00.01 | 0 | | | | |
|* 8 | TABLE ACCESS FULL | T2 | 0 | 72661 | 0 |00:00:00.01 | 0 | | | | |
| 9 | PX RECEIVE | | 0 | 60537 | 0 |00:00:00.01 | 0 | | | | |
| 10 | PX SEND HYBRID HASH | :TQ10001 | 0 | 60537 | 0 |00:00:00.01 | 0 | | | | | >>>>====新的HYBRID HASH分布操作
| 11 | PX BLOCK ITERATOR | | 0 | 60537 | 0 |00:00:00.01 | 0 | | | | |
|* 12 | TABLE ACCESS FULL | T1 | 0 | 60537 | 0 |00:00:00.01 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------

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

3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
8 - access(:Z>=:Z AND :Z>=:Z)
12 - access(:Z>=:Z AND :Z>=:Z)
filter("T1"."OBJECT_NAME"='TS$')

Note
-----
- Degree of Parallelism is 8 because of hint

Adaptive Bitmap Index Pruning

当优化器生成一个星型转换的执行计划时,必须要选择正确的位图索引结合方式,用以减少访问相关的ROWIDS。如果访问了太多索引,而部分索引又不能很好的过滤数据,那么减少访问的索引量无疑能增加执行效率。Adaptive Bitmap Index Pruning这个特性就是用来自动做这些索引的裁剪工作。

先构建一个星型转换的场景

新建一张事实表和三张维度表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
create table DIM1 (dim1_id number,DIM1_COD varchar2(10),DIM1_TXT varchar2(10));
insert into DIM1 values(1,'One','One');
insert into DIM1 values(2,'Two','Two');
insert into DIM1 values(3,'Three','Three');
insert into DIM1 values(4,'Four','Four');
insert into DIM1 values(5,'Five','Five');
insert into DIM1 values(6,'Six','Six');
insert into DIM1 values(7,'Seven','Seven');
insert into DIM1 values(8,'Eight','Eight');
insert into DIM1 values(9,'Nine','Nine');
insert into DIM1 values(10,'Ten','Ten');
commit;

create table DIM2 as select DIM1_ID DIM2_ID,DIM1_COD DIM2_COD,DIM1_TXT DIM2_TXT from DIM1 where rownum>=10;
create table DIM3 as select DIM1_ID DIM3_ID,DIM1_COD DIM3_COD,DIM1_TXT DIM3_TXT from DIM1 where rownum>=10;

create table FACT as select rownum FACT_ID,DIM1_ID,DIM2_ID,DIM3_ID,mod(rownum,1000)/10 FACT_MESURE from
DIM1,DIM2,DIM3,(select * from dual connect by level>=1000);

alter table DIM1 add constraint DIM1PK primary key(DIM1_ID);
alter table FACT add constraint DIM1FK foreign key (DIM1_ID) references DIM1;
create index DIM1BX on FACT(DIM1_ID);
alter table DIM2 add constraint DIM2PK primary key(DIM2_ID);
alter table FACT add constraint DIM2FK foreign key (DIM2_ID) references DIM2;
create index DIM2BX on FACT(DIM2_ID);
alter table DIM3 add constraint DIM3PK primary key(DIM3_ID);
alter table FACT add constraint DIM3FK foreign key (DIM3_ID) references DIM3;
create index DIM3BX on FACT(DIM3_ID);

SYS@ora12c> alter session set star_transformation_enabled=true;

Session altered.

收集相关统计信息

1
2
3
4
exec DBMS_STATS.gather_table_stats(USER, 'DIM1');
exec DBMS_STATS.gather_table_stats(USER, 'DIM2');
exec DBMS_STATS.gather_table_stats(USER, 'DIM3');
exec DBMS_STATS.gather_table_stats(USER, 'FACT');

当维度表的数据很少时,如果索引的选择性又很好,那么星型转换的效率很高。如下结果:首先对3张维度表进行了扫描(每张表都应用了对应的谓词条件),对三个结果集进行了笛卡尔积关联得到满足所有谓词的数据,得到的结果集包含用于访问fact表的维度键以及得到最后结果所需要的相关信息。对于每一个维度键扫描bitmap index得到相应的行,这些bitmaps随后合并到一起,做完and运算后转换成ROWID后找到对应fact表。这里statistics collectors的目的主要是提供缓存的数据信息,来确定之后是用nested loop还是其他。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
SQL_ID  4dk2udqhwz501, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */* from FACT join DIM1
using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID) where
DIM1_COD='One' and DIM2_COD='One' and DIM3_COD='One'

Plan hash value: 246786650

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000 |00:00:00.04 | 1636 | | | |
| * 1 | HASH JOIN | | 1 | 1000 | 1000 |00:00:00.04 | 1636 | 1123K| 1123K| 414K (0)|
| 2 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.01 | 21 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.01 | 12 | | | |
| * 4 | TABLE ACCESS FULL | DIM1 | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 5 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 9 | 2048 | 2048 | 2048 (0)|
| * 6 | TABLE ACCESS FULL | DIM2 | 1 | 1 | 1 |00:00:00.01 | 9 | | | |
| 7 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 9 | 2048 | 2048 | 2048 (0)|
| * 8 | TABLE ACCESS FULL | DIM3 | 1 | 1 | 1 |00:00:00.01 | 9 | | | |
| 9 | VIEW | VW_ST_A500F760 | 1 | 1000 | 1000 |00:00:00.04 | 1615 | | | |
| 10 | NESTED LOOPS | | 1 | 1000 | 1000 |00:00:00.04 | 1615 | | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | 1 | 1000 | 1000 |00:00:00.04 | 615 | | | |
| 12 | BITMAP AND | | 1 | | 1 |00:00:00.04 | 615 | | | |
| 13 | BITMAP MERGE | | 1 | | 1 |00:00:00.01 | 201 | 1024K| 512K|20480 (0)|
| 14 | BITMAP KEY ITERATION | | 1 | | 1 |00:00:00.01 | 201 | | | |
| * 15 | TABLE ACCESS FULL | DIM1 | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 16 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 1 |00:00:00.01 | 198 | | | |
| * 17 | INDEX RANGE SCAN | DIM1BX | 1 | | 100K|00:00:00.07 | 198 | | | |
|- 18 | STATISTICS COLLECTOR | | 1 | | 2 |00:00:00.01 | 207 | | | |
| 19 | BITMAP MERGE | | 1 | | 2 |00:00:00.01 | 207 | 1024K| 512K|47104 (0)|
| 20 | BITMAP KEY ITERATION | | 1 | | 2 |00:00:00.01 | 207 | | | |
| * 21 | TABLE ACCESS FULL | DIM2 | 1 | 1 | 1 |00:00:00.01 | 9 | | | |
| 22 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 2 |00:00:00.01 | 198 | | | |
| * 23 | INDEX RANGE SCAN | DIM2BX | 1 | | 100K|00:00:00.06 | 198 | | | |
|- 24 | STATISTICS COLLECTOR | | 1 | | 5 |00:00:00.01 | 207 | | | |
| 25 | BITMAP MERGE | | 1 | | 5 |00:00:00.01 | 207 | 1024K| 512K| 146K (0)|
| 26 | BITMAP KEY ITERATION | | 1 | | 5 |00:00:00.01 | 207 | | | |
| * 27 | TABLE ACCESS FULL | DIM3 | 1 | 1 | 1 |00:00:00.01 | 9 | | | |
| 28 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 5 |00:00:00.01 | 198 | | | |
| * 29 | INDEX RANGE SCAN | DIM3BX | 1 | | 100K|00:00:00.06 | 198 | | | |
| 30 | TABLE ACCESS BY USER ROWID | FACT | 1000 | 1 | 1000 |00:00:00.01 | 1000 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------

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

1 - access("ITEM_3"="DIM3"."DIM3_ID" AND "ITEM_2"="DIM2"."DIM2_ID" AND "ITEM_1"="DIM1"."DIM1_ID")
4 - filter("DIM1"."DIM1_COD"='One')
6 - filter("DIM2"."DIM2_COD"='One')
8 - filter("DIM3"."DIM3_COD"='One')
15 - filter("DIM1"."DIM1_COD"='One')
17 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
21 - filter("DIM2"."DIM2_COD"='One')
23 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
27 - filter("DIM3"."DIM3_COD"='One')
29 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")

Note
-----
- star transformation used for this statement
- this is an adaptive plan (rows marked '-' are inactive)

假设表记录估算错误,导致维度表不能很好的过滤掉数据,fact表中大部分的数据都满足维度表的要求,那么优化器会直接先忽略掉那个维度表的条件,先做其他纬度表的bitmap合并操作,等到最后再与这个表做必要的关联。

现在对数据做下处理,更改一下DIM3表的数据,使其所有的数据都满足谓词条件

1
2
3
4
5
6
7
8
9
update DIM3
set DIM3_COD='One';


10 rows updated.

SYS@ora12c> SYS@ora12c> commit;

Commit complete.

重新查看新的执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
SYS@ora12c> alter system flush shared_pool;

System altered.

SYS@ora12c> @1
SYS@ora12c> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4dk2udqhwz501, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */* from FACT join DIM1
using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID) where
DIM1_COD='One' and DIM2_COD='One' and DIM3_COD='One'

Plan hash value: 788957811

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:00.18 | 3457 | 3 | | | |
| * 1 | HASH JOIN | | 1 | 1000 | 10000 |00:00:00.18 | 3457 | 3 | 1123K| 1123K| 921K (0)|
| 2 | MERGE JOIN CARTESIAN | | 1 | 1 | 10 |00:00:00.01 | 21 | 0 | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 1 | 1 |00:00:00.01 | 12 | 0 | | | |
| * 4 | TABLE ACCESS FULL | DIM1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
| 5 | BUFFER SORT | | 1 | 1 | 1 |00:00:00.01 | 9 | 0 | 2048 | 2048 | 2048 (0)|
| * 6 | TABLE ACCESS FULL | DIM2 | 1 | 1 | 1 |00:00:00.01 | 9 | 0 | | | |
| 7 | BUFFER SORT | | 1 | 1 | 10 |00:00:00.01 | 9 | 0 | 2048 | 2048 | 2048 (0)|
| * 8 | TABLE ACCESS FULL | DIM3 | 1 | 1 | 10 |00:00:00.01 | 9 | 0 | | | |
| 9 | VIEW | VW_ST_A500F760 | 1 | 1000 | 10000 |00:00:00.18 | 3436 | 3 | | | |
| 10 | NESTED LOOPS | | 1 | 1000 | 10000 |00:00:00.18 | 3436 | 3 | | | |
| 11 | BITMAP CONVERSION TO ROWIDS | | 1 | 1000 | 10000 |00:00:00.15 | 2392 | 3 | | | |
| 12 | BITMAP AND | | 1 | | 1 |00:00:00.15 | 2392 | 3 | | | |
| 13 | BITMAP MERGE | | 1 | | 1 |00:00:00.01 | 201 | 0 | 1024K| 512K|20480 (0)|
| 14 | BITMAP KEY ITERATION | | 1 | | 1 |00:00:00.01 | 201 | 0 | | | |
| * 15 | TABLE ACCESS FULL | DIM1 | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
| 16 | BITMAP CONVERSION FROM ROWIDS | | 1 | | 1 |00:00:00.01 | 198 | 0 | | | |
| * 17 | INDEX RANGE SCAN | DIM1BX | 1 | | 100K|00:00:00.14 | 198 | 0 | | | |
|- 18 | STATISTICS COLLECTOR | | 1 | | 2 |00:00:00.01 | 207 | 0 | | | |
| 19 | BITMAP MERGE | | 1 | | 2 |00:00:00.01 | 207 | 0 | 1024K| 512K|47104 (0)|
| 20 | BITMAP KEY ITERATION | | 1 | | 2 |00:00:00.01 | 207 | 0 | | | |
| * 21 | TABLE ACCESS FULL | DIM2 | 1 | 1 | 1 |00:00:00.01 | 9 | 0 | | | |
| 22 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 2 |00:00:00.01 | 198 | 0 | | | |
| * 23 | INDEX RANGE SCAN | DIM2BX | 1 | | 100K|00:00:00.12 | 198 | 0 | | | |
|- 24 | STATISTICS COLLECTOR | | 1 | | 5 |00:00:00.12 | 1984 | 3 | | | | >>>>====
|- 25 | BITMAP MERGE | | 1 | | 5 |00:00:00.12 | 1984 | 3 | 1024K| 512K| 1454K (0)| >>>>====
|- 26 | BITMAP KEY ITERATION | | 1 | | 50 |00:00:00.01 | 1984 | 3 | | | | >>>>====
|- * 27 | TABLE ACCESS FULL | DIM3 | 1 | 1 | 10 |00:00:00.01 | 9 | 0 | | | | >>>>====
|- 28 | BITMAP CONVERSION FROM ROWIDS| | 10 | | 50 |00:00:00.03 | 1975 | 3 | | | | >>>>====
|- * 29 | INDEX RANGE SCAN | DIM3BX | 10 | | 1000K|00:00:01.65 | 1975 | 3 | | | | >>>>====
| 30 | TABLE ACCESS BY USER ROWID | FACT | 10000 | 1 | 10000 |00:00:00.01 | 1044 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------

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

1 - access("ITEM_3"="DIM3"."DIM3_ID" AND "ITEM_2"="DIM2"."DIM2_ID" AND "ITEM_1"="DIM1"."DIM1_ID")
4 - filter("DIM1"."DIM1_COD"='One')
6 - filter("DIM2"."DIM2_COD"='One')
8 - filter("DIM3"."DIM3_COD"='One')
15 - filter("DIM1"."DIM1_COD"='One')
17 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
21 - filter("DIM2"."DIM2_COD"='One')
23 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
27 - filter("DIM3"."DIM3_COD"='One')
29 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")

Note
-----
- star transformation used for this statement
- this is an adaptive plan (rows marked '-' are inactive)

可以看到我标注的部分全部都被优化器忽略掉了,并没有走DIM3表的位图扫描、合并等操作,只是最后过滤的时候用到了DIM3进行了hash join。