ORACLE数据库对象统计分析技术应用

数据库对象统计分析技术应用

 

ORACLE在执行SQL时如果使用成本方式分析则所有的成本分析信息来源依靠于系统的统计分析表（DBA_TABLES、DBA_INDEXES、DBA_TAB_COLUMNS）数据，如果说统计分析的数据是不准确的，那可能会使ORACLE分析出来的路径执行性能极差，所以统计分析数据是影响ORACLE性能极重要的信息。

 

统计分析主要包括产生表及索引的统计信息

表的统计信息主要包括表的行数，每行的平均长度（字节），空闲块，统计时间等信息

索引的统计信息主要包括行数、层数、叶块数、统计时间等信息。

另外ORACLE还可以统计列及数据不对称信息，9i还可以统计系统信息（CPU，I/O）

 

ORACLE执行成本分析时首先取出所应用表及索引的统计数据进行分析，其中数据行数是一个重要的参数，因为ORACLE在分析表大小时行数为主要参数，如果进行两个表联合时，ORACLE会通过分析表的大小，决定应用小表进行全表查询，而大表执行联合查询，这种性能明显高于先大表进行全表扫描。索引的统计信息对分析也产生比较大的影响，如ORACLE通过统计可以分析产生多个索引的优先级及索引的实用性来确定最优的索引策略。ORACLE还可以统计列及数据对称信息以产生更精确的分析。如一个表有A字段的索引，其中A共有两种值1和0，共10000条记录，为0的记录有10条，为1的记录有9990条，这时如果没有进行列数据不对称的统计信息，那么ORACLE对A=0及A=1条件查询都会进行索引，但实际应用对A=0的索引性能得到了很大的提高，而A=1的索引反而使性能下降。所以说索引特征值分析信息对应用索引产生重大影响，精确的信息使ORACLE不会使用不应该用的索引。

 

实际分析

zl_cbqc和zl_yhjbqk都没有建立统计信息，执行如下两个SQL ORACLE将产生不同的执行计划。

1   select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                             

 NESTED LOOPS                            

  TABLE ACCESS FULL    DLYX     ZL_YHJBQK              

  TABLE ACCESS BY INDEX ROWID      DLYX     ZL_CBQC                   

   INDEX UNIQUE SCAN  DLYX     抄表区册主键                    

 

2  select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                             

 NESTED LOOPS                            

  TABLE ACCESS FULL    DLYX     ZL_CBQC                  

  TABLE ACCESS BY INDEX ROWID      DLYX     ZL_YHJBQK                

   INDEX RANGE SCAN    DLYX     区册索引                    

 

 

在对两个表进行了统计分析后

3         select * from dlyx.zl_yhjbqk a,dlyx.zl_cbqc b where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                159  72853     9689449

 HASH JOIN                159  72853     9689449

  TABLE ACCESS FULL      DLYX     ZL_CBQC      1     426  19596

  TABLE ACCESS FULL      DLYX     ZL_YHJBQK   157  72853     6338211

 

4  select * from dlyx.zl_cbqc b,dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

执行计划：

SELECT STATEMENT, GOAL = CHOOSE                159  72853     9689449

 HASH JOIN                159  72853     9689449

  TABLE ACCESS FULL      DLYX     ZL_CBQC      1     426  19596

  TABLE ACCESS FULL      DLYX     ZL_YHJBQK   157  72853     6338211

 

从以上测试可以明显看出ORACLE的分析结果

第1条SQL与第2条SQL在没有统计分析的信息下进行，ORACLE无法进行判断，只能以规则方法进行分析，根据表的出现先后顺序有明显关系。

第3条SQL与第4条SQL在有统计分析的信息下进行，ORACLE分析与表的出现先后顺序无关，因为它已经知道了表的数据量并且已经确定返回的数据量基本上是两个表所有的数据，所以对表两个表进行了HASH JOIN （同时取出两个表的数据然后在内存中进行联合产生返回结果）。

 

相关技术

用analyze语句产生分析数据

分析表：analyze table zl_yhjbqk estimate statistics sample 20 percent

分析索引：analyze index用户资料表主键compute statistics

分析列：analyze table zl_yhjbqk compute statistics for columns hbs_bh

分析索引列：analyze table zl_yhjbqk compute statistics for all indexed columns

用sys.dbms_utility包分析数据

       分析数据库（包括所有的用户对象和系统对象）：analyze_database

       分析用户所有的对象（包括用户方案内的表、索引、簇）：analyze_schema

用sys.dbms_stats包处理分析数据

       分析数据库（包括所有的用户对象和系统对象）：gather_database_stats

       分析用户所有的对象（包括表、索引、簇）：gather_schema_stats

分析表：gather_table_stats

分析索引：gather_index_stats

删除数据库统计信息：delete_database_stats

删除用户方案统计信息：delete_schema_stats

删除表统计信息：delete_table_stats

删除索引统计信息：delete_index_stats

删除列统计信息：delete_column_stats

设置表统计信息：set_table_stats

设置索引统计信息：set_index_stats

设置列统计信息：set_column_stats

 

ORACLE推荐用户采用sys.dbms_stats包体进行分析，因为在ORACLE9i及其以上的版本全面扩充的此包体的功能。sys.dbms_utility包体进行分析时会对所有的信息全部分析一遍，时间比较长，而在9i中sys.dbms_stats可以利用表修改监控技术来判断需统计分析的表进行，节省了用户的分析资源。

 

备注：

       ORACLE在得到了表的统计分析数据后才会进行成本分析，否则采用规则分析。

       并不是所有的统计数据都会对ORACLE分析产生影响，有些统计数据ORACLE并不处理，只是提供给用户一个参考信息，也可能在ORACLE以后的版本中利用这些信息进行分析。

       分析表的登录用户必须对表拥有访问权限或具有DBA或Analyze any权限

本文是我刚学ORACLE数据库时写，可能有些错误的地方，希望网友能给与指正！

叶正盛
2003-08-15

原文转自：http://www.ltesting.net