04 系统和对象统计信息

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

CREATE TABLE tASSELECT 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 padFROM all_objectsWHERE rownum <= 1000ORDER 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);BEGINdbms_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不计算这个值, 设置为0avg_space: 表里数据块的平均空闲空间(字节), dbms_stats不计算这个值, 设置为0chain_cnt: 涉及行链接和行迁移的总行数, dbms_stats不计算这个值, 被设置为0avg_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 1VAL1 431 C103 C2213E .00254 503 3 HEIGHT BALANCED 254VAL2 6 C20202 C20207 .00050 0 4 FREQUENCY 6VAL3 6 C20202 C20207 .00050 0 4 FREQUENCY 6PAD 1000 202623436F2943 7E79514A202D49 .00100 0 251 HEIGHT BALANCED 2547334237B426574 4649366C744E25336E4A5B302E4F 3F36264C6927554B53236932303A 7A57737C6D4B2221215F46 59414C44num_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 8102 33 25103 101 68104 286 185105 788 502106 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 25T_VAL1_I 1 2 431 497 479 1 1T_PK 1 2 1000 1000 980 1 1blevel, 分支数量leaf_blocks, 叶子块数distinct_keys, 键值总数num_rows, 索引中的键值数, 对于主键来说, 等同于distinct_keysclustering_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相关的参数和操作, 感觉无法记住, 还是等用到的时候, 再回头来查看吧.