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常用的几个过程如下： - dbms_stats.gather_table_stats 收集表、列和索引的统计信息；
- dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；
- dbms_stats.gather_index_stats 收集索引的统计信息；
- dbms_stats.gather_system_stats 收集系统统计信息
- dbms_stats.GATHER_DICTIONARY_STATS：所有字典对象的统计；
- DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计
- dbms_stats.delete_table_stats 删除表的统计信息
- dbms_stats.delete_index_stats 删除索引的统计信息
- dbms_stats.export_table_stats 输出表的统计信息
- dbms_stats.create_state_table
- dbms_stats.set_table_stats 设置表的统计
- dbms_stats.auto_sample_size
analyze 命令的语法如下： - SQL>analyze table tablename compute statistics;
- SQL>analyze table tablename compute statistics for all indexes;
- SQL>analyze table tablename delete statistics
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来查看： - select job_name, program_name, enabled, stop_on_window_close
- from dba_scheduler_jobs
- 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) - sys@ORCL> show parameter statistics_level;
-
- NAME TYPE VALUE
- ------------------------------------ ----------- ---------
- 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）索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。 这些统计信息存放在以下的数据字典里： - DBA_TABLES
- DBA_OBJECT_TABLES
- DBA_TAB_STATISTICS
- DBA_TAB_COL_STATISTICS
- DBA_TAB_HISTOGRAMS
- DBA_INDEXES
- DBA_IND_STATISTICS
- DBA_CLUSTERS
- DBA_TAB_PARTITIONS
- DBA_TAB_SUBPARTITIONS
- DBA_IND_PARTITIONS
- DBA_IND_SUBPARTITIONS
- DBA_PART_COL_STATISTICS
- DBA_PART_HISTOGRAMS
- DBA_SUBPART_COL_STATISTICS
- DBA_SUBPART_HISTOGRAMS
包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小： - SELECT NUM_ROWS, --表中的记录数
- BLOCKS, --表中数据所占的数据块数
- EMPTY_BLOCKS, --表中的空块数
- AVG_SPACE, --数据块中平均的使用空间
- CHAIN_CNT, --表中行连接和行迁移的数量
- AVG_ROW_LEN --每条记录的平均长度
- FROM USER_TABLES
包含索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数。 - SELECT BLEVEL, --索引的层数
- LEAF_BLOCKS, --叶子结点的个数
- DISTINCT_KEYS, --唯一值的个数
- AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
- AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
- CLUSTERING_FACTOR --群集因子
- FROM USER_INDEXES
包含唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数 - SELECT NUM_DISTINCT, --唯一值的个数
- LOW_VALUE, --列上的最小值
- HIGH_VALUE, --列上的最大值
- DENSITY, --选择率因子（密度）
- NUM_NULLS, --空值的个数
- NUM_BUCKETS, --直方图的BUCKET个数
- HISTOGRAM --直方图的类型
- FROM USER_TAB_COLUMNS
| 
发表于 2020-7-7 14:00:49
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
楼主
