【DB笔试面试598】在Oracle中，如何得到真实的执行计划？

AiDBA宝典

发布于 2019-09-29 16:01:53

6660

发布于 2019-09-29 16:01:53

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

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

♣

答案部分

在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 IDX_OBJ_LHR ON  TEST_EXPLAIN_LHR(OBJECT_ID);
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 idx_obj_lhr ON  test_explain_lhr(object_id);

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

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |
|*  2 |   FILTER           |             |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_OBJ_LHR |   363 |  1815 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
   3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND
              "T"."OBJECT_ID"<=TO_NUMBER(:Y))

17 rows selected.

SYS@PROD1> 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

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          |
|*  2 |   FILTER           |             |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_OBJ_LHR |   363 |  1815 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
   3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND
              "T"."OBJECT_ID"<=TO_NUMBER(:Y))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        329  consistent gets
          0  physical reads
          0  redo size
        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

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |    90 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     5 |            |          |
|*  2 |   FILTER               |             |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IDX_OBJ_LHR |   145K|   708K|    90   (2)| 00:00:02 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   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 id):
---------------------------------------------------

   2 - filter(:X<=:Y)
   3 - filter(("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]


53 rows selected.

& 说明：

有关真实的执行计划的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2152884/