04 系统和对象统计信息

本章提要
----------------------------------------------------
工具包 dbms_stats
系统统计
对象统计
通用服务
----------------------------------------------------
实际上, 仅知道要处理的SQL语句和它引用的对象结构, 查询优化器还是无法提供高效的执行计划, 优化器还必须要量化待处理的数据量.
4.1 工具包 dbms_stats 简介
    过去, 对象统计是通过SQL语句ANALYZE来收集, 现在不这样了, 现在推荐使用 dbms_stats.
4.2 系统统计(感觉默认就可以了)
4.3 对象统计信息(比较重要)
    有三种类型的对象统计信息, 表统计, 列统计, 索引统计.
   
    测试, 使用对象统计信息:
   

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 => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
cascade => TRUE);
END;
/

-- ============ 表统计信息 ============= 
SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
 FROM user_tab_statistics
 WHERE table_name = 'T';

-- result
/*
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
1000 44 0 0 0 265 
num_rows: 表中数据的行数
blocks: 高水位下面的数据块个数
empty_blocks: 高水位上面的数据块个数, dbms_stats不计算这个值, 设置为0
avg_space: 表里数据块的平均空闲空间(字节), dbms_stats不计算这个值, 设置为0
chain_cnt: 涉及行链接和行迁移的总行数, dbms_stats不计算这个值, 被设置为0
avg_row_len: 表中平均每个记录的长度(字节)
*/

-- ============ 列统计信息 ============= 
SELECT column_name AS "NAME",
 num_distinct AS "#DST",
 low_value,
 high_value,
 density AS "DENS",
 num_nulls AS "#NULL",
 avg_col_len AS "AVGLEN",
 histogram,
 num_buckets AS "#BKT"
 FROM user_tab_col_statistics
 WHERE table_name = 'T';
-- result
/*
NAME #DST LOW_VALUE HIGH_VALUE DENS #NULL AVGLEN HISTOGRAM #BKT
---- ----- -------------- -------------- ------- ----- ------ --------------- -----
ID 1000 C102 C20B .00100 0 4 NONE 1
VAL1 431 C103 C2213E .00254 503 3 HEIGHT BALANCED 254
VAL2 6 C20202 C20207 .00050 0 4 FREQUENCY 6
VAL3 6 C20202 C20207 .00050 0 4 FREQUENCY 6
PAD 1000 202623436F2943 7E79514A202D49 .00100 0 251 HEIGHT BALANCED 254
7334237B426574 4649366C744E25
336E4A5B302E4F 3F36264C692755
4B53236932303A 7A57737C6D4B22
21215F46 59414C44

num_distinct: 该列中唯一值的数量
low_value: 该列中最小值, 这里无法读懂, 需要借助别的函数
high_value:  该列中最大值, 这里无法读懂, 需要借助别的函数
density: 0-1之间的小树, 接近0表示对于列的过滤操作能去掉大多数行
num_nulls: 该列中null的总数
avg_col_len: 平均列大小, 以字节表示
histogram: 表明是否有直方图统计信息
num_buckets: 直方图里桶(bucket)数.

-- 直方图
关于数据不均匀分布的额外信息叫做直方图, 
 */
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 = 'VAL2'
 ORDER BY endpoint_number;
-- result
/*
ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ----------
101 8 8
102 33 25
103 101 68
104 286 185
105 788 502
106 1000 212 
 */

-- =========== 索引统计信息 ===================
SELECT index_name AS name,
 blevel,
 leaf_blocks AS leaf_blks,
 distinct_keys AS dst_keys,
 num_rows,
 clustering_factor AS clust_fact,
 avg_leaf_blocks_per_key AS leaf_per_key,
 avg_data_blocks_per_key AS data_per_key
 FROM user_ind_statistics
 WHERE table_name = 'T';
-- result
/*
NAME BLEVEL LEAF_BLKS DST_KEYS NUM_ROWS CLUST_FACT LEAF_PER_KEY DATA_PER_KEY
---------- ------ --------- -------- -------- ---------- ------------ ------------
T_VAL2_I 1 2 6 1000 153 1 25
T_VAL1_I 1 2 431 497 479 1 1
T_PK 1 2 1000 1000 980 1 1

blevel, 分支数量
leaf_blocks, 叶子块数
distinct_keys, 键值总数
num_rows, 索引中的键值数, 对于主键来说, 等同于distinct_keys
clustering_factor: 聚簇因子, 大师的索引一章有介绍这个因子.
avg_leaf_blocks_per_key, 存放一个键值的平均叶子块数
avg_data_blocks_per_key, 单个键引用的平均数据块数
 */

4-1

    收集对象统计信息
    以前是dba负责收集, oracle10g以后, 创建数据库的时候, 就创建了一个定期收集对象统计信息的作业并进行调度.
    用dbms_stats收集统计信息:
        gather_database_stats 收集整个数据库的对象的统计信息
        gather_dictionary_stats 收集数据字典的对象统计信息
        gather_fixed_objects_stats 收集数据字典里面的称之为固定表的特定对象的统计信息, 10g以后才有用. v$fixed_table
        gather_schema_stats 收集整个模式的所有对象的统计信息
        gather_table_stats 收集表(索引可选)的对象统计信息
        gather_index_stats 收集索引的对象统计信息
        可以看到, 没有必要单独收集列的统计信息.
        收集过程中用到的参数:
       
        以上图标中, 经常使用的有: ownname: 模式名, indname: 索引名, tabname: table 名, cascade: 是否包括索引,
            obj_filter_list:制定至少在对象负荷一个传递过来参数条件才收集统计信息.        
            estimate_percent: 指明收集统计信息方式, 11g以后使用比较好, 如果数据库觉得这个参数值太小会自动增大, 要加速收集
                统计信息的过程, 通常这个数设置小一点比较合适, 10%通常是不错的选择, 对于大表来说, 0.5%或0.1%乃至更小, 也不错.
        还有很多参数可以设置, 这里还是用到再说吧, 太多参数了.
    锁定对象统计信息
    有时候, 比如你想使用旧的统计信息, 想要确保统计信息不变, 通过:
    dbms_stats.lock_schema_stats(ownname=> user)  -- 锁定schema统计信息
    dbms_stats.loc_table_stats(ownname=>user, tabname=>'T')  -- 锁定表统计信息
    调用 unlock 可以解锁:
    dbms_stats.unlock_schema_stats(ownname=>user)
    以上的锁 与 oracle 传统意义上的锁不同.
    当对象被锁定后, 再调用 gather来更新统计信息, 会得到错误.
    此外, 还可以比较对象的统计信息和删除对象的统计信息
    
这一章, 有很多dbms_status相关的参数和操作, 感觉无法记住, 还是等用到的时候, 再回头来查看吧.