系统统计信息与对象统计信息

系统统计信息数据字典aux_stats$

SYSSTATS_INFO这一组数据包括系统统计信息的状态和收集时间
SYSSTATS_MAIN系统统计信息的结果集
--CPUSPEEDNW 一个CPU一秒钟能处理的操作数据，单位百万次
--IOSEEKTIM 平均磁盘寻道时间，单位是毫秒，默认10
--IOTFRSPEED 平均每毫秒从磁盘传输的字节数，默认4096
收集非工作量统计信息
exec dbms_stats.gather_system_stats(gathering_mode=>'noworkload');

收集工作量统计信息
工作量统计信息是基于数据库负载状态下的信息，收集步骤：
1. 启动快照
exec dbms_stats.gather_system_stats(gathering_mode=>'start');
命令执行后，初始信息将保存到aux_stats$
select * from sys.aux_stats$ where SNAME='SYSSTATS_TEMP'

2. 等待足够的时间产生一个有代表性的负载后再进行第二次快照
3. 执行第二次快照
exec dbms_stats.gather_system_stats(gathering_mode=>'stop');
4．根据两次快照之间的差值，计算出系统统计信息

计算访问一个列的开销
cpu_cost=column_position * 20

由I/O开销和CPU开销来计算总开销
cost= io_cost + cpu_cost/cupseed*sreadtim*1000

计算非工作量统计信息sreadtim
sreadtim=ioseektim+db_block_size/iotfrspeed

计算非工作量统计信息mreadtim
mreadtim=ioseektim+mbrc*db¬_block_size/iotfrspeed

三种类型的确对象统计信息：表统计、列统计、索引统计

收集对象统计信息：
sys@ORCL>BEGIN
dbms_stats.gather_table_stats(ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
cascade => TRUE);
END;
/
查询表统计信息
select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len from user_tab_statistics where table_name='T'

--num_rows 表中数据的行数
--blocks 高水位以下的数据块个数
--avg_space 表中数据块的平均空闲空间(字节)
--chain_cnt 涉及行链接和行迁移的总行数
--avg_row_len 表中平均每个记录的长度(字节)

查询列统计信息
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 该列中最小值
--hight_value该列中最大值
--density 0到1之间的一个小数，接近0表示对于列的过滤操作能去掉多数行
--num_nulls 该列中存储的NULL总数
--avg_col_len列平均大小
--histogram 表明是否有直方图统计信息
--num_buckets直方图里的桶数

 

 

两种类型的直方图：频度直方图(frequency histogram)和等高直方图(height-balanced histogram)

查询直方图的统计信息
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;
--桶数等于唯一值总数
--endpoint_value提供该值本身
--endpoint_numbber取值的累计出现次数

查询等高直方图信息
SELECT count(*), max(val2) AS endpoint_value, endpoint_number
FROM (
  SELECT val2, ntile(5) OVER (ORDER BY val2) AS endpoint_number
  FROM t
)
GROUP BY endpoint_number
ORDER BY endpoint_number;

SELECT endpoint_value, endpoint_number
FROM user_tab_histograms
WHERE table_name = 'T'
AND column_name = 'VAL2'
ORDER BY endpoint_number;

扩展统计信息：基于表达式或一组列上的对象统计信息和直方图(11g)

SELECT dbms_stats.create_extended_stats(ownname   => user,
                                        tabname   => 'T',
                                        extension => '(upper(pad))'),
       dbms_stats.create_extended_stats(ownname   => user,
                                        tabname   => 'T',
                                        extension => '(val2,val3)')

查询扩展统计信息
SELECT extension_name, extension
FROM user_stat_extensions
WHERE table_name = 'T';

删除扩展统计信息

BEGIN
  dbms_stats.drop_extended_stats(ownname   => user,
                                 tabname   => 'T',
                                 extension => '(upper(pad))');
  dbms_stats.drop_extended_stats(ownname   => user,
                                 tabname   => 'T',
                                 extension => '(val2,val3)');
END;

oracle 11g 虚列
CREATE TABLE persons (
  name VARCHAR2(100),
  name_upper AS (upper(name))
);

索引统计信息
索引结构：根块、分支块、叶子块

查询索引统计信息
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';

--blevel:访问叶子块而需要读取的分支块数量，包括根块
--leaf_blocks:索引中叶子块数
--distinct_keys:索引中的唯一键值总数
--num_rows:索引中的键值数
--clustering_factor:表明有多少临近的索引条目指到不同的数据块
--avg_leaf_blocks_per_key:存放一个键值的平均叶子块数 avg_leaf_blocks_per_key=leaf_blocks/distinct_keys
--avg_data_blocks_per_key:单个键引用的平均数据块数 avg_data_blocks_per_key=clustering_factor/distincet_keys

使用dbms_stats包收集对象统计信息
dbms_stats.gather_database_stats收集整个数据库的对象统计信息
dbms_stats.gather_dictionary_stats收集数据字典的对象统计信息
dbms_stats.gather_fixed_objects_stats收集数据字典中固定表的特定对象统计信息
dbms_stats.gather_schema_stats收集整个模式的所有对象统计信息
dbms_stats.gather_table_stats收集表(包括索引)的对象统计信息
dbms_stats.gather_index_stats收集索引对象统计信息
--目标参数
--ownname模式名称
--indname索引名称
--tabname表名称
--partname分区或子分区名称
--comp_id指定用于处理的组件的ID
--granularity指定要处理的分区的统计级别
--cascade指名是否收集索引统计信息
--gather_sys指明是否收集sys用户的统计信息
--gather_temp指明是否收集临时表的统计信息
--option 指明处理对象以及处理方式
--objlist根据参数option不同取值，返回被处理对象的列表
--force指明是否覆盖锁定的对象统计信息
--obj_filter_list指定至少在对象符合一个传递过来的参数才收集统计信息
--收集参数
--estimate_percent指明是否采样收集统计信息
--block_sample指明是否采用数据行采样或者数据块采样来收集统计信息
--method_opt不但指明是否收集直方图的统计信息还指明采样最大的桶数
--degree 指明一个对象统计时所用的从属进程数据
--no_invalidate指明是否使用与收集的对象相关的游标失效
--备份表
--stattab指定数据字典以外的存储统计信息的备份表
--statid可选标识符
--statown指定备份表的用户名

11g待定的统计信息
--将统计信息的收集与发布分离

10g调度收集统计信息：由gather_stats_job调度

11g调试收集统计作息：收集统计信息的作业集成了自动维护任务

锁定对象统计信息
--lock_schema_stats锁住一个模式的所有对象统计信息
dbms_stats.lock_schema_stats(ownname => user);
--lock_table_stats锁住一张表的统计信息
dbms_stats.lock_table_stats(ownname=>user,tabname=>'T')
--unlock_schema_stats将属于一个模式的对象全部解锁
--unlock_table_stats解除一张表上的统计信息锁定

比较对象统计信息
--diff_table_stats_in_stattab 比较当前的统计信息和备份表里的统计信息
--diff_table_stats_in_history 拿表的当前对象统计信息或一组历史统计信息与其他对象的历史统计信息做比较
--diff_table_stats_in_pending 拿表的当前对象统计信息或历史统计信息和待定统计信息做比较

删除对象统计信息
--delete_database_stats
--delete_dictionary_stats
--delete_fixed_object_stats
--delete_schema_stats
--delete_table_stats
--delete_column_stats
--delete_index_stats

保持统计信息时效性的策略
--定期收集
--一个表中有超过10%的行被修改
--使用批处理任务修改大里数据，直接收集
--收集统计信息导致低效的执行计划解决办法：1.恢复原始统计信息；2.检查统计信息是否正确描述了数据的分布

统计信息保留时间和清除
--使用get_stats_history_retention显示当前的配置
--使用dbms_stats.alter_stats_history_retention(retention=>14) 修改配置

查询某表统计信息何时被修改
select * from user_tab_stats_history where table_name='TAB$'

恢复统计信息
--restore_database_stats恢复数据库全部对象的统计信息
--restore_dictionary_stats恢复数据字典对象的统计信息
--restore_fixed_object_stats恢复固定表的统计信息
--restore_system_stats恢复系统统计信息
--restore_schema_stats恢复单个用户的对象统计信息
--restore_table_stats恢复单张表的统计信息
dbms_stats.restore_schema_stats(ownname=>'SH',as_of_timestamp=>systimestamp-1,force=>TRUE)

查询列使用历史

SELECT c.name, cu.timestamp,
       cu.equality_preds AS equality, cu.equijoin_preds AS equijoin,
       cu.nonequijoin_preds AS noneequijoin, cu.range_preds AS range,
       cu.like_preds AS "LIKE", cu.null_preds AS "NULL"
FROM sys.col$ c, sys.col_usage$ cu, sys.obj$ o, sys.user$ u
WHERE c.obj# = cu.obj# (+)
AND c.intcol# = cu.intcol# (+)
AND c.obj# = o.obj#
AND o.owner# = u.user#
AND o.name = 'T'
AND u.name = user
ORDER BY c.col#;