度量快速开发平台-专业、快速的软件定制快开平台

标题: Oracle Statistic统计信息 [打印本页]

作者: 张兴康    时间: 2020-7-7 14:00
标题: Oracle Statistic统计信息
1.统计信息简介

统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO正是根据这些统计信息数据,计算出不同访问路径下,不同join 方式下,各种计划的成本,最后选择出成本最小的计划。

在CBO(基于代价的优化器模式)条件下,SQL语句的执行计划由统计信息来决定,若没有统计信息则会采取动态采样的方式决定执行计划!可以说统计信息关乎sql的执行计划是否正确,属于sql执行的指导思想,oracle的初始化参数statistics_level控制收集统计信息的级别,有三个参数值:

BASIC :收集基本的统计信息

TYPICAL:收集大部分统计信息(数据库的默认设置)

ALL:收集全部统计信息

Oracle 10g之后,Query Optimizer就已经将CBO作为默认优化器,并且Oracle官方不再支持RBO服务。但是,通过优化器参数optimizer_mode,我们可以控制Oracle优化器生成不同模式下的执行计划。
2.如何收集统计信息
2.1 统计信息的内容:
1)Table statistics
Number of rows --行数量
Number of blocks --block数量
Average row length --平均行的长度.
2)Column statistics
Number of distinct values (NDV) in column --列中distinct的值
Number of nulls in column --列中null的值
Data distribution (histogram)  --数据分布
3)Index statistics
Number of leaf blocks --子节点的块数量
Levels --子节点数量
Clustering factor --集群因子
4)System statistics
I/O performance and utilization --IO性能和利用率
CPU performance and utilization --CPU的性能和利用率
2.2 收集统计信息
Oracle Statistic 的收集,可以使用analyze 命令,也可以使用DBMS_STATS 包来收集,Oracle 建议使用DBMS_STATS包来收集统计信息,因为DBMS_STATS包收集的更广,并且更准确。analyze 在以后的版本中可能会被移除。
DBMS_STATS常用的几个过程如下:
  1. dbms_stats.gather_table_stats 收集表、列和索引的统计信息;
  2. dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息;
  3. dbms_stats.gather_index_stats 收集索引的统计信息;
  4. dbms_stats.gather_system_stats 收集系统统计信息
  5. dbms_stats.GATHER_DICTIONARY_STATS:所有字典对象的统计;
  6. DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计
  7. dbms_stats.delete_table_stats 删除表的统计信息
  8. dbms_stats.delete_index_stats 删除索引的统计信息
  9. dbms_stats.export_table_stats 输出表的统计信息
  10. dbms_stats.create_state_table
  11. dbms_stats.set_table_stats 设置表的统计
  12. dbms_stats.auto_sample_size
复制代码
analyze 命令的语法如下:
  1. SQL>analyze table tablename compute statistics;
  2. SQL>analyze table tablename compute statistics for all indexes;
  3. SQL>analyze table tablename delete statistics
复制代码
2.3 统计信息的分类
Oracle 的Statistic 信息的收集分两种:自动收集和手工收集。
Oracle 的Automatic Statistics Gathering 是通过Scheduler 来实现收集和维护的。Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息:
(1)Missing statistics(统计信息缺失)
(2)Stale statistics(统计信息陈旧)
该Job 是在数据库创建的时候自动创建,并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。 默认情况下,job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级,再开始进行统计信息。
Scheduler Job的stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False,那么GATHER_STATS_JOB 会中断, 而没有收集完的对象将在下次启动时继续收集。
Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下:
(1)对象的统计信息之前没有收集过。
(2)当对象有超过10%的rows 被修改,此时对象的统计信息也称为stale statistics。
但是对于高度变化的表在白天的活动期间被TRUNCATE/DROP并重建或者块加载超过本身总大小10%的对象;我们可以将这些表上的统计设置为NULL
可以通过以下SQL来查看:
  1. select job_name, program_name, enabled, stop_on_window_close
  2.   from dba_scheduler_jobs
  3. where job_name = 'gather_stats_job';
复制代码
为了决定是否对对象进行监控,Oracle 提供了一个参数STATISTICS_LEVEL。通过设置初始化参数STATISTIC_LEVEL 为TYPICAL 或ALL,就可以自动收集统计信息(默认值为TYPICAL,因此可以随即启用自动收集统计信息的功能)。STATISTIC_LEVEL 参数的值可以激活GATHER_STATS_JOB。
在10g中表监控默认是激活的,如果STATISTICS_LEVEL设置为basic,不仅不能监控表,而且将禁掉如下一些10g的新功能:
(1)ASH(Active Session History)
(2)ASSM(Automatic Shared Memory Management)
(3)AWR(Automatic Workload Repository)
(4)ADDM(Automatic Database Diagnostic Monitor)
  1. sys@ORCL> show parameter statistics_level;
  2.       
  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ---------
  5. statistics_level                     string      TYPICAL
复制代码
当启动对象的监控后,从上次统计信息收集之后的的信息,如inserts,updates,deletes 等,这些改变的信息会记录到user_tab_modifications 视图。
当对象的数据发生改变之后, 经过几分钟的延时,这些信息写入到user_tab_modifications视图,然后dbms_stats.flush_database_monitoring_info过程就会发现这些信息,并讲这些信息保存在内存中。
当监控的对象被修改的部分超过10%时,gather_database_stats 或者gather_schema_stats 过程就会去收集这些stale statistics
3.统计信息的存储位置以及常用数据字典
3.1 统计信息常用数据字典
统计信息收集如下数据:
(1)表自身的分析: 包括表中的行数,数据块数,行长等信息。
(2)列的分析:包括列值的重复数,列上的空值,数据在列上的分布情况。
(3)索引的分析: 包括索引叶块的数量,索引的深度,索引的聚合因子等。
这些统计信息存放在以下的数据字典里:
  1. DBA_TABLES
  2. DBA_OBJECT_TABLES
  3. DBA_TAB_STATISTICS
  4. DBA_TAB_COL_STATISTICS
  5. DBA_TAB_HISTOGRAMS
  6. DBA_INDEXES
  7. DBA_IND_STATISTICS
  8. DBA_CLUSTERS
  9. DBA_TAB_PARTITIONS
  10. DBA_TAB_SUBPARTITIONS
  11. DBA_IND_PARTITIONS
  12. DBA_IND_SUBPARTITIONS
  13. DBA_PART_COL_STATISTICS
  14. DBA_PART_HISTOGRAMS
  15. DBA_SUBPART_COL_STATISTICS
  16. DBA_SUBPART_HISTOGRAMS
复制代码
3.2 表的统计信息
包含表行数,使用的块数,空的块数,块的使用率,行迁移和链接的数量,pctfree,pctused的数据,行的平均大小:
  1. SELECT NUM_ROWS, --表中的记录数
  2. BLOCKS, --表中数据所占的数据块数
  3. EMPTY_BLOCKS, --表中的空块数
  4. AVG_SPACE, --数据块中平均的使用空间
  5. CHAIN_CNT, --表中行连接和行迁移的数量
  6. AVG_ROW_LEN --每条记录的平均长度
  7. FROM USER_TABLES
复制代码
3.3索引列的统计信息
包含索引的深度(B-Tree的级别),索引叶级的块数量,集群因子(clustering_factor), 唯一值的个数。
  1. SELECT BLEVEL, --索引的层数
  2. LEAF_BLOCKS, --叶子结点的个数
  3. DISTINCT_KEYS, --唯一值的个数
  4. AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
  5. AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
  6. CLUSTERING_FACTOR --群集因子
  7. FROM USER_INDEXES
复制代码
3.4 列的统计信息
包含唯一的值个数,列最大小值,密度(选择率),数据分布(直方图信息),NUll值个数
  1. SELECT NUM_DISTINCT, --唯一值的个数
  2. LOW_VALUE, --列上的最小值
  3. HIGH_VALUE, --列上的最大值
  4. DENSITY, --选择率因子(密度)
  5. NUM_NULLS, --空值的个数
  6. NUM_BUCKETS, --直方图的BUCKET个数
  7. HISTOGRAM --直方图的类型
  8. FROM USER_TAB_COLUMNS
复制代码









作者: 张兴康    时间: 2020-7-7 14:01

作者: fteair    时间: 2020-7-7 17:32

作者: caixuqad    时间: 2020-7-7 17:34

作者: 张兴康    时间: 2020-7-8 15:01

作者: fteair    时间: 2020-7-8 17:34

作者: caixuqad    时间: 2020-7-8 17:46

作者: 张兴康    时间: 2020-7-9 14:04





欢迎光临 度量快速开发平台-专业、快速的软件定制快开平台 (http://plat.delit.cn/) Powered by Discuz! X3.2