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

原创 2007年09月13日 20:40:00
                                                             数据库对象统计分析技术应用(转)

  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的捷径...

Oracle数据库对象

  • 2012年10月16日 11:04
  • 141KB
  • 下载

Oracle数据库数据高速缓存技术

     使用过Oracle数据库的人都知道,Oracle数据库的运行速度与效率,在同类数据库中是名列前茅的,特别是对大量数据进行访问时,更加有出色的表现。那么,Oracle数据库是靠什么实现的呢?笔...

oracle数据库对象

  • 2014年05月10日 13:17
  • 117KB
  • 下载

Oracle数据库对象操作

  • 2011年11月12日 13:55
  • 914KB
  • 下载

使用Hibernate映射技术将Mysql数据复制到Oracle数据库

第一步、建立实体类 package joyo.entity; import java.io.Serializable; public class Category impleme...

Oracle数据库应用与实践

  • 2017年11月03日 14:01
  • 93.85MB
  • 下载

浅谈Oracle数据库12c PDB技术

PDB大致功能描述:   创建:   用create pluggable database的命令,   用file_name_convert的方式拷贝seed pdb的文件到对应的新pdb的路径...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE数据库对象统计分析技术应用
举报原因:
原因补充:

(最多只允许输入30个字)