Oracle 表分析说明

Oracle 表分析说明

一、    表分析概念

1.  简单的说，就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执行路径。通过对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以优化。

1)     收集统计信息的目的是为了使基于 CBO的执行计划更加准确。

2)     分析的结果数据[all_tables]有什么用，要看Oracle初始化参数 optimal_mode 怎么设置的。

3)     分析更新表的统计信息,有可能导致执行计划改变

2.  用analyze或dbms_stats包做统计表的的分析,分析可以判断表的使用情况,比如行迁移情况、表的资料量情况等,如:

1)     select * from all_tables where table_name='TEMP_TABLE';（表名一定要大写）

如果对该表没有做过统计分析，则查询的大部分栏位值都为空

2)     analyze table tableName compute statistics;

执行时间与表的数据量有关

3)     select * from all_tables where table_name=' TEMP_TABLE ';

之前为空的部分字段，现在查询已经有值

3.  oracle的联机文档描述了analyze的做用：

Use the ANALYZE statement to collect non-optimizer statistics, for example, to: --使用analyze功能可以收集一些没有进行优化的统计信息，例如： Collect or delete statistics about an index or index partition, table or table partition, --聚集或删除索引或索引分区，表或表分区 index-organized table, cluster, or scalar object attribute. --索引表，串，或游标的目标属性等统计信息 Validate the structure of an index or index partition, table or table partition, index-organized table, --增加下列结构的有效性：索引或索引分区，表或表分区，索引表 cluster, or object reference (REF). --串，目标参照 Identify migrated and chained rows of a table or cluster. --鉴别一个表或串中已经被移动的抑或被锁住的列。

 以的analyze table abc compute statistics;这条为例，生成的统计信息会存在于user_tables这个视图，查看一下select * from user_tables where table_name='ABC';
观察一下NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN几列你就会明白，这就是变化。分析完表之后，会发现DBA_tables 视图中，以前很多列值是空的，现在开始有数据了。这些数据就是分析表得到的.

 

命令行方式可以采用analyze命令
如Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
等等

 

4.  dbms_stats包的作用主要是替代analyze的收集统计信息这一块的功能，且在这一方面做了相当大程度上的增强。

5.  带有ESTIMATE选项的ANALYZE操作对于一些表能够产生不正确的结果，尤其是那些取样较小的表。这不是个BUG,而是每个统计取样方法的特性。如果所选取样不能代表整个数据集，你就不能期待产生正确的统计。

6.  统计分析，在Oracle 10G中能够保存列值的分布信息

7. 在考虑表分析时，需要考虑如下问题:

1)    对一个带索引的表的ANALYZE，将分析其相关索引。

2)    如果你对一个表进行ANALYZE ... ESTIMATE分析，那么然后在其相关索引上做ANALYZE COMPUTE分析是很明智的。这样可以确保被索引字段的统计是准确的。

3)    分析索引不用到临时表空间

4)    如果分析一个索引而不分析其基表，在这一单一基础上CBO不会被选中如果你需要使用ESTIMATE-估计（例如，由于时间的限制），建议你在几个不同的取样大小上进行 ANALYZE ... ESTIMATE，来确定每个对象的理想的取样大小。总的目标是找到一个能在最短的时间内产生准确的统计的取样大小。较好的开始点是 10% - 15%

5)    进行超过50%的ANALYZE ... ESTIMATE就会导致/变成ANALYZE ... COMPUTE。

 

如果想分析整个用户或数据库，还可以采用工具包，可以并行分析
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，而不是使用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)、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息.