04 系统和对象统计信息-白红宇

04 系统和对象统计信息

阅读量：6420 次

发布时间：2019-06-23

本文共 7553 字，大约阅读时间需要 25 分钟。

本章提要

----------------------------------------------------

工具包 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相关的参数和操作, 感觉无法记住, 还是等用到的时候, 再回头来查看吧.

干货

统计信息对优化十分重要, 执行获得准确的统计信息:

1. 系统默认: 11G 以后, 用DBCA创建数据库时, 会默认收集统计信息, 系统会自动做成一个JOB来完成这个工作, 这个工作设定的时间是在晚上22:00, 如果你想修改这个时间, 或者停止自动收集, 请参考如下:

　　个人强烈建议开启: 自动收集统计信息(默认选项)

- 开启或关闭自动收集的job

开启(默认):

begin  dbms_auto_task_admin.enable(  client_name => 'auto optimizer stats collection',  operation => NULL,  window_name => NULL);end;/

enable

　关闭:

begin    dbms_auto_task_admin.disable(    client_name => 'auto optimizer stats collection',    operation => NULL,    window_name => NULL);end;/

disable

查询是否开启了自动收集统计信息:

select client_name,status from dba_autotask_client;

查看具体的自动 job:

select window_name,duration,next_start_date from dba_scheduler_windows;

查询结果有 3 列, 分别是 name, 持续时间, 下次Job 时间;

由于默认的时间是 22:00, 但是这个时候可能是业务的高峰期, 所以, 我们可以通过以下内容, 修改为凌晨 2 点, 持续 4 个小时

begin    sys.dbms_scheduler.set_attribute( name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/begin    sys.dbms_scheduler.set_attribute( name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/begin    sys.dbms_scheduler.set_attribute( name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/begin    sys.dbms_scheduler.set_attribute( name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/begin    sys.dbms_scheduler.set_attribute( name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/begin    sys.dbms_scheduler.set_attribute( name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/begin    sys.dbms_scheduler.set_attribute( name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0' );    sys.dbms_scheduler.set_attribute( name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');end;/

修改时间

2. 手动收集统计信息

参考上边内容

转载于:https://www.cnblogs.com/moveofgod/p/3913375.html

你可能感兴趣的文章

让虚拟机的软盘盘符不显示（适用于所有windows系统包括Windows Server）

查看>>

Cygwin不好用

查看>>

jQuery插件之验证控件jquery.validate.js

查看>>

[经验]无线鼠标和无线键盘真的不能用了？——雷柏的重生之路~