analyze 与 dbms_stats 使用

analyze 与 dbms_stats 使用
一、analyze 使用
1、功能
a)搜集和删除索引、表和簇的统计信息
b)验证表、索引和簇的结构
c)鉴定表和簇和行迁移和行联接
d)针对analyze的搜集和删除统计信息功能而言，oracle推荐使用DBMS_STATS包来搜集优化信息，DBMS_STATS可以并行的搜集信息，可以搜集分区表的全局信息，进一步来说，按成本的优化器只会使用DBMS_STATS包所统计出来的信息。

2、先决条件
必须在你自己的方案中或有ANALYZE ANY system 的权限，

[@more@]

3、语法
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
} ;
INDEX index：对索引进行分析，分析的结果会放在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)

TABLE table：对表进行分析，分析的结果会放在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)
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：统计索引的所有字段
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：百分数
validation_clauses
分析REF或是对像的结构
EG：ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE customers VALIDATE REF UPDATE;

4、分析表的限制
a)不可以分析数据字典表
b)不可以分析扩展表，但可以用DBMS_STATS来实现这个目的
c)不可以分析临时表
d)不可以计算或估计下列字段类型REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.
二、dbms_stats 使用
Dbms_stats是oracle8i新增的程序包，它使统计数据的生成和处理更加方便。
--参数
estimate_percent --估算抽样百分比
method_opt for table --只统计表　for all indexed columns --只统计有索引的表列 for all indexes --只分析统计相关索引

--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;
pl/sql procedure successfully completed

--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;
pl/sql procedure successfully completed

--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'scott',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )

--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'work_list',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
pl/sql procedure successfully completed

--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',estimate_percent => '10',degree => '4') ;
pl/sql procedure successfully completed

--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'scott',tabname => 'work_list') ;
pl/sql procedure successfully completed

--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname => 'scott',tabname => 'work_list',stattab => 'stat_table') ;
pl/sql procedure successfully completed

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname => 'scott',stattab => 'stat_table');
pl/sql procedure successfully completed

--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname => 'crm2',indname => 'IDX_ADM_PERMISSION_PID_MID',stattab => 'stat_table')

--检查是否导入成功
SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name='WORK_LIST';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------ ---------- ---------- -------------
WORK_LIST 4005 186 2007-10-12 15
三、analyze dbms_stats 区别
自从Oracle8.1.5引入dbms_stats包，Experts们便推荐使用dbms_stats取代analyze。 理由如下
1,dbms_stats可以并行分析
2,dbms_stats有自动分析的功能(alter table monitor )
3,analyze 分析统计信息的不准确some times
1,2好理解，且第2点实际上在VLDB中是最吸引人的；3以前比较模糊，看了metalink236935.1 解释,analyze在分析Partition表的时候，有时候会计算出不准确的Global statistics .原因是，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);

总结
1、DBMS_STATS的优点
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
f) 对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句
g) 对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
参考：http://xujt82.spaces.live.com/Blog/cns!1EB2C64171792704!334.entry
http://www.itpub.net/viewthread.php?tid=244353&extra=&page=2
http://www.itpub.net/viewthread.php?tid=845777&extra=&page=1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/670493/viewspace-1025047/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/670493/viewspace-1025047/