在Oracle中，如何得到真实的执行计划？

whitek

　　Oracle查看执行计划的几种方法：http://blog.itpub.net/26736162/viewspace-2136865/
一、  如何得到真实的执行计划？　　在Oracle数据库中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行过，真正执行过的SQL所对应的执行计划就是准确的，反之则有可能不准，因此，通过10046事件及如下的几种方式得到的执行计划是最准确的，而从其它方式获取到的执行计划都有可能不准确。
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS'));
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER, 'ADVANCED ALLSTATS'));
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));
　　这里需要注意的是，虽然SQL*Plus的AUTOTRACE功能有部分是真实执行了SQL语句的（例如所有DML语句），但是，由于该命令所显示的执行计划来源于调用EXPLAIN PLAN命令，所以，其得到的执行计划依然可能不准确（特别是在使用了绑定变量的情况下）。那么，为什么EXPLAIN PLAN命令里显示的预估执行计划与该SQL真实的执行计划不一样呢？原因有多个方面，常见的情况包括以下几个方面：
　　①　绑定变量窥视（Bind Peeking）：EXPLAIN PLAN里不会进行绑定变量窥视，但是Runtime Plan里会进行绑定变量窥视，所以，如果发生这种情况，那么会使这两个执行计划产生差异。
　　②　隐式转换：Explain Plan里不会考虑绑定变量的类型，但是Runtime Plan里会考虑类型，从而有可能会根据绑定变量的类型出现隐式转换，所以谓词（Predicate）会发生变化，使得执行计划也会产生差异。
　　③　优化器参数：执行Explain Plan的Session与Runtime Plan的Session不是同一个。如果各个Session之间存在优化器参数差异，那么执行计划也会产生差异。
　　④　统计信息收集参数：Explain Plan始终是用最新的统计信息产生执行计划，但是，Runtime Plan不一定会用最新的统计信息。因此也会产生执行计划差异。在收集统计信息时，一个与缓存的游标是否失效的很重要的参数为NO_INVALIDATE。在重新收集统计信息时，可以指定NO_INVALIDATE选项。该选项有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE这3个值。如果取值为TRUE，那么表示收集统计信息后不进行游标失效动作，原有的Shared Cursor保持原有状态。如果取值为FALSE，那么表示将统计信息对象相关的所有Cursor全部失效，目标SQL语句在下次执行时就会使用硬解析。如果设置为AUTO_INVALIDATE，那么Oracle自己决定Shared Cursor失效动作，当SQL再次执行时间距离上次收集统计信息的时间超过5小时（隐含参数“_OPTIMIZER_INVALIDATION_PERIOD”决定）则对SQL重新做硬解析。AUTO_INVALIDATE为默认选项。有些DBA在收集统计信息时，没有使用NO_INVALIDATE=>FALSE选项，所以，即使收集了统计信息，执行计划也不会立即改变。可以在表级别设置让所有依赖于该表的游标不失效，设置方法为：
　　EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集SH.SALES表上的统计信息时，让所有依赖于该表的游标不失效
　　实验一：
　　CREATE TABLE TEST_EXPLAIN_LHR AS SELECT * FROM DBA_OBJECTS;
　　INSERT INTO TEST_EXPLAIN_LHR SELECT * FROM TEST_EXPLAIN_LHR;
　　COMMIT;
　　SELECT COUNT(*) FROM TEST_EXPLAIN_LHR;

　　CREATE INDEX>　　EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'test_explain_lhr',ESTIMATE_PERCENT => 100,CASCADE => TRUE);
　　VAR X NUMBER;
　　VAR Y NUMBER;
　　EXEC :X := 0;
　　EXEC :Y := 100000;
　　EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
　　SET AUTOT ON
　　SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;
　　SET AUTOT OFF
　　SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;
　　SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));
　　下面实验验证了使用EXPLAIN PLAN FOR和SET AUTOT ON方式获取到的执行计划都是不准确的：
　　SYS@PROD1> clear scr
　　SYS@PROD1> CREATE TABLE test_explain_lhr AS SELECT * FROM Dba_Objects;
　　Table created.
　　SYS@PROD1> INSERT INTO test_explain_lhr SELECT * FROM test_explain_lhr;
　　72503 rows created.
　　SYS@PROD1> COMMIT;
　　Commit complete.
　　SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr;
　　COUNT(*)
　　----------
　　145006

　　SYS@PROD1> CREATE INDEX>　　Index created.
　　SYS@PROD1> EXEC dbms_stats.gather_table_stats(USER,'test_explain_lhr',estimate_percent => 100,cascade => TRUE);
　　PL/SQL procedure successfully completed.
　　SYS@PROD1> VAR x NUMBER;
　　SYS@PROD1> VAR y NUMBER;
　　SYS@PROD1> EXEC :x := 0;
　　PL/SQL procedure successfully completed.
　　SYS@PROD1> EXEC :y := 100000;
　　PL/SQL procedure successfully completed.
　　SYS@PROD1> EXPLAIN PLAN FOR SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;
　　Explained.
　　SYS@PROD1> set line 9999
　　SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display);
　　PLAN_TABLE_OUTPUT
　　---------------------------------------------------------------------------------------------
　　Plan hash value: 3299589416
　　----------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT   |             |     1 |     5 |     3   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |
　　|*  2 |   FILTER           |             |       |       |            |          |

　　|*  3 |    INDEX RANGE SCAN|>　　----------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - filter(TO_NUMBER(:X)=TO_NUMBER(:X) AND
　　"T"."OBJECT_ID" set autot on
　　SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;
　　COUNT(*)
　　----------
　　145006
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3299589416
　　----------------------------------------------------------------------------------

　　|>　　----------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT   |             |     1 |     5 |     3   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |
　　|*  2 |   FILTER           |             |       |       |            |          |

　　|*  3 |    INDEX RANGE SCAN|>　　----------------------------------------------------------------------------------

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - filter(TO_NUMBER(:X)=TO_NUMBER(:X) AND
　　"T"."OBJECT_ID"　　424  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　SYS@PROD1> SET AUTOT OFF
　　SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;
　　COUNT(*)
　　----------
　　145006
　　SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'advanced'));
　　PLAN_TABLE_OUTPUT
　　----------------------------------------------------------------------------------------
　　SQL_ID  1r87sg98rdkuf, child number 0
　　-------------------------------------
　　SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x
　　AND :y
　　Plan hash value: 2428225634
　　--------------------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT       |             |       |       |    90 (100)|          |
　　|   1 |  SORT AGGREGATE        |             |     1 |     5 |            |          |
　　|*  2 |   FILTER               |             |       |       |            |          |

　　|*  3 |    INDEX FAST FULL SCAN|>　　--------------------------------------------------------------------------------------

　　Query Block Name / Object Alias (identified by operation>　　-------------------------------------------------------------
　　1 - SEL$1
　　3 - SEL$1 / T@SEL$1
　　Outline Data
　　-------------
　　/*+
　　BEGIN_OUTLINE_DATA
　　IGNORE_OPTIM_EMBEDDED_HINTS
　　OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
　　DB_VERSION('11.2.0.1')
　　ALL_ROWS
　　OUTLINE_LEAF(@"SEL$1")
　　INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("TEST_EXPLAIN_LHR"."OBJECT_ID"))
　　END_OUTLINE_DATA
　　*/
　　Peeked Binds (identified by position):
　　--------------------------------------
　　1 - :X (NUMBER): 0
　　2 - :Y (NUMBER): 100000

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　2 - filter(:X=:X AND "T"."OBJECT_ID"　　-----------------------------------------------------------
　　1 - (#keys=0) COUNT(*)[22]
　　53 rows selected.
　　二、  如何在不执行SQL的情况下获取执行计划？
　　1、“EXPLAIN PLAN FOR SQL”不实际执行SQL语句，生成的计划未必是真实执行的计划。但是，必须要有PLAN_TABLE表，可以执行脚本“@?/rdbms/admin/utlxplan.sql”来创建。
　　2、SQL*Plus的AUTOTRACE功能，命令：SET AUTOTRACE TRACEONLY EXPLAIN。除SET AUTOTRACE TRACEONLY EXPLAIN外其它的AUTOTRACE方式均实际执行SQL。但是，如果该命令后执行的是DML语句，那么该DML语句是确实被Oracle实际执行过的。
　　三、  如何获取SQL历史执行计划？
　　历史执行计划只能从AWR中获取，如果AWR没有记录的话，那么就无法获取历史执行计划了，获取历史执行计划的命令如下所示：
　　SELECT  * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
　　对于历史计划，可以生成SQL报告，命令如下所示：
　　SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(L_DBID => , L_INST_NUM => , L_BID => , L_EID => , L_SQLID => )) ;
　　其中，L_DBID代表数据库的DBID，L_INST_NUM代表数据库的实例号，单机环境为1，RAC环境填写具体的实例号，L_BID为开始的快照号，L_EID为结束的快照号，L_SQLID为要查看SQL的SQL_ID。
　　下面的例子可以直接从AWR中获取SQL_ID为“bsa0wjtftg3uw”的执行计划，可以看到历史有2种执行计划，一个是全表扫描，一个是索引范围扫描：
　　SYS@RAC2LHR1> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID => 'bsa0wjtftg3uw' )) ;
　　SQL_ID bsa0wjtftg3uw
　　--------------------
　　SELECT file# FROM file$ WHERE ts#=:1
　　Plan hash value: 690176192
　　-----------------------------------------------

　　|>　　-----------------------------------------------
　　|   0 | SELECT STATEMENT            |         |
　　|   1 |  TABLE ACCESS BY INDEX ROWID| FILE$   |
　　|   2 |   INDEX RANGE SCAN          | I_FILE2 |
　　-----------------------------------------------
　　Note
　　-----
　　- rule based optimizer used (consider using cbo)
　　SQL_ID bsa0wjtftg3uw
　　--------------------
　　SELECT file# FROM file$ WHERE ts#=:1
　　Plan hash value: 3494626068
　　---------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |       |       |       |     2 (100)|          |
　　|   1 |  TABLE ACCESS FULL| FILE$ |     1 |     6 |     2   (0)| 00:00:01 |
　　---------------------------------------------------------------------------
　　31 rows selected.
　　四、  给出一个执行计划的执行顺序
　　阅读如下的执行计划，给出SQL的执行顺序。
　　-----------------------------------------

　　|>　　-----------------------------------------
　　|   0 | SELECT STATEMENT
　　|   1 |  SORT AGGREGATE
　　|   2 |   VIEW
　　|   3 |    UNION-ALL
　　|*  4 |     FILTER
　　|*  5 |      HASH JOIN
　　|   6 |       TABLE ACCESS FULL
　　|*  7 |       TABLE ACCESS FULL
　　|*  8 |      TABLE ACCESS BY INDEX ROWID
　　|*  9 |       INDEX UNIQUE SCAN
　　|  10 |     NESTED LOOPS
　　|  11 |      INDEX FULL SCAN
　　|  12 |      TABLE ACCESS CLUSTER
　　|* 13 |       INDEX UNIQUE SCAN
　　-------------------------------------------
　　分析：采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID，那么就最先执行，首先，6、7、9、13最右，所以，6，7最先执行做HASH JOIN，为6，7，5。
　　第二，8有子节点，接下来是9，8。
　　第三，HASH的结果和8的结果做FILTER过滤。
　　第四，10这个节点根据原则是11，13，12，10。
　　第五，剩下依次是3，2，1，0。
　　所以，该图的执行顺序是6，7，5，9，8，4，11，13，12，10，3，2，1，0。