dbms_stats与analyze分析汇总
综合整理，尤其感谢下面两个链接：
http://czmmiao.iteye.com/blog/1483190
http://blog.163.com/donfang_jianping/blog/static/136473951201322303541549/
dbms_stats
DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
常用语句：
exec dbms_stats.gather_schema_stats(ownname => 'tb',degree => 2);
exec dbms_stats.gather_table_stats(ownname => 'tb',tabname => 'obj3' ,degree => 2,cascade => true);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
degree:决定并行度.默认值为null.
使用method_opt创建直方图：
method_opt控制如何创建列上的统计信息。默认值为“for all columns size auto“，表示允许Oracle来选择需要收集直方图的列，并为直方图设置合适的桶大小。
Oracle根据数据的基数和列是否在SQL缓存的执行计划中出现（即是否在过虑条件中其他谓语中出现）来创建直方图。这种方法可能在大多数情况下运行很好，但是存在下面的一些问题：
除非所有的SQL都在缓存区内，否则会缺少直方图。Oracle在创建一个直方图前，在列中的数据的分布的理解是不完整的，这可能会导致一个不好的结果，也就是说Oracle在直方图存在的情况，才意识到需要一个直言图。
在这些情况下，我们需要手动进行直方图的创建，
exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for all indexed columns size auto')
它使用了索引，并达到了直方图预期的目标。
关于method_opt语法的说明：
for all columns:10g默认值(根据版本的不同，默认值也会有所差异)，统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns
- SIZE
FOR [ALL {INDEXED|HIDDEN}] COLUMNS [ column_expression] [size_clause] [,[ column_expression] [size_clause] ]
column_expresson通常是一个列名。size_claues按照这个语法：SIZE [bucket_size | repeat | auto | skewonly]，
bucket_size：字义直方图桶的数量。repeat：只更新已存在的直方图。auto：确定是否创建珍上直方图并根据倾斜的指示设置桶的大小，缓存中的SQL可能会受益于该直方图。skewonly：只有存在的列是倾斜的情况下才创建直方图，与auto的区别就是不考虑缓存中的SQL。
granularity:设置分区表收集统计信息的粒度，分别有
all：对表达全局，分区，子分区的数据都做分析
auto：Oracle根据分区的类型，自动决定做哪一种粒度的分析
global：只做全局级别的分析
global and partition：只对全局和分区级别做分析，对子分区不做分析，这是和all的一个区别
partition：只做分区级别做分析
subpartition：只做子分区做分析
对分区表分析示例：
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T3', GRANULARITY => 'SUBPARTITION', CASCADE => TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T2', GRANULARITY => 'PARTITION', CASCADE => TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T1', GRANULARITY => 'GLOBAL', CASCADE => TRUE);
其中，T1为全表，T2为分区，T3为子分区
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表；statid如果多个表的统计信息存储在同一个stattab中用于进行区分；statown存储统计信息表的拥有者。以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.
dbms_stats的使用
dbms_stats包除了gather_table_stats过程外还有如下过程
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_STATS：分析系统信息
GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息
GATHER_SCHEMA_STATS：分析方案信息
DELETE_COLUMN_STATS：删除字段的统计信息
DELETE_SYSTEM_STATS：删除系统的统计信息
DELETE_INDEX_STATS：删除索引的统计信息
DELETE_TABLE_STATS：删除表的统计信息
DELETE_DATABASE_STATS：删除数据库的统计信息
DELETE_DICTIONARY_STATS：删除数据字典的统计信息
DELETE_SCHEMA_STATS：删除用户方案的统计信息
DELETE_FIXED_OBJECTS_STATS：删除固定对象的统计信息
EXPORT_COLUMN_STATS：导出列的分析信息
EXPORT_INDEX_STATS：导出索引分析信息
EXPORT_SYSTEM_STATS：导出系统分析信息
EXPORT_TABLE_STATS：导出表分析信息
EXPORT_SCHEMA_STATS：导出方案分析信息
EXPORT_DATABASE_STATS：导出数据库分析信息
IMPORT_COLUMN_STATS：导入列分析信息
IMPORT_INDEX_STATS：导入索引分析信息
IMPORT_SYSTEM_STATS：导入系统分析信息
IMPORT_TABLE_STATS：导入表分析信息
IMPORT_SCHEMA_STATS：导入方案分析信息
IMPORT_DATABASE_STATS：导入数据库分析信息
GET_COLUMN_STATS：获取字段的统计信息
GET_SYSTEM_STATS：获取系统的统计信息
GET_INDEX_STATS：获取索引的统计信息
GET_TABLE_STATS：获取表的统计信息
SET_COLUMN_STATS：设置字段的统计信息。通常应用在测试环境，也不排除在极端情况下起到奇效。
SET_SYSTEM_STATS：设置系统的统计信息
SET_INDEX_STATS：设置索引的统计信息
SET_TABLE_STATS：设置表的统计信息
CREATE_STAT_TABLE：建立存放统计信息的表
DROP_STAT_TABLE：删除存放统计信息的表
LOCK_TABLE_STATS：锁定表的统计信息。当觉得当前统计信息非常好，且表数据几乎不变化时，可以考虑锁定统计信息，锁定之后相关的所有数据分析，包括表级，列级，直方图、索引的分析都将被锁定，不允许被更新。
LOCK_SCHEMA_STATS：锁定用户方案的统计信息
UNLOCK_TABLE_STATS：解锁表的统计信息
UNLOCK_SCHEMA_STATS：解锁用户方案的统计信息
RESTORE_SYSTEM_STATS：还原系统的统计信息
RESTORE_INDEX_STATS：还原索引的统计信息
RESTORE_TABLE_STATS：还原表的统计信息
RESTORE_DATABASE_STATS：还原数据库的统计信息
RESTORE_DICTIONARY_STATS：还原数据字典的统计信息
RESTORE_SCHEMA_STATS：还原用户方案的统计信息
RESTORE_FIXED_OBJECTS_STATS：还原固定对象的统计信息
统计信息还原过程如下
通过dbms_stats.get_stats_history_availability查找分析数据恢复到最早时间点，只有在这个时间点之后的分析数据才可以被恢复。
SQL> select dbms_stats.get_stats_history_availability from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
12-MAR-12 10.58.17.552941000 AM +08:00
查看最后一次分析表T的时间
SQL> select last_analyzed from user_tables where table_name='T';
LAST_ANALYZED
------------------
12-APR-12
恢复表T的统计信息
SQL> exec dbms_stats.restore_table_stats('HR','T','11-APR-12 10.58.17.552941000 AM +08:00');
再次查看最后一次分析表T的时间，恢复成功
并不能恢复到指定的时间，只能在指定的时间之前的某个点上；
查看当前的时间格式；
select systimestamp from dual;
exec dbms_stats.restore_table_stats(ownname => 'TB',tabname => 't1',as_of_timestamp => '10-3月 -16 11.02.32.705660 上午 +08:00');
我们在收集统计信息时，有可能由于统计信息收集错误，而额导致性能下降，这时我们就要保存之前收集的统计信息来快速恢复统计信息。下面就通过具体案例来贯穿dbms_stats的使用
1、首先创建一个分析表，该表是用来保存之前的分析值：
SQL> exec dbms_stats.create_stat_table('HR',stattab=>'STAT_TABLE');
2、收集表的统计信息:
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'FOR ALL INDEXED COLUMNS',DEGREE=>4,CASCADE=>TRUE);
3、导出表分析信息到stat_table中
SQL> select count(*) from stat_table;
COUNT(*)
----------
0
SQL> exec dbms_stats.export_table_stats(ownname=>'HR',TABNAME=>'T',STATTAB=>'STAT_TABLE');
PL/SQL procedure successfully completed.
SQL> select count(*) from stat_table;
COUNT(*)
----------
4
4、删除分析信息
SQL> exec dbms_stats.delete_table_stats(ownname=>'HR',TABNAME=>'T');
SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner = 'TEST' AND table_name = 'T1';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
没有查到分析数据
5、导入统计信息
SQL> exec dbms_stats.import_table_stats(ownNAME=>'HR',TABNAME=>'T',STATTAB=>'STAT_TABLE');
SQL> select num_rows,blocks,empty_blocks,avg_space,chain_cnt from user_tables where table_name='T';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT
---------- ---------- ------------ ---------- ----------
10104 20 0 0 0
可以查到分析数据
统计信息记录了表、分区和索引中的数据列和数据分布。
统计信息表
dba_tab_statistics
dba_ind_statistics
dba_tab_col_statistics
表的统计信息历史表
dba_tab_stats_history
select * from dba_ind_statistics i where i.OWNER='TB';
select * from dba_tab_statistics s where s.TABLE_NAME='OBJ3';
SELECT * FROM DBA_TAB_COL_STATISTICS s where s.owner='TB';
select * from dba_tab_stats_history s where s.TABLE_NAME='OBJ3';
相关关键字段解释如下：
avg_cache_hit_ratio：适用于表、索引、分区，表示对象的平均缓存命中率，在检索时，对象的块在缓存区高速缓存中被发现的慨率。
avg_cached_block：适用于表，索引，分区，表示缓冲区高速缓存中出现对象块的平均数量。
avg_col_len：表示列的平均长度（单位为字节数）。
avg_data_blocks_per_key：适用于索引，表示每个索引键对应数据块的平均数量。
avg_leaf_blocks_per_key：表示每个索引键对应叶子块的平均数量。
avg_row_len：适用于表、分区，表示平均记录的长度，包含开销。
avg_space：适用于表、分区、索引，表示对象可用的平均空闲空间。
blevel：表示B*树索引的层级数。
blocks：对象已使用块的数量。
clustering_factor：表示对于键值对应记录在表中排序程度的一种衡量方法。如果键值都打包在一起的，那么聚簇因子将会比较低，如果聚簇键是随机分布的，聚簇因子将会比较高。它的最大值为表中的记录数，最小值为数据块的个数。
density：表示列的密度。这是一个判断列选择性的衡量指标，如果值为0，表示非常有选择性，如果为1，表示没有选择怀，如果它没有直方图，这个值等于1/不同值的数量，如果含有直方图，那么将会根据直方图的计算方式来计算。
enpty_blocks：适用于表、分区，表示对象中空块的数量。
high_value：表示列中的最大值。
leaf_blocks：表示索引中叶子块的数量。
low_value：列中的最小值。
num_distinct：列中不同值的数量。
num_nulls：列中空值的数量。
num_rows：表示表、分区、或索引中包含记录数。
系统的统计信息可以使用dbms_stats.gather_system_stats进行收集。可以在sys.aux_stats$视图中进行系统统计信息的查看，主要包括如下信息：
非工作负载统计：
CPUSPEEDNW：系统上每个CPU每秒钟可以执行的标准操作的次数。后缀NW表示非工作负载下的统计。
IOSEEKTIM：在磁盘上定位数据的平均时间，虽然被称为寻找道（seek）时间，但实际上是从磁盘检索一个数据块的总时间。包含磁盘的旋转延时，传输与寻道时间。
IOTFRSPEED：磁盘的传输速率（单位：b/ms）。
工作负载统计：
SREDTIM：执行单块读取的平均时间。
MREADTIM：执行多块读取的平均时间。
CPUSPEED：系统上每个CPU每秒钟可以执行的标准操作的次数
修改收集统计信息的部分参数
exec dbms_stats.set_global_prefs(pname=>'degree',pvalue=>'2');
exec dbms_stats.set_global_prefs(pname=>'method_opt',pvalue=>'for all columns size auto');
设置自动统计的状态开关
对于对象统计信息的收集可以自动收集也可以手动收集。
自动收集，我们可以通过dbms_auto_task_admin包来禁止用或启动，如下：
begin
dbms_auto_task_admin.disable(
client_name=>'auto optimizer stats collection',
operation=>null,
window_name=>null);
end;
/
begin
dbms_auto_task_admin.enable(
client_name=>'auto optimizer stats collection',
operation=>null,
window_name=>null);
end;
/
说明：使用disable进行禁用，用enable进行启用。
查看当前自动统计任务状态；
select * from dba_autotask_client ;
10g和11g的自动统计分析任务依然是调用的dbms_stats.gather_database_stats_job_proc这个内部的存储过程。
在oracle 11g后自动统计分析的任务每次运行时都会生成一个ORA$AT_OS_OPT_XXX的作业，可通过查看dba_scheduler_job_run_details视图来查看执行状态。
只有对应的task正在执行时，dba_autktask_client_job视图中才有数据：
查看autotask对应的window_name的开启状态。
SQL> select * from dba_autotask_window_clients;
总结一下统计信息自动收集任务运行的步骤：
首先是dba_autotask_task-->dba_autotask_client建立自动执行任务
再根据时间窗口及资源组建立自动执行作业dba_autotask_client-->dba_scheduler_window_groups-->dba_scheduler_windows（查看自动统计信息任务对应的window_groups、window_name信息）
-->dba_autotask_job_history-->dba_scheduler_job_run_details(查看自动统计信息任务生成job的历史运行状态）
-->dba_scheduler_programs （查看自动统计信息任务对应的program_name,program_action）
当一张表没有统计信息时，比较大表，或当预估出SQL消耗的时间很长，这个时候， 我们可以考虑使用动态采样。动态采样通过参数optimizer_dynamic_sampling来设置，也可以在SQL使用dynamic_samping提示来进行，它的值从0到10中的一个数，如果为0，表示不进行动态采样，如果设置为10，表示当SQL涉及到的表的统计信息丢失或不完整，或SQL在两个或两个以上的列中有过虑条件时，系统将读取表的所胡块。此参数的默认值为2，它表示指示优化器对没有分析的表进行少量的统计信息收集。
22:11:46 SQL> show parameter optimizer_dynamic_sampling
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
ANALYZE
analyze语法如下
ANALYZE
{ TABLE [ schema.]table
[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]
| INDEX [ schema. ]index
[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]
| CLUSTER [ schema. ]cluster
}
{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]
| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]
| validation_clauses
| LIST CHAINED ROWS [ into_clause ]
| DELETE [ SYSTEM ] STATISTICS
} ;
PARTITION | SUBPARTITION：对分区表或索引进行分析
CLUSTER cluster:对簇进行分析，分析的结果会放在ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS.
语法：COMPUTE [ SYSTEM ] STATISTICS [for_clause]
对分析对像进行精确的统计，然后把信息存储的数据字典中。可以选择对表或对字段进行分析。
computed和estimated这两种方式的统计数据都被优化器用来影响sql的执行计划
如果指定system选项就只统计系统产生的信息
for_clause
FOR TABLE：只统计表
FOR COLUMNS：只统计某个字段
FOR ALL COLUMNS：统计所有字段
FOR ALL INDEXED COLUMNS：统计索引的所有字段，如
analyze table t compute statistics for table for all indexed columns size 25; #size为直方图的桶数
estimate_statistics_clause
ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]
只是对部分行做一个大概的统计。适用于大表
SAMPLE：指定具体统计多少行，如果忽略这个参数的话，oracle会默认为1064行
ROWS causes：行数 Oracle to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1.
PERCENT causes：百分数，如
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
validation_clauses
分析REF(游标,动态关联结果集的临时对象)或是对像的结构，如
ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE customers VALIDATE REF UPDATE;
analyze的限制
不可以分析数据字典表:Oracle 9i中不可以分析外部表，但可以用DBMS_STATS来实现这个目的
不可以分析临时表
不可以计算或估计下列字段类型
REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.
统计信息相关的视图
对索引进行分析后，分析的结果默认会放在USER_INDEXES, ALL_INDEXES,或 DBA_INDEXES中
分析的内容：
Depth of the index from its root block to its leaf blocks (BLEVEL)
Number of leaf blocks (LEAF_BLOCKS)
Number of distinct index values (DISTINCT_KEYS)
Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY)
Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)
Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR)
对表进行分析后，分析的结果默认会放在USER_TABLES, ALL_TABLES, and DBA_TABLES表中，在分析表的时候，oracle也会分析基于函数的index所引用的表达式
分析的内容：
Number of rows (NUM_ROWS) *
Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to
receive data, regardless whether they currently contain data or are empty) (BLOCKS)
* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) Average available free
space in each data block in bytes (AVG_SPACE)
Number of chained rows (CHAIN_COUNT) Average row length, including the row's overhead, in bytes (AVG_ROW_LEN)
USER_TAB_COL_STATISTICS：用于存储与列相关的统计信息。
USER_HISTOGRAMS :用于存储与直方图相关的统计信息。
dbms_stats和analyze的使用场景
自dbms_stats推出后，Oracle就强烈建议在收集CBO统计信息时用dbms_stats替代analyze，原因如下：
1、dbms_stats可以并行分析,可以并行进行，对多个用户，多个Table
2、dbms_stats有自动分析的功能(alter table monitor )
3、analyze 分析分区表时统计信息不准确
4、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
关于第3点原因是，dbms_stats会实在的去分析表全局统计信息（当指定参数）；而analyze是将表分区（局部）的statistics 汇总计算成表全局statistics ,可能导致误差。
如果想分析整个用户或数据库，还可以采用工具包，可以并行分析
Dbms_utility(8i以前的工具包)
Dbms_stats(8i以后提供的工具包)，如
dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
既然dbms_stats相对于analyze有如此之多的优势，是否可以完全废弃analyze命令呢？答案是否定的，现在关于analyze的定位Oracle解释：
Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer,for example:
1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3、Identify migrated and chained rows of a table or cluster.
可以看到analyze已经不是用来收集与CBO相关的统计信息了，而侧重于对象结构的分析。故通常我们会这样使用analyze:
1、通过Validate Structure来分析对象的结构信息, dbms_stats还不能分析对象结构
2、收集CHAINED ROWS, 收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。可以通过analyze….list chained rows收集块中行链接的信息到chained_rows表中。
注：必须先在执行analyze语句所在的schema内执行$ORACLE_HOME/rdbms/admin/utlchain.sql(或utlchn1.sql)脚本建立chained_rows表。在chained_rows建立之后﹐才能收集行链接信息
3、Analyze table compute statistics 收集表上的 empty_blocks、avg_space 空间使用信息
4、Analyze Cluster 收集簇的信息，其实cluster上唯一可统计的信息是DBA_CLUSTERS.AVG_BLOCKS_PER_KEY(Number of blocks in the table divided by number of cluster keys) ，
三个注意点
1、当某个索引处于monitoring usage的时候，如果使用dbms_stats去分析表并且同时分析索引，oracle会调用gather_index_stat来分析索引，需要用到索引名，故会将该索引的v$object_usage.USED设置为TRUE。analyze 虽然分析了索引，但是其实只需要obj#，不会将索引状态设置为USE = TRUE
2、dbms_stats无法分析cluster表，分析cluster表仍然需要analyze
3、如果无法执行analyze命令，请验证是否创建了存放验证信息的表，创建命令如下:@?/rdbms/admin/utlvalid.sql