dbms_stats与analyze分析汇总(原创)

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);
参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的
通过设置 method_opt参数可以智能地生成直方图,具体取值如下:
for all columns:10g默认值(根据版本的不同，默认值也会有所差异)，统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254];
REPEAT上次统计过的histograms;
AUTO由oracle决定N的大小;
SKEWONLY选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。
假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。
degree:决定并行度.默认值为null.
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过程外还有如下过程
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：导入数据库分析信息
GATHER_INDEX_STATS：分析索引信息
GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息
GATHER_SCHEMA_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：设置表的统计信息
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：删除固定对象的统计信息
GATHER_SCHEMA_STATS：分析方案信息
GATHER_DATABASE_STATS：分析数据库信息
GATHER_SYSTEM_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');

 

PL/SQL procedure successfully completed.
再次查看最后一次分析表T的时间，恢复成功

 

SQL> select last_analyzed from user_tables where table_name='T';

 

 

 

LAST_ANALYZED
------------------
11-APR-12

 

我们在收集统计信息时，有可能由于统计信息收集错误，而额导致性能下降，这时我们就要保存之前收集的统计信息来快速恢复统计信息。下面就通过具体案例来贯穿dbms_stats的使用

1、首先创建一个分析表，该表是用来保存之前的分析值：
SQL> exec dbms_stats.create_stat_table('HR',stattab=>'STAT_TABLE');
PL/SQL procedure successfully completed.
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);
PL/SQL procedure successfully completed.
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');
PL/SQL procedure successfully completed.
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');
PL/SQL procedure successfully completed.
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
可以查到分析数据

 

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_statistics_clause
语法：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               

 

参考至：《让Oracle跑得更快》谭怀远著
            http://www.itpub.net/thread-845777-1-1.html

            http://www.cnblogs.com/tracy/archive/2011/07/19/2110508.html
            http://www.cnblogs.com/sopost/archive/2011/02/20/2190045.html

            http://blog.chinaunix.net/uid-7332142-id-249531.html

            http://www.itpub.net/thread-1344276-1-1.html
            https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=237397.1
            https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=237537.1
            http://www.oracledatabase12g.com/archives/what-dbms_stats-can-not-but-analyze-can-do.html
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]