对象统计信息
对象统计信息的作用是帮助查询优化器选择更好的执行计划。比如,某一个表的索引clustering_factor非常的高,这个时候如果在where条件中指定column between A and B的限定条件来查询,那么查询优化器有两个选择,一个是通过索引做索引范围扫描,另一个是全表扫描。 因为我们知道clustering_factor的值非常高,这时候做索引范围扫描的开销可能还会比全表扫描更高。所以我们期望是全表扫描。但是如果数据库中没有关于这个索引数据对象的统计信息,那么查询优化器就不会了解到这个索引的 clustering_factor很高,那么查询优化器就有可能选择索引范围扫描的执行计划,这样就会产生一个低效的执行计划。 所以我们可以看到,对象的统计信息的重要。下面了解一下对象的统计信息。
1. 对象统计信息有哪几种类型
有三种类型的对象统计信息:表统计,列统计,索引统计。针对这三种类型来说,每一种类型又有三个子类型:表或索引级别统计,分区级别统计和子分区级别统计。当然,分区与子分区级别的统计信息只有在对象被分区或者具有子分区的时候才有意义。
表1-1显示了数据字典试图里对象的统计信息
对象 | 表/索引级别统计 | 分区级别统计 | 子分区级别统计 |
表 | user_tab_statistics
| user_tab_statistics | user_tab_statistics |
列 | user_tab_col_statistics user_tab_histograms | user_part_col_statistics user_part_histograms | user_subpart_col_statistics user_subpart_histograms |
索引 | user_ind_statistics
| user_ind_statistics | user_ind_statistics |
表1-2是对表1-1的一些补充,因为在9i之前,以上的有些视图不存在(红色加粗的视图)
对象 | 表/索引级别统计 | 分区级别统计 | 子分区级别统计 |
表 | user_tab_statistics user_tables | user_tab_statistics user_tables | user_tab_statistics user_tables |
列 | user_tab_col_statistics user_tab_histograms | user_part_col_statistics user_part_histograms | user_subpart_col_statistics user_subpart_histograms |
索引 | user_ind_statistics user_indexes | user_ind_statistics user_ind_partitions | user_ind_statistics user_ind_subpartitions |
为了测试,我们创建如下表并手动搜集统计信息。
CREATE TABLE t AS SELECT ROWNUM AS id, ROUND(DBMS_RANDOM.normal*1000) AS val1, 100+ROUND(LN(ROWNUM/3.25+2)) AS val2, 100+ROUND(LN(ROWNUM/3.25+2)) AS val3, DBMS_RANDOM.string('p',250) AS pad FROM all_objects WHERE ROWNUM<=1000 ORDER BY dbms_random.value;
UPDATE t SET val1=NULL WHERE val1<0; ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY(id); CREATE INDEX t_val1_i ON t(val1); CREATE INDEX t_val2_i ON t(val2);
BEGIN dbms_stats.gather_table_stats( ownname => 'SYS', tabname =>'T', method_opt => 'for all columns size skewonly', cascade => TRUE); END;
|
下面详细说一下表,列,索引的统计信息。
1.1 表统计信息
表统计信息从user_tab_statistics中可以得到。
SELECT
num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len
FROM
user_tab_statistics
WHERE
table_name = 'T';
这里要注意的是empty_blocks表示高水位以上的数据块,这里为0,因为dbms_stat不计算其值。Avg_space表示数据块平均空闲空间(字节),这里也不计算,设置为0。Chain_cnt 行链接和航迁移总数,也不计算设置为零。Avg_row_len table中平均行的长度(字节)。
1.2 列统计信息
列统计信息有两种,普通列统计信息和直方图。先看一下普通列统计信息。
普通列统计信息
SELECT column_name, num_distinct, low_value, high_value, density, num_nulls, avg_col_len, histogram,num_buckets FROM user_tab_col_statistics WHERE table_name ='T'; |
Num_distinct 该列中唯一值的数量。
Low_value 该列的最小值,存储为oracle内部格式,对字符串至存储前32字节。
High_value 该列的最大值,存储为oracle内部格式,对字符串至存储前32字节。
Density 0到1之间的一个小数。接近0表示对该列的过滤可以过滤掉大多数行(选择性较强),接近1表示选择性较弱。没有直方图的话其值为1/num_distinct。如果有直方图,则根据直方图计算。
Histograms 是否有直方图,如果有是那种类型。
Num_buckets 直方图里桶数。没有直方图则桶数为1,最大桶数为254。
直方图
查询优化器原则上认为数据是均匀分布的,就如表t的主键列id。这一列的值分布很均匀,在1到1000上平均分布。然而有时数据的分布并不均匀。那么这时查询优化器需要额外的信息才能做出正确的判断。比如我们已经知道了val2列的数据分布如下:
SQL> SELECT val2 as val2,count(*) FROM t GROUP BY val2 ORDER BY val2;
VAL2 COUNT(*) ---------- ---------- 101 8 102 25 103 68 104 185 105 502 106 212
6 rows selected |
如果有一条查询语句以val2=105做过滤条件,这时查询优化器如何选择访问路径?很可能查询优化器会选择索引扫描,因为它并不知道这一列值为105的占了一半还多。如果查询优化器知道了这个信息,那么它就可以果断的选择全表扫描,因为在返回大量数据的情况下,全表扫描比索引开销更小。(当然,本例中t表一共才有1000条数据,所以无论是全表还是索引扫描都会很快返回结果)
要获得这些额外信息,查询优化器可以查询直方图。直方图有两种,频率直方图和等高直方图。他们的区别主要在于桶数和唯一值的个数。直方图的桶数最大为254。如果唯一值的个数小于等于254就可以建立频率直方图,每一个唯一值一个桶。如果唯一值的个数大于254则要建立等高直方图。
频率直方图,下面的查询查询了表t val2列上的频率直方图。
SQL> SELECT endpoint_value, 2 endpoint_number, 3 endpoint_number - lag(endpoint_number,1,0) OVER (ORDER BY endpoint_number) AS frequency 4 FROM user_tab_histograms 5 WHERE table_name='T' AND column_name='VAL2' 6 ORDER BY endpoint_number 7 /
ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY -------------- --------------- ---------- 101 8 8 102 33 25 103 101 68 104 286 185 105 788 502 106 1000 212
6 rows selected |
从上面的查询可以了解到,endpoint_value代表的是直方图中桶内唯一值的内容。Endpoint_number存储的是频率。不过这个频率是累加频率。
等高直方图,下面的语句手动建立了一个等高直方图。
SQL> SELECT count(*) , max(val2) AS endpoint_value , endpoint_number 2 FROM (SELECT val2, ntile(5) over(order by val2) as endpoint_number FROM t) 3 GROUP BY endpoint_number 4 ORDER BY endpoint_number 5 /
COUNT(*) ENDPOINT_VALUE ENDPOINT_NUMBER ---------- -------------- --------------- 200 104 1 200 105 2 200 105 3 200 106 4 200 106 5
|
关于直方图的信息这里讲述的很不完全,需要查看额外的文档具体了解。
1.3 扩展列的统计信息
如果我们在where条件中这样使用一个查询条件upper(val2) = 105,那么查询优化器是无法使用统计信息的。而相应的我们可以这样处理,创建一个upper(val2)的虚列,然后在这个虚列上收集统计信息。不过我们要了解的下面的这个函数可以帮我们完成从创建虚列到收集虚列统计信息的全过程。
SELECT dbms_stats.create_extended_stats(ownname => 'SYS',tabname => 'T',extension => '(UPPER(PAD))') FROM DUAL; |
1.4 索引统计信息
SQL> SELECT INDEX_NAME, 2 BLEVEL, 3 LEAF_BLOCKS, 4 DISTINCT_KEYS, 5 NUM_ROWS, 6 CLUSTERING_FACTOR 7 FROM DBA_IND_STATISTICS WHERE TABLE_NAME='IND_GOOD'; |
上面主要讲解了各种类型的统计信息,以及如何查询这些统计信息,下面讲解一下怎样生成这些统计信息。
2. 收集对象统计信息
收集统计信息要用dbms_stats工具包来收集。里面提供了多个过程:
Gater_database_stats
Gather_dictionary_stats
Gather_fixed_objects_stats
Gather_schema_stats
Gather_table_stats
Gather_index_stats
这些存储过程每个都需要多个参数,这些参数可以分成三大组,第一组,指明对象,第二组,指明收集选项,第三组指明覆盖当前的统计信息之前是否备份。
参数 | 数据库 | 数据字典 | 固定对象 | 模式 | 表 | 索引 |
目标对象 |
|
|
|
|
|
|
Owner |
|
|
| √ | √ | √ |
Indname |
|
|
|
|
| √ |
tabname |
|
|
|
| √ |
|
partname |
|
|
|
| √ | √ |
Comp_id |
| √ |
|
|
|
|
granularity | √ | √ |
| √ | √ | √ |
cascade | √ | √ |
| √ | √ |
|
Gather_sys | √ |
|
|
|
|
|
Gather_temp | √ |
|
| √ |
|
|
options | √ | √ |
| √ |
|
|
objlist | √ | √ |
| √ |
|
|
force |
|
|
| √ | √ | √ |
Obj_filter_list | √ | √ |
| √ |
|
|
收集选项 |
|
|
|
|
|
|
Estimate_percent | √ | √ |
| √ | √ | √ |
Block_sample | √ | √ |
| √ | √ |
|
Method_opt | √ | √ |
| √ | √ |
|
No_invalidate | √ | √ | √ | √ | √ | |
备份表 |
|
|
|
|
|
|
Stattab | √ | √ | √ | √ | √ | √ |
Statid | √ | √ | √ | √ | √ | √ |
statown | √ | √ | √ | √ | √ | √ |
下面介绍一下这些参数的意义。
目标对象参数:
Ownname 指定schema名称。
Indname 指定索引名称。
Tabname 指定表的名称
Partname 指定分区或者子分区的名称。如果不指定,则收集所有分区的对象统计信息。
Comp_id 这个不明确。
Granularity 指定要处理的分区对象的统计级别。
Cascade 指明是否手机索引统计信息。
Gather_sys 指明是否手机sys用户统计信息
Gather_temp 指明是否收集临时表的统计信息。但要注意的是,dbms_stats运行时,会先commit,所以只有指定了on commit preserve rows的临时表才可以被处理。
Options 包含7个选项
Gather: 处理所有对象
Gatherauto 由系统决定那些对象要处理以及如何处理
Gatherstale 只处理信息失效的对象,注意,没有统计信息的不算信失效。
Gatherempty 只处理没有统计信息的对象
List auto 列出将用gather auto处理的对象
Liststale 列出将用gather stale处理的对象
Listempty 列出将用gather empty处理的对象
Objlist 该参数根据options的不同值返回被处理的对象列表。
Obj_filter_list 在目标对象满足该参数指定的过滤条件时才会收集统计信息
收集选项参数:
Estimate_percent指明是否采样收集统计信息。值可以为0.0000001到100。一百表示不采样。 常量 dbms_stats.auto_sample_size算作0,由系统决定采样大小。
Block_sample 指明采样是采用数据行采样还是数据块采样。行采样精确,块采样快捷。
Method_opt 指明是否收集直方图统计信息还指明采样的最大桶数。
值的格式为columns clause + size clause
例如:for all columns size skewonly
Columnsclause可取值有:
for all [indexed / hidden ] columns
Sizeclause 可取值有:
size 1-254
Size skewonly 只收集skew列,桶数由系统决定
Size auto 同上,并且要加上where引用的列
Size repat 只是刷新现有直方图
No_invalidate 收集了统计信息,很有可能有一些游标就不适用了。那么这时这个参数可以指定成三个值。
True游标不失效,这样重新收集统计信息就没意义了。
False游标立即失效,可能会造成集中大量解析游标
Dbms_stats.auto_invalidate游标不立即失效,这样可以避免集中解析问题。
备份表参数
Stattab 指定存储统计信息的备份表
Statid
Statown 指定备份表的用户名,默认是当前用户。