将语句中加入hints,让oracle的优化器使用嵌套循环,并且大表作为驱动表,生成新的执行计划:
select /*+ ORDERED USE_NL(A) */ count(a.CHANNEL||B.user_class) from swd_billdetail B, SUPER_USER A where A.cn = B.cn;
EXEC_ORDER PLANLINE ---------- ----------------------------------------------------------------------------------------------------- 6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=109893304,CARD=1,BYTES=21) 5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21) 4 NESTED LOOPS (COST=109893304,CARD=1213745,BYTES=25488645) 1 TABLE ACCESS (FULL) OF 'SWORD.SWD_BILLDETAIL' (COST=165412,CARD=54863946,BYTES=603503406) 3 TABLE ACCESS (BY INDEX ROWID) OF 'SWORD.SUPER_USER' (COST=2,CARD=2794,BYTES=27940) 2 INDEX (RANGE SCAN) OF 'SWORD.IDX_SUPER_USER_CN' (NON-UNIQUE) (COST=1,CARD=2794,BYTES=) |
这个查询耗费的时间较短,才20分钟,性能比较好。
运行后的信息如下:
COUNT(A.CHANNEL||B.USER_CLASS) ------------------------------ 1186387
Elapsed: 00:20:1208.87
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=109893304 Card=1 Bytes=21) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (Cost=109893304 Card=1213745 Bytes=25488645) 3 2 TABLE ACCESS (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946 Bytes=603503406) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SUPER_USER' (Cost=2Card=2794 Bytes=27940) 5 4 INDEX (RANGE SCAN) OF 'IDX_SUPER_USER_CN' (NON-UNIQUE) (Cost=1 Card=2794)
Statistics ---------------------------------------------------------- 0 recursive calls 8823 db block gets 56650250 consistent gets 1413250 physical reads 0 redo size 316 bytes sent via SQL*Net to client 421 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed |
注意上面2个执行计划对应的statistics部分好像有问题,我正在重测,但因系统出现问题,需要过些日子才能测试完。
总结:
因为上两个查询都是采用nested loop循环,这时采用哪个表作为driving table就很重要。在第一个sql中,小表(SUPER_USER)作为driving table,符合oracle优化的建议,但是由于SWD_BILLDETAIL表中cn列的值有很多重复的,这样对于SUPER_USER中的每一行,都会在SWD_BILLDETAIL中有很多行,利用索引查询出这些行的rowid很快,但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_class列的值,就比较慢了。原因是这些rowid是随机的,而且该表比较大,不可能缓存到内存,所以几乎每次按照rowid查询都需要读物理磁盘,这就是该执行计划比较慢的真正原因。从结果可以得到验证:查询出1186387行,需要利用rowid从SWD_BILLDETAIL表中读取1186387次,而且大部分为从硬盘上读取。
反其道而行之,利用大表(SWD_BILLDETAIL)作为driving表,这样大表只需要做一次全表扫描(而且会使用多块读功能,每次物理I/O都会读取几个oracle数据块,从而一次读取很多行,加快了执行效率),对于读出的每一行,都与SUPER_USER中的行进行匹配,因为SUPER_USER表很小,所以可以全部放到内存中,这样匹配操作就极快,所以该sql执行的时间与SWD_BILLDETAIL表全表扫描的时间差不多(SWD_BILLDETAIL全表用11分钟,而此查询用20分钟)。
另外:如果SWD_BILLDETAIL表中cn列的值唯一,则第一个sql执行计划执行的结果或许也会不错。如果SUPER_USER表也很大,如500万行,则第2个sql执行计划执行的结果反而又可能会差。其实,如果SUPER_USER表很小,则第2个sql语句的执行计划如果不利用SUPER_USER表的索引,查询或许会更快一些,我没有对此进行测试。
所以在进行性能调整时,具体问题要具体分析,没有一个统一的标准。
分享到:
相关推荐
Oracle执行计划参数解释,Oracle SQL优化的基础是看懂Oracle的执行计划,本文当系统整理了Oracle执行计划里面的各种参数。
Oracle执行计划详解,包括oracle执行顺序和索引详细介绍
oracle执行计划详解 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆!
oracle 执行计划 建立与阅读 在进行语句性能分析时,提取语句执行计划,是重要的分析手段。Oracle数据库有多种获得执行计划方式,以下进行简要介绍
什么是执行计划,如何访问数据,执行计划层次关系、例子解说等几个方面,全面介绍oracle执行计划说明,并进一步给出优化建议等相关内容。
教你如何生成、分析Oracle SQL执行计划,让SQL调优更轻松。
oracle执行计划详细解释
Oracle 执行计划介绍与测试 pdf
ORACLE执行计划和SQL调优
自己动手总结出来的所有的东西都有例子,执行计划的分析,访问路径分析,执行顺序分析方法,执行计划的解读方法,刚近公司,带我的人要我做的,也是想帮我好好理解,现在拿来分享下
ORACLE执行计划和SQL调优.pptx
Oracle的执行计划,本文档说明了Oracle的执行计划,非原创,好东西再这里分项下
1. 掌握执行计划 2. 掌握判断SQL优劣的基本能力 3. 在合适的时候用合适的Hints
Oracle执行计划与SQL优化实例.pptx
怎么进行autotrace进行查看执行计划
针对Oracle性能的优化,了解sql语句的执行计划,对性能优化起到很好的指导作用!