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

标题: 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