ORACLE 执行计划介绍与测试(沈克勤)2005-3-31.目的：本文档的目的是通过介绍常用的HINT来了解ORACLE的优化器的工作原理及执行计划，以期望起到抛砖引玉的作用。
在实际开发中有意识地控制SQL的执行计划，以达到SQL 执行性能的最优以及执行计划稳定。
为了减少枯燥的文档描述，使用了较多的图示。
2.如何查看执行计划首先创建EXPLAIN_PLAN表不同版本的ORACLE，该表结构可能会不同。
请使用的ORACLE中$ORACLE_HOME/rdbms/admin/utlxplan.sql去创建该表。
方法1：使用SQL*PLUS 的SET AUTOTRACE ：SQL>SET AUTOTRACE ON EXPLAIN 执行SQL，且仅显示执行计划SQL>SET AUTOTRACE ON STATISTICS 执行SQL，且仅显示执行统计信息SQL>SET AUTOTRACE ON 执行SQL，且显示执行计划与执行统计信息SQL>SET AUTOTRACE TRACEONLY 仅显示执行计划与统计信息，无执行结果SQL>SET AUTOTRACE OFF 关闭跟踪显示计划与统计方法2：使用PL/SQL Developer工具方法3：使用DBMS_XPLAN.DISPLAY()方法4：直接查看表：EXPLAIN_TABLESELECT lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"FROM plan_tableCONNECT BY prior id = parent_idAND prior statement_id = statement_idSTART WITH id = 0AND statement_id = '&1'ORDER BY id;3.如何控制与改变执行计划我并没有见过单独介绍ORACLE SQL优化器原理方面的资料。
但可以从ORACLE的HINT这个侧面来了解ORACLE的优化器的原理，从而最有效地书写SQL。
方法1：使用ORACLE的HINTORACLE的HINT是用来提示ORACLE的优化器，以期选择用户期望的执行计划。
在许多情况下，ORACLE默认的执行方式并不总是最优的，只不过我们平时大多数所操作的数据量比较小，好的执行计划与差的执行计划所消耗的时间上的差很少，用户感觉不到而已。
但于对书写操作大数据量的SQL而言，其SQL的书写则需要先了解一下执行计划是否最优或满足生产需要。
通常从开发环境迁移到生产环境下时，往往会出现此类情况。
例如：假设有一张客户表，在客户类别上有索引。
如果想查找某一类别用户，而该类别用户占总数的比例高达90%，则此时采用全表扫描方式将会比索引扫描方式快。
如果不使用HINT，ORACLE很可能会选择使用索引方式来执行。
使用HINT可以：1．改变SQL中的表的关联顺序2．改变SQL中的表的关联方式3．使用并行来执行DML、DDL、以及SELECT语句4．改变表的访问路径5．重写SQL6．其他等HINT的书写方式如下：{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */如：select /*+ index(emp)*/ count(*) from emp方法2：使用OUTLINE来改变已有SQL的执行计划在实际项目中，通常在开发环境下一些SQL执行没有任何问题，而到了生产环境或生产环境的数据量发生较大的变量时，其SQL的执行效率会异常的慢。
此时如果更改SQL，则可能需要重新修改源程序以及重新编译程序。
如果觉得修改源程序的成本比较大，则可以使用OUTLINE在不改变原应用程序的情况下更改特定SQL的执行计划。
OUTLINE的原理是将调好的SQL的执行计划(一系列的HINT)存贮起来，然后该执行计划所对应的SQL用目前系统那个效率低下的SQL来替代之。
从而使得系统每次执行该SQL时，都会使用已存贮的执行计划来执行。
因此可以在不改变已有系统SQL的情况下达到改变其执行计划的目的。
OUTLINE方式也是通过存贮HINT的方式来达到执行计划的稳定与改变。
4.常用的HINT介绍(部分)FULL1)功能：用于指定对某个表进行全表扫描。
语法如下：2)使用场景：¾如果查询的结果占全表的数据量的比例比较高（即选择率较高，其经验值为>30%），则选择使用全表扫描的方式会比索引来得快。
因为如果查询结果字段不是索引字段的话，则将进行两次IO：一次执行索引的选择，另一次根据其索引中存贮的ROWID来查询表中的结果字段值。
¾另外一种情况也可能需要使用FULL提示。
如两个表进行关联操作，你希望其中一个表为全表扫描，另一表为索引扫描（如使用NESTED LOOPS方式）.INDEX1)功能：与FULL相对应，用于指定对某个表进行索引扫描。
INDEX提示的可以不需要指定所使用的索引名，如果一个表有多个索引，则系统会选择合适的一个。
语法如下：2)使用场景：¾查询结果占全表的比例较小或只返回几行，则需要使用INDEX提示。
¾如果查询的结果字段存在于所提示的索引中，则使用全索引扫描方式也可能比全表扫描方式快。
因为索引的数据量通常比表的数据量要小，因此其IO的次数也会比扫描表少。
如：(可以看出，下图的执行计划将不进行表的扫描。
)INDEX_COMBINE1)功能该提示用于提示优化器在单个表中使用多于一个的位图索引。
语法如下：2)使用场景：¾在数据仓库项目中，常会创建位图索引来提高查询效率以及减少索引存贮量。
但在OLTP系统中，建议不要创建位图索引，因为在有位图索引的表上大量更新或插入数据的话，则将导致索引容量快速增长，此时需要将索引重建或在更新前置无效。
注：如果割舍不了位图索引的高性能以及低存贮的话，可以参照以下方式来解决其更新的问题：a) A workaround for this problem would be to commit after every update.Committing will enable index logic to reclaim the wasted space created by these deleted records.b)The best workaround is still to rewrite your application to use bulk dml.This may be done by creating a table to store up all the changes youintend to make. This table should enable you to code a bulk dmlstatement that applies all the changes. Since bitmap index dml isspecially optimized for bulk dml this would probably be the bestmechanism to rapidly apply many changes to a bitmap indexed table.c)One workaround is to drop the index, do the updates and then againrecreate the indexd)Last workaround is to set the Bitmap to UNUSED.USE_CONCAT1)功能用于提示优化器使用UNION ALL方式来替代范围查询。
语法如下：2)使用场景：¾如果查询条件字段上有索引、查询条件表达式为IN或OR，且通过索引方式会比全表扫描方式快的话，则可以考虑使用该种提示。
但如果查询结果占全表的比例比较高时，则使用全表扫描反而会更快。
ORDERED1)功能用于提示优化器使用FROM子名中表出现的顺序来关联各表。
语法如下：`2)使用场景：¾如以上三表：设serv为用户表,有100个用户，cdr_gsm为用户语音清单表，有1000万个用户的1亿条清单，cdr_sms为用户短信清单表，也有1000万用户的1亿条清单，如果不使用该提示，则如果ORACLE使用关联顺序为((cdr_gsm,cdr_sms)serv)，则系统将先进行致命的两个清单表的关联操作。
USE_NL1)功能当需要进行多表关联操作时，选择使用嵌套循环方式进行关联操作。
语法如下：2)使用场景：¾相对于使用HASH JOIN或MERGE JOIN，NESTED LOOPS JOIN方式可以获得最快的用户响应。
例如上图所示，优化器的执行过程如下：a)扫描CDR表，取得一条记录b)根据步骤a扫描得到的记录中的prod_id，查找prod中的相同prod值c)将该prod_id关联的结果返回给用户d)继续执行步骤a，直到CDR表结束。
¾另外一种情况也需要使用该种提示：如果关联的两个表，其中在第一个表中的选择结果很小，而第二表个的选择结果很大，且第二个表中在关联字段上有索引，此时使用该种提示将比其他二种JOIN方式更有优势。
见下图：在索引上进行范围扫描USE_MERGE1)功能当需要进行多表关联操作时，选择使用先按关联字段进行排序，然后从相关联的已排序的结果中取得关联结果。
语法如下：2)使用场景：¾如果相关联的表都是一个数量级，且其中一个或多个表在关联字段上有索引，此时使用该提示将可获得的比其他两种JOIN方式更好的性能。
MERGE JOIN的执行过程如下：a)对关联的其中的一个表进行排序操作，其排序方式可以为全表扫描获得关联字段上的ROWID顺序及相关结果字段，也可以通过关联字段上的索引来获得所需ROWID的顺序、并根据此ROWID从表中获得结果字段b)对第二张表进行排序，方法同步骤ac)从步骤a与步骤b的结果中按顺序取得匹配结果即为所关联的结果集。
注：如果相关系的表是同一数量级，且相关联的表在关联字段上没有索引，该种方式下系统将会对所关联的表都进行全表扫描排序，其成本极高。
所以在有的数据库系统中（如INFORMIX IDS7以后的版本）已不使用MERGE JOIN的关联方式。
取而代之的是使用HASH JOIN方式。
USE_HASH1)功能当需要进行多表关联操作时，选择使用全表扫描的方式在其中一个表的关联字段上创建HASH表（该表一般选择较小的表，以便可以存于SGA内存中，并达到提高探测的速度的目的），然后对另一表上进行全表扫描且以同样的算法构建HASH表，同时探测基于第一个表中上HASH表，查找匹配的键值。