OracleSQL语句的执行计划优化的总结_计划执行情况总结
OracleSQL语句的执行计划优化的总结由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“计划执行情况总结”。
通过分析SQL语句的执行计划优化SQL(总结)
第一章、第2章 并不是很重要，是自己的一些想法，关于如何做一个稳定、高效的应用系统的一些想法。
第三章以后都是比较重要的。
附录的内容也是比较重要的。我常用该部分的内容。
前言
本文档主要介绍与SQL调整有关的内容，内容涉及多个方面：SQL语句执行的过程、ORACLE优化器，表之间的关联，如何得到SQL执行计划，如何分析执行计划等内容，从而由浅到深的方式了解SQL优化的过程，使大家逐步步入SQL调整之门，然后你将发现„„。
该文档的不当之处，敬请指出，以便进一步改正。请将其发往我的信箱：xu_yu_jin2000@sina.com。
如果引用本文的内容，请著名出处！
作者：徐玉金
MSN：sunny_xyj@hotmail.com
Email: xu_yu_jin2000@sina.com
日期：2005.12.12
活跃于:www.daodoc.com;
这样在分析时导致查询出的数据过多，不方便，所以用count(a.CHANNEL||B.user_cla)来代替，而且count(a.CHANNEL||B.user_cla)操作本身并不占用过多的时间，所以可以接受此种替代。
利用索引查询出SWD_BILLDETAIL表中所有记录的方法 SQL> select count(id)from SWD_BILLDETAIL;COUNT(ID)----------
53923574 Elapsed: 00:02:166.00 Execution Plan---------------------------0
SELECT STATEMENT Optimizer=CHOOSE(Cost=18051 Card=1)1
0
SORT(AGGREGATE)2
INDEX(FAST FULL SCAN)OF 'SYS_C001851'(UNIQUE)(Cost=18051 Card=54863946)
Statistics---------------------------
0 recursive calls
1952 db block gets
158776 consistent gets
158779 physical reads
1004 redo size
295 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows proceed
利用全表扫描从SWD_BILLDETAIL表中取出全部数据的方法。SQL> select count(user_cla)from swd_billdetail;COUNT(USER_CLASS)-----------------
53923574 Elapsed: 00:11:703.07 Execution Plan---------------------------0
SELECT STATEMENT Optimizer=CHOOSE(Cost=165412 Card=1 Bytes=2)1
0
SORT(AGGREGATE)2
TABLE ACCESS(FULL)OF 'SWD_BILLDETAIL'(Cost=165412 Card=54863946 Bytes=109727892)
Statistics---------------------------
0 recursive calls
8823 db block gets
1431070 consistent gets
1419520 physical reads
0 redo size
303 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows proceed
select count(a.CHANNEL||B.user_cla)from swd_billdetail B, SUPER_USER A where A.cn = B.cn;EXEC_ORDER PLANLINE------------------------
SELECT STATEMENT OPT_MODE:CHOOSE(COST=108968,CARD=1,BYTES=21)
SORT(AGGREGATE)(COST=,CARD=1,BYTES=21)
NESTED LOOPS
(COST=108968,CARD=1213745,BYTES=25488645)
TABLE ACCESS(FULL)OF 'SWORD.SUPER_USER'(COST=2,CARD=2794,BYTES=27940)
TABLE ACCESS(BY INDEX ROWID)OF 'SWORD.SWD_BILLDETAIL'(COST=39,CARD=54863946,BYTES=603503406)
INDEX(RANGE SCAN)OF 'SWORD.IDX_DETAIL_CN'(NON-UNIQUE)(COST=3,CARD=54863946,BYTES=)
这个查询耗费的时间很长，需要1个多小时。运行后的信息如下：
COUNT(A.CHANNEL||B.USER_CLASS)------------------------------
1186387
Elapsed: 01:107:6429.87
Execution Plan---------------------------
0
SELECT STATEMENT Optimizer=CHOOSE(Cost=108968 Card=1 Bytes=21)
0
SORT(AGGREGATE)
NESTED LOOPS(Cost=108968 Card=1213745 Bytes=25488645)
TABLE ACCESS(FULL)OF 'SUPER_USER'(Cost=2 Card=2794Bytes=27940)
TABLE ACCESS(BY INDEX ROWID)OF 'SWD_BILLDETAIL'(Cost=39 Card=54863946 Bytes=603503406)
INDEX(RANGE SCAN)OF 'IDX_DETAIL_CN'(NON-UNIQUE)(Cost=3 Card=54863946)Statistics---------------------------
0 recursive calls db block gets
1196954 consistent gets
1165726 physical reads
0 redo size
316 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows proceed
将语句中加入hints，让oracle的优化器使用嵌套循环，并且大表作为驱动表，生成新的执行计划：
select /*+ ORDERED USE_NL(A)*/ count(a.CHANNEL||B.user_cla)from swd_billdetail B, SUPER_USER A where A.cn = B.cn;
EXEC_ORDER PLANLINE------------------
SELECT STATEMENT OPT_MODE:CHOOSE(COST=109893304,CARD=1,BYTES=21)
SORT(AGGREGATE)(COST=,CARD=1,BYTES=21)
NESTED LOOPS
(COST=109893304,CARD=1213745,BYTES=25488645)
TABLE ACCESS(FULL)OF 'SWORD.SWD_BILLDETAIL'(COST=165412,CARD=54863946,BYTES=603503406)
TABLE ACCESS(BY INDEX ROWID)OF 'SWORD.SUPER_USER'(COST=2,CARD=2794,BYTES=27940)
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)
0
SORT(AGGREGATE)
NESTED LOOPS(Cost=109893304 Card=1213745 Bytes=25488645)
TABLE ACCESS(FULL)OF 'SWD_BILLDETAIL'(Cost=165412 Card=54863946 Bytes=603503406)
TABLE ACCESS(BY INDEX ROWID)OF 'SUPER_USER'(Cost=2Card=2794 Bytes=27940)
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 SQL*Net roundtrips to/from client sorts(memory)
0 sorts(disk)rows proceed
总结：
因为上两个查询都是采用nested loop循环，这时采用哪个表作为driving table就很重要。在第一个sql中，小表(SUPER_USER)作为driving table，符合oracle优化的建议，但是由于SWD_BILLDETAIL表中cn列的值有很多重复的，这样对于SUPER_USER中的每一行，都会在SWD_BILLDETAIL中有很多行，利用索引查询出这些行的rowid很快，但是再利用这些rowid去查询SWD_BILLDETAIL表中的user_cla列的值，就比较慢了。原因是这些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表的索引，查询或许会更快一些，我没有对此进行测试。
所以在进行性能调整时，具体问题要具体分析，没有一个统一的标准。
[center]第6章 其它注意事项[/center]
1.不要认为将optimizer_mode参数设为rule，就认为所有的语句都使用基于规则的优化器
不管optimizer_mode参数如何设置，只要满足下面3个条件，就一定使用CBO。
1)如果使用Index Only Tables(IOTs), 自动使用CBO.2)Oracle 7.3以后，如果表上的Paralle degree option设为>1，则自动使用CBO, 而不管是否用rule hints.3)除rlue以外的任何hints都将导致自动使用CBO来执行语句
总结一下，一个语句在运行时到底使用何种优化器可以从下面的表格中识别出来，从上到下看你的语句到底是否满足description列中描述的条件：
Description
对象是否被分析
优化器的类型
~~~~~~~~~~~
~~~~~~~~~~~~
~~~~~~~~~
Non-RBO Object(Eg:IOT)
n/a
#1
Parallelism > 1
n/a
#1
RULE hint
n/a
RULE
ALL_ROWS hint
n/a
ALL_ROWS
FIRST_ROWS hint
n/a
FIRST_ROWS
*Other Hint
n/a
#1
OPTIMIZER_GOAL=RULE
n/a
RULE
OPTIMIZER_GOAL=ALL_ROWS
n/a
ALL_ROWS
OPTIMIZER_GOAL=FIRST_ROWS
n/a
FIRST_ROWS
OPTIMIZER_GOAL=CHOOSE
NO
RULE
OPTIMIZER_GOAL=CHOOSE
YES
ALL_ROWS
#1 表示除非OPTIMIZER_GOAL 被设置为FIRST_ROWS，否则将使用ALL_ROWS。在PL/SQL中，则一直是使用ALL_ROWS
*Other Hint 表示是指除RULE、ALL_ROWS 和FIRST_ROWS以外的其它提示
2)当CBO选择了一个次优化的执行计划时, 不要同CBO过意不去, 先采取如下措施: a)检查是否在表与索引上又最新的统计数据
b)对所有的数据进行分析，而不是只分析一部分数据
c)检查是否引用的数据字典表，在oracle 10G之前，缺省情况下是不对数据字典表进行分析的。
d)试试RBO优化器，看语句执行的效率如何，有时RBO能比CBO产生的更好的执行计划
e)如果还不行，跟踪该语句的执行，生成trace信息，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。
3)假如利用附录的方法对另一个会话进行trace，则该会话应该为专用连接
4)不要认为绑定变量(bind variables)的缺点只有书写麻烦，而优点多多，实际上使用绑定变量虽然避免了重复parse，但是它导致优化器不能使用数据库中的列统计，从而选择了较差的执行计划。而使用硬编码的SQL则可以使用列统计。当然随着CBO功能的越来越强，这种情况会得到改善。目前就已经实现了在第一次运行绑定变量的sql语句时，考虑列统计。
5)如果一个row source 超过10000行数据，则可以被认为大row source
6)有(+)的表不是driving table，注意：如果有外联接，而且order hint指定的顺序与外联结决定的顺序冲突，则忽略order hint
7)影响CBO选择execution plan的初始化参数:
这些参数会影响cost值 ALWAYS_ANTI_JOIN B_TREE_BITMAP_PLANS COMPLEX_VIEW_MERGING DB_FILE_MULTIBLOCK_READ_COUNT FAST_FULL_SCAN_ENABLED HASH_AREA_SIZE HASH_JOIN_ENABLED HASH_MULTIBLOCK_IO_COUNT OPTIMIZER_FEATURES_ENABLE OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MODE> / GOAL OPTIMIZER_PERCENT_PARALLEL OPTIMIZER_SEARCH_LIMIT PARTITION_VIEW_ENABLED PUSH_JOIN_PREDICATE SORT_AREA_SIZE SORT_DIRECT_WRITES SORT_WRITE_BUFFER_SIZE STAR_TRANSFORMATION_ENABLED V733_PLANS_ENABLED CURSOR_SHARING
