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可以利用表修改监控技术来判断需统计分析的表进行,节省了用户的分析资源。



备注:

       

大数据数据库的技术对垒:MapReduce vs. MPP[作者:李明]

大数据数据库的技术对垒:MapReduce vs. MPP --作者:李明(email: mli@pivotal...
  • bigdatacommunity
  • bigdatacommunity
  • 2015年11月16日 18:00
  • 3877

提升大数据数据分析性能的方法及技术(一)

http://www.cnblogs.com/csbdong/p/5719484.html 关于此文   最近在忙着准备校招的相关复习,所以也整理了一下上http://ww...
  • zdy0_2004
  • zdy0_2004
  • 2016年07月31日 01:59
  • 3564

一文读懂大数据:Hadoop,大数据技术、案例及相关应用

你想了解大数据,却对生涩的术语毫不知情?你想了解大数据的市场和应用,却又没有好的案例和解说?别担心,这本来自Wikibon社区的小书想要帮你。 是的,这是一本小书而不是一篇文章,因为它详实细致的让你...
  • doupeihua
  • doupeihua
  • 2016年08月03日 20:08
  • 2101

Excel在统计分析中的应用—第三章—数据库统计函数与数据透视表-Part1-(数据查询与筛选、分类汇总)

这章内容有点奇怪,明明是一张worksheet,为啥一定要说成数据库呢。 不过,在这里学到了新技能:分类汇总,进而得到了启发,也不错。 数据——》表单,这个按钮在Excel2016中已经不属于常用...
  • hpdlzu80100
  • hpdlzu80100
  • 2017年08月04日 00:53
  • 176

【Oracle数据库技术支持】RAC性能分析 - gc buffer busy acquire 等待事件

本文转自微信号“甲骨文中国”: http://mp.weixin.qq.com/s?__biz=MjM5MzA5OTEyMA==&mid=205906107&idx=2&sn=bfd350221fb...
  • msdnchina
  • msdnchina
  • 2015年05月07日 18:11
  • 2564

Oracle数据库数据对象分析--入门必读

Oracle数据库数据对象中最基本的是表和视图,其他还有约束、序列、函数、存储过程、包、触发器等。对数据库的操作可以基本归结为对数据对象的操作,理解和掌握Oracle数据库对象是学习Oracle的捷径...
  • thesumous
  • thesumous
  • 2012年09月01日 19:30
  • 562

Oracle数据库对象操作

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

oracle数据库的权限系统分为系统权限与对象权限

  • 2014年04月03日 10:51
  • 16KB
  • 下载

Oracle数据库对象

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

oracle数据库对象

  • 2014年05月10日 13:17
  • 117KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE数据库对象统计分析技术应用
举报原因:
原因补充:

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