--Oracle 优化器统计信息
Oracle优化器统计信息描述了关于数据库和相关对象的统计信息,当执行SQL查询时,优化器会使用这些统计信息估算出各种不同的执行计划的资源消耗,
,从而选择最高效的执行计划。当统计信息缺失或者陈旧时,Oracle可能会选择错误的执行计划,导致SQL执行效率低下。
优化器统计信息包括以下部分:
表级别统计信息
1. 数据行数
2. 数据块数。
3. 每行的长度
--相关性能视图
admin@ORCL> SELECT NUM_ROWS, BLOCKS, AVG_ROW_LEN FROM USER_TABLES WHERE TABLE_NAME = 'T';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
14301 200 102
字段的统计信息
1. 唯一值的个数。
2. 空值的个数
3. 数据分布情况(直方图)
4. 相关列统计信息(Extended statistics)
admin@ORCL> SELECT NUM_DISTINCT,NUM_NULLS,HISTOGRAM FROM USER_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OWNER';
NUM_DISTINCT NUM_NULLS HISTOGRAM
------------ ---------- ---------------
10 0 FREQUENCY
admin@ORCL> SELECT * FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T' AND COLUMN_NAME = 'OWNER';
TABLE COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
----- -------------------- --------------- -------------- --------------------
T OWNER 13 3.3888E+35
T OWNER 15 3.3913E+35
T OWNER 39 3.5442E+35
T OWNER 41 4.1186E+35
T OWNER 43 4.1192E+35
T OWNER 1379 4.1711E+35
T OWNER 4885 4.3277E+35
T OWNER 5102 4.3277E+35
T OWNER 5208 4.5330E+35
T OWNER 5508 4.5831E+35
索引统计信息
1. 索引叶子块的个数
2. 索引的高度(Levels)
3. 集群因子(Clustering Factor)
admin@ORCL> SELECT BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR FROM DBA_INDEXES WHERE INDEX_NAME = 'IDX_T';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
1 61 28600
系统级别的统计信息
1. I/O 执行性能和消耗
2. CPU 执行性能和消耗
Oracle会根据以上的统计信息来计算每种执行计划的Cost,然后选择Cost最小的执行计划(即CBO)。Cost是Oracle对执行计划消耗的计算指标。
CBO的计算模型是Oracle内部的,目前是没有公布的,而且Oracle的计算模型是十分复杂的,我们只能依据一些实验来答题猜测CBO的计算模型。
admin@ORCL> CREATE TABLE T1 AS
SELECT
TRUNC(DBMS_RANDOM.VALUE(0,25)) N1,
RPAD('X',40) IND_PAD,
TRUNC(DBMS_RANDOM.VALUE(0,20)) N2,
LPAD(ROWNUM,10,'0') SMALL_VC,
RPAD('X',200) PADDING
FROM ALL_OBJECTS
WHERE ROWNUM<= 10000;
Table created.
--创建索引
admin@ORCL> create index t1_i1 on t1(n1,ind_pad,n2) nologging pctfree 91;
Index created.
admin@ORCL> SELECT
2 NUM_ROWS, --记录数
3 DISTINCT_KEYS, --不重复的键值数
4 BLEVEL, --索引数的级别
5 LEAF_BLOCKS, --叶子块数
6 CLUSTERING_FACTOR, --聚促因子
7 AVG_LEAF_BLOCKS_PER_KEY,
8 AVG_DATA_BLOCKS_PER_KEY
9 FROM USER_INDEXES WHERE TABLE_NAME = 'T1' AND INDEX_NAME = 'T1_I1';
NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- ----------------------- -----------------------
10000 500 2 1111 9754 2 19
--收集表统计信息
admin@ORCL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true,method_opt=>'for all columns size 1')
PL/SQL procedure successfully completed.
admin@ORCL> select small_vc from T1 where n1 = 2 and n2 = 3;
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2886394002
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 340 | 25 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 20 | 340 | 25 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | T1_I1 | 20 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=2 AND "N2"=3)
filter("N2"=3)
--字段的selectivity
admin@ORCL> select COLUMN_NAME,NUM_DISTINCT from user_tab_col_statistics where table_name = 'T1' and column_name in ('N1','N2');
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
N1 25
N2 20
第一步的成本:
= blevel+ceil((n1.selectivity*n2.selectivity)*leaf_blocks) = 2+ceil((1/20*1/25)*1111) = 5
第二步的成本
=ceil(n1.selectivity*n2.selectivity*clustering_fact) = 20
通过以上的数据模型可以看出,涉及到Oracle的COST成本包括字段的可选择性(不收集直方图),索引的叶子块数,及索引的集群因子。
关于代价的计算
cost = io cost+cpu cost
主要的代价是io cost,极小部分是cpu cost
全表的计算
1.没有系统统计信息
cost = blocks/k
k是db_file_multiple_read_count的修正值
索引代价的计算
unique scan = blevel +1(1这里指通过索引访问表的代价)
Fast full scan = leaf_blocks/k
index only = blevel+FF*leaf_blocks(只通过索引扫描得到查询的结果,不用访问表数据)
Range scan = blevel+FF*leaf_blocks+FF*clustering(FF*Clustering 指聚簇因子影响索引来访问表的数据)
FF指filter factor,数据的选择率。