Optimizer statistics are a collection of data that describe more details about the database and the objects in the database. These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

 

　　　　Data distribution (histogram)

优化器统计范围：

表统计； --行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；
列统计； --列中唯一值的数量（NDV），NULL值的数量，数据分布；
             --DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；
索引统计；--叶块数量，等级，聚簇因子；
             --DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；
系统统计；--I/O性能与使用率；
             --CPU性能与使用率；
             --存储在aux_stats$中，需要使用dbms_stats收集，I/O统计在X$KCFIO中；

1。统计命令语句介绍

1.1Analyze:
　　需要使用ANALYZE统计的统计：
　　使用LIST CHAINED ROWS和VALIDATE子句；
　　收集空闲列表块的统计；

　　ANALYZE缺点：

　　不适合做分区表分析
　　无法提供灵活的分析选项
　　无法提供并行的分析
　　无法对分析数据进行管理

1.2dbms_stats：
　　dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。
这个包的下面四个存储过程分别收集index、table、schema、database的统计信息:
　　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

1.3统计收集的权限
==========================
必须授予普通用户权限
　　sys@ORADB> grant execute_catalog_role to hr;
　　sys@ORADB> grant connect,resource,analyze any to hr;

统计收集的时间考虑
==========================
　　当参数STATISTICS_LEVEL设置为TYPICAL或者ALL，系统会在夜间自动收集统计信息。
查看系统自动收集统计信息的job：
　　SELECT * FROM dba_scheduler_jobs WHERE job_name = ‘GATHER_STATS_JOB‘;
也可以disable自动收集统计信息：
　　BEGIN
　　　　dbms_scheduler.disable(‘GATHER_STATS_JOB‘);
　　END;

1.4使用手工统计

　　对所有更改活动中等的对象自动统计应该足够充分，由于自动统计收集在夜间进行，因此对于一些更新频繁的对象其统计可能已经过期。两种典型的对象：
高度变化的表在白天的活动期间被TRUNCATE/DROP并重建；
块加载超过本身总大小10%的对象；

对于第一种对象可以使用以下两种方法：
　　a.将这些表上的统计设置为NULL，当Oracle遇到没有统计的表时，将动态收集必要的统计作为查询优化的一部分；
动态收集特征由OPTIMIZER_DYNAMIC_SAMPLING控制，这个参数应该设置为大于等于2，默认为2。可以通过删除并锁住统计将统计设置为NULL：
DBMS_STATS.DELETE_TABLE_STATS(‘SCHEMA‘,‘TABLE‘);
DBMS_STATS.LOCK_TABLE_STATS(‘SCHEMA‘,‘TABLE‘);

　  b.将这些表上的统计设置为代表表典型状态的值。在表具有某个有代表性的值时收集统计，然后锁住统计；
　　由于夜间收集的统计未必适合于白天的负载，因此这些情况下使用手工收集比GATHER_STATS_JOB更有效。
　　对于块加载，统计应该在加载后立刻收集，通常合并在加载语句的后面防止遗忘。
　　对于外部表，统计不能通过GATHER_DATABASE_STATS，GATHER_SCHEMA_STATS以及自动统计收集收集。因此需要使用GATHER_TABLE_STATS在单个表上收集统计，并且在外部表上不支持取样，ESTIMATE_PERCENT应该被显示设置为NULL。
　　如果STATISTICS_LEVEL设置为BASIC禁用了监控特征，自动统计收集将不会检测过期的统计，此时需要手工收集。

　　需要手工收集的另一个地方是系统统计，其不会自动收集。
　　对于固定表，如动态性能表，需要使用GATHER_FIXED_OBJECTS_STATS收集，这些表上的统计应该在数据库具有有代表性的活动后收集。

1.5统计收集考虑
==========================

• 统计收集使用取样

　　不使用抽样的统计收集需要全表扫描并且排序整个表，抽样最小化收集统计的必要资源。
Oracle推荐设置DBMS_STATS的ESTIMATE_PERCENT参数为DBMS_STATS.AUTO_SAMPLE_SIZE在达到必要的统计精确性的同时最大化性能。

• 并行统计收集

　　Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。
　　聚簇索引，域索引，位图连接索引不能并行收集。

• 分区对象的统计收集

　　对于分区表和索引，DBMS_STATS可以收集单独分区的统计和全局分区，对于组合分区，可以收集子分区，分区，表/索引上的统计，分区统计的收集可以通过声明参数GRANULARITY。根据将优化的SQL语句，优化器可以选择使用分区统计或全局统计，对于大多数系统这两种统计都是很重要的，Oracle推荐将GRANULARITY设置为AUTO同时收集全部信息。

• 列统计和直方图

　　当在表上收集统计时，DBMS_STATS收集表中列的数据分布的信息，数据分布最基本的信息是最大值和最小值，但是如果数据分布是倾斜的，这种级别的统计对于优化器来说不够的，对于倾斜的数据分布，直方图通常用来作为列统计的一部分。
直方图通过METHOD_OPT参数声明，Oracle推荐设置METHOD_OPT为FOR ALL COLUMNS SIZE AUTO，使用该值时Oracle自动决定需要直方图的列以及每个直方图的桶数。也可以手工设置需要直方图的列以及桶数。
　　如果在使用DBMS_STATS的时候需要删除表中的所有行，需要使用TRUNCATE代替drop/create，否则自动统计收集特征使用的负载信息以及RESTORE_*_STATS使用的保存的统计历史将丢失。这些特征将无法正常发挥作用。

• 确定过期的统计

　　对于那些随着时间更改的对象必须周期性收集统计，为了确定过期的统计，Oracle提供了一个表监控这些更改，这些监控默认情况下在STATISTICS_LEVEL为TYPICAL/ALL时启用，该表为USER_TAB_MODIFICATIONS。使用DBMS_STATS.FLUSH_DATABASE _MONITORING_INFO可以立刻反映内存中超过监控的信息。在OPTIONS参数设置为GATHER STALE or GATHER AUTO时，DBMS_STATS收集过期统计的对象的统计。

• 用户定义统计

　　在创建了基于索引的统计后，应该在表上收集新的列统计，这可以通过调用过程设置METHOD_OPT的FOR ALL HIDDEN COLUMNS。

• 何时收集统计

　　对于增量更改的表，可能每个月/每周只需要收集一次，而对于加载后表，通常在加载脚本中增加收集统计的脚本。对于分区表，如果仅仅是一个分区有了较大改动，只需要收集一个分区的统计，但是收集整个表的分区也是必要的。

1.6系统统计
==========================
　　系统统计描述系统硬件的特征，包括I/O和CPU。在选择执行计划时，优化器考虑查询所需的CPU和I/O代价。系统统计允许优化器更加精确的评价CPU和IO代价，选择更好的查询计划。
使用DBMS_STATS.GATHER_SYSTEM_STATS收集系统统计，Oracle推荐收集系统统计。收集系统统计需要DBA权限。
收集的优化器系统统计包括：
cpuspeedNW：代表无负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位Millions/sec。
ioseektim：I/O查找时间=查找时间+延迟时间+OS负载时间；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为ms。
Iotfrspeed：I/O传输速度；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为Bytes/ms.
Cpuspeed：代表有负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Millions/sec。
Maxthr：最大I/O吞吐量；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Bytes/sec.
Slavethr：服务I/O吞吐量是平均并行服务I/O吞吐量；通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；Bytes/sec.
Sreadtim：随机读取单块的平均时间；通过设置gathering_mode =INTERVAL，START|STOP或手工设置统计；单位为ms。
Mreadtim：顺序读取多块的平均时间，通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为ms。
Mbrc: 多块读平均每次读取的块数量；通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为blocks。

系统统计的重新收集不会导致当前的SQL无效，只是所有的新SQL语句使用新的统计。

Oracle提供两个选项收集统计：负载统计；非负载统计。

1.7负载统计
==========================
　　在负载窗口的开始运行dbms_stats.gather_system_stats(’start’)，然后运行dbms_stats.gather_system_stats(’stop’)结束负载窗口。
　　运行dbms_stats.gather_system_stats(’interval’, interval=>N)，N表示N分钟后系统统计收集结束。
　　运行dbms_stats.delete_system_stats()删除负载统计。

1.8非负载统计
==========================
　　运行不带参数的dbms_stats.gather_system_stats()收集非负载统计，运行非负载统计时会有一定的I/O负载。在某些情况下，非负载统计的值可能会保持默认，此时需要使用dbms_stats.set_system_stats设置。

1.9管理统计
==========================
　　转储先前版本的统计
　　使用RESTORE过程转储先前版本的统计，这些过程使用一个时间戳作为参数，包含统计时间的视图包括：
　　1.)DBA_OPTSTAT_OPERATIONS：其中包含了使用DBMS_STATS在模式/系统级别执行的统计操作；
　　2.)*_TAB_STATS_HISTORY：包含了表统计更改的历史。
　　旧的统计定期刷新，根据DBMS_STATS的ALTER_STATS_HISTORY_RETENTION过程设置而定，默认为31天。
默认情况下，如果STATISTICS_LEVEL为TYPICAL/ALL，自动刷新启用；否则需要使用PURGE_STAT手工刷新。

其他转储与刷新相关的信息包括：
　　PURGE_STATS：     手工刷新超过某个时间戳的旧统计；
　　GET_STATS_HISTORY_RENTENTION：   得到当前历史统计保留值；
　　GET_STATS_HISTORY_AVAILABILTY： 得到可用的最旧的统计的时间戳。
转储的限制：
　　1.)不能转储用户定义统计；
　　2.)如果使用了ANALYZE收集，旧的统计将无法转储。

1.10导入/导出统计
==========================
　　导出统计前需要使用DBMS_STATS.CREATE_STAT_TABLE创建一个统计表保留统计，在表创建后可以使用DBMS_STATS.EXPORT_*_STATS导出统计到自定义表，这些统计可以使用DBMS_STATS.IMPORT_*_STATS重新导入。
也可以使用IMP/EXP导到其他数据库。

转储统计与导入导出统计

使用转储的情况：
　　1.) 恢复旧版本的统计；
　　2.)希望数据库管理统计历史的保留和刷新；
使用EXPORT/IMPORT_*_STATS的情况：
　　1.) 实验各种值的不同情况；
　　2.) 移动统计到不同数据库；
　　3.) 保留统计数据更长的时间。

1.11锁住表和模式的统计
==========================
　　一旦统计被锁住，将无法在更改这些统计直到被解锁。DBMS_STAT提供两个过程用于解锁，两个用于加锁：
　　1.)LOCK_SCHEMA_STATS；?¤LOCK_TABLE_STATS；
　　2.)UNLOCK_SCHEMA_STATS；?¤UNLOCK_TABLE_STATS；

1.12设置统计
==========================
　　可以使用SET_*_STATISTICS设置表，索引，列，系统统计。

2 使用动态取样评价统计
==========================
　　动态取样的目的是通过为谓词选择性和表/索引统计确定更加精确的估计提高服务器性能，估计越精确产生的性能更好。
可以使用动态取样的情况：
　　1.) 在收集的统计不能使用或会导致严重的估计错误时估计单表的谓词选择性；
　　2.) 估计没有统计的表/索引的统计；
　　3.) 估计统计过期的表和索引的统计；
动态取样特征由参数OPTIMIZER_DYNAMIC_SAMPLING控制，默认级别为2。

动态取样的工作机制
　　主要的性能特征是编译时，Oracle在编译时决定一个查询是否能通过取样获益，如果可以，将用递归SQL随机扫描一小部分表块，然后应用相关的单表谓词评价谓词选择性。

　　使用动态取样的时间
　　使用动态取样将获益的情况：
　　1.) 可以发现更好的执行计划；
　　2.) 取样时间仅占总时间的一小部分；
　　3.) 查询将执行多次；

取样级别
==========================
　　范围从1..10

缺失统计处理
==========================
　　当Oracle遇到丢失统计时，优化器动态必要的统计。在某些情况下，Oracle无法执行动态取样，包括：远程表/外部表，此时将使用默认统计。
缺失统计时的表默认值：
　　1.) Cardinality：num_of_blocks * (block_size - cache_layer) / avg_row_len
　　2.) Average row length：100字节；
　　3.) Number of blocks：100或基于分区映射的实际值；
　　4.) Remote cardinality：2000行；
　　5.) Remote average row length：100字节；
缺失统计时的索引默认值：
　　Levels：1
　　Leaf blocks：25
　　Leaf blocks/key：1
　　Data blocks/key：1
　　Distinct keys：100
　　Clustering factor：800

2.1gather_schema_stats
==========================

options参数使用4个预设的方法：
　　gather——重新分析整个架构（Schema）。
　　gather empty——只分析目前还没有统计的表。
　　gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
　　gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。类似于组合使用gather stale和gather empty。

注意，无论gather stale还是gather auto，都要求进行监视。
　　如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。
这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。
　　SELECT * FROM Sys.Dba_Tab_Modifications WHERE Table_Owner = ‘SCOTT‘;
使用alter table xxx monitoring命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。
auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）
来创建直方图。使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gather auto。
begin
dbms_stats.gather_schema_stats(ownname => ‘SCOTT‘,
                               estimate_percent => dbms_stats.auto_sample_size,
                               method_opt => ‘for all columns size auto‘,
                               degree => 7);
end;

estimate_percent选项
　　以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size
要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。

method_opt选项
　　dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。
某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。
　　如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。
为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：　　　　　　method_opt=>‘for all columns size skewonly‘
method_opt=>‘for all columns size repeat‘
method_opt=>‘for all columns size auto‘

skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。
　　假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，那么为了检索这些行，全表扫描的速度会快于索引扫描。

　　重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。
--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(ownname => ‘SCOTT‘,
                               estimate_percent => dbms_stats.auto_sample_size,
                               method_opt => ‘for all columns size repeat‘,
                               degree => 7);
end;

　　Oracle中关于表的统计信息是在数据字典中的，可以下SQL查询到:
SELECT Table_Name,Num_Rows,Blocks,Empty_Blocks,Avg_Space,Chain_Cnt,Avg_Row_Len,Sample_Size,Last_Analyzed
FROM Dba_Tables WHERE wner = ‘SCOTT‘ ;

这是对命令与工具包的一些总结
1)、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
　　a) 可以并行进行，对多个用户，多个Table
　　b) 可以得到整个分区表的数据和单个分区的数据。
　　c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区 ,但不收集聚簇统计
　　d) 可以倒出统计信息
　　e) 可以用户自动收集统计信息
2)、DBMS_STATS的缺点
　　a) 不能Validate Structure
　　b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
　　c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3)、对于External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

2.2 GATHER_TABLE_STATS
==========================
DBMS_STATS.gather_table_stats
    (ownname varchar2,
     tabname varchar2,
     partname varchar2 default null,
     estimate_percent number default   to_estimate_percent_type(get_param(‘ESTIMATE_PERCENT‘)),
     block_sample boolean default FALSE,
     method_opt varchar2 default get_param(‘METHOD_OPT‘),
     degree number default to_degree_type(get_param(‘DEGREE‘)),
     granularity varchar2 default get_param(‘GRANULARITY‘),
     cascade boolean default to_cascade_type(get_param(‘CASCADE‘)),
     stattab varchar2 default null, statid varchar2 default null,
     statown varchar2 default null,
     no_invalidate boolean default to_no_invalidate_type(get_param(‘NO_INVALIDATE‘)),
     stattype varchar2 default ‘DATA‘,
     force boolean default FALSE);

参数说明:

例子:

 

---------------------------------------
2.3 10g自动收集统计信息
---------------------------------------
　　从10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。
这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。
调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

可以通过以下查询这个JOB的运行情况：
　　SELECT * FROM Dba_Scheduler_Jobs WHERE Job_Name = ‘GATHER_STATS_JOB‘;
其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：
　　SELECT Job_Name, Last_Start_Date FROM Dba_Scheduler_Jobs;

　　JOB_NAME                       　　　　　LAST_START_DATE
　　------------------------------ ------------------------------------
　　AUTO_SPACE_ADVISOR_JOB         30-OCT-08 10.00.01.463000 PM +08:00
　　GATHER_STATS_JOB               　　30-OCT-08 10.00.01.463000 PM +08:00

　　然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。
所以建议最好关闭这个自动统计信息收集功能：

关闭及开启自动搜集功能，有两种方法，分别如下：
方法一：
　　exec dbms_scheduler.disable(‘SYS.GATHER_STATS_JOB‘);
　　exec dbms_scheduler.enable(‘SYS.GATHER_STATS_JOB‘);
方法二：
　　alter system set "_optimizer_autostats_job"=false scope=spfile;
　　alter system set "_optimizer_autostats_job"=true scope=spfile;

---------------------------------------
查看统计
---------------------------------------
表/索引/列上的统计

---------------------------------------
2.4 直方图统计
---------------------------------------
　　直方图的类型存储在*TAB_COL_STATISTICS视图的HISTOGRAM列上。

　　概念:

　　直方图是一种统计学上的工具，并非Oracle专有。Oracle对列上的数据分布进行统计分析。直方图分为频率直方图(Frequency)和高度平衡(Height  balanced)直方图。默认如果一个倾斜列上的唯一值超过了254个，那么ORACLE会对此列建立等高直方图，否则建立等频直方图。
　　可以通过all/dba/user_histograms查到直方图的信息。

---------------------------------------
2.5分区表的统计信息实例
---------------------------------------  
ORATEA ORACLE的统计信息在执行SQL的过程中扮演着非常重要的作用，而且ORACLE在表的各个层次都会有不同的统计信息，通过这些统计信息来描述表的，列的各种各样的统计信息。下面通过一个复合分区表来说明一些常见的和常见的统计信息。

2.6 查询各类统计信息

1.表级的统计信息

2.表上列的统计信息

3.表上列的直方图信息

4.分区的统计信息

5.分区上列的统计信息

6.分区上列的直方图信息

7.子分区的统计信息

8.子分区上的列的统计信息

9.子分区上的列的直方图信息

我们对这个复合分区分析之后产生了上面这九种不同层次的统计信息。CBO想要得要一个高效的执行计划需要如此多的统计信息.