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

2021/4/16 2:31:01

编程Tag： table select oracle explain count DB lhr PROD1 598

本文主要是介绍【DB笔试面试598】在Oracle中，如何得到真实的执行计划？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

640?wx_fmt=gif

♣题目 部分

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


      

♣答案部分


在Oracle数据库中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行过，真正执行过的SQL所对应的执行计划就是准确的，反之则有可能不准，因此，通过10046事件及如下的几种方式得到的执行计划是最准确的，而从其它方式获取到的执行计划都有可能不准确。

1SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS'));2SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER, 'ADVANCED ALLSTATS'));3SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id'));SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS'));
2SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id/hash_value',CHILD_CURSOR_NUMBER, 'ADVANCED ALLSTATS'));
3SELECT * 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选项，所以，即使收集了统计信息，执行计划也不会立即改变。可以在表级别设置让所有依赖于该表的游标不失效，设置方法为：

1EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集SH.SALES表上的统计信息时，让所有依赖于该表的游标不失效--在收集SH.SALES表上的统计信息时，让所有依赖于该表的游标不失效

 
实验一：
1CREATE TABLE TEST_EXPLAIN_LHR AS SELECT * FROM DBA_OBJECTS; 2INSERT INTO TEST_EXPLAIN_LHR SELECT * FROM TEST_EXPLAIN_LHR; 3COMMIT; 4SELECT COUNT(*) FROM TEST_EXPLAIN_LHR; 5 6CREATE INDEX IDX_OBJ_LHR ON  TEST_EXPLAIN_LHR(OBJECT_ID); 7EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'test_explain_lhr',ESTIMATE_PERCENT => 100,CASCADE => TRUE); 8 9VAR X NUMBER;10VAR Y NUMBER;11EXEC :X := 0;12EXEC :Y := 100000;1314EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;1516SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);171819SET AUTOT ON20SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;2122SET AUTOT OFF23SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;24SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));CREATE TABLE TEST_EXPLAIN_LHR AS SELECT * FROM DBA_OBJECTS;
 2INSERT INTO TEST_EXPLAIN_LHR SELECT * FROM TEST_EXPLAIN_LHR;
 3COMMIT;
 4SELECT COUNT(*) FROM TEST_EXPLAIN_LHR;

 6CREATE INDEX IDX_OBJ_LHR ON  TEST_EXPLAIN_LHR(OBJECT_ID);
 7EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'test_explain_lhr',ESTIMATE_PERCENT => 100,CASCADE => TRUE);

 9VAR X NUMBER;
10VAR Y NUMBER;
11EXEC :X := 0;
12EXEC :Y := 100000;

14EXPLAIN PLAN FOR SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;

16SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

19SET AUTOT ON
20SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;

22SET AUTOT OFF
23SELECT COUNT(*) FROM TEST_EXPLAIN_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y ;
24SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

下面实验验证了使用EXPLAIN PLAN FOR和SET AUTOT ON方式获取到的执行计划都是不准确的：
 1SYS@PROD1> clear scr  2SYS@PROD1> CREATE TABLE test_explain_lhr AS SELECT * FROM Dba_Objects;  3  4Table created.  5  6SYS@PROD1> INSERT INTO test_explain_lhr SELECT * FROM test_explain_lhr;  7  872503 rows created.  9 10SYS@PROD1> COMMIT; 11 12Commit complete. 13 14SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr; 15 16  COUNT(*) 17---------- 18    145006 19 20SYS@PROD1> CREATE INDEX idx_obj_lhr ON  test_explain_lhr(object_id); 21 22Index created. 23 24SYS@PROD1> EXEC dbms_stats.gather_table_stats(USER,'test_explain_lhr',estimate_percent => 100,cascade => TRUE); 25 26PL/SQL procedure successfully completed. 27 28SYS@PROD1> VAR x NUMBER; 29SYS@PROD1> VAR y NUMBER; 30SYS@PROD1> EXEC :x := 0; 31 32PL/SQL procedure successfully completed. 33 34SYS@PROD1> EXEC :y := 100000; 35 36PL/SQL procedure successfully completed. 37 38SYS@PROD1> EXPLAIN PLAN FOR SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; 39 40Explained. 41 42 43SYS@PROD1> set line 9999 44SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display); 45 46PLAN_TABLE_OUTPUT 47--------------------------------------------------------------------------------------------- 48Plan hash value: 3299589416 49 50---------------------------------------------------------------------------------- 51| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | 52---------------------------------------------------------------------------------- 53|   0 | SELECT STATEMENT   |             |     1 |     5 |     3   (0)| 00:00:01 | 54|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          | 55|*  2 |   FILTER           |             |       |       |            |          | 56|*  3 |    INDEX RANGE SCAN| IDX_OBJ_LHR |   363 |  1815 |     3   (0)| 00:00:01 | 57---------------------------------------------------------------------------------- 58 59Predicate Information (identified by operation id): 60--------------------------------------------------- 61 62   2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y)) 63   3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND 64              "T"."OBJECT_ID"<=TO_NUMBER(:Y)) 65 6617 rows selected. 67 68SYS@PROD1> set autot on 69SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ; 70 71  COUNT(*) 72---------- 73    145006 74 75 76Execution Plan 77---------------------------------------------------------- 78Plan hash value: 3299589416 79 80---------------------------------------------------------------------------------- 81| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | 82---------------------------------------------------------------------------------- 83|   0 | SELECT STATEMENT   |             |     1 |     5 |     3   (0)| 00:00:01 | 84|   1 |  SORT AGGREGATE    |             |     1 |     5 |            |          | 85|*  2 |   FILTER           |             |       |       |            |          | 86|*  3 |    INDEX RANGE SCAN| IDX_OBJ_LHR |   363 |  1815 |     3   (0)| 00:00:01 | 87---------------------------------------------------------------------------------- 88 89Predicate Information (identified by operation id): 90--------------------------------------------------- 91 92   2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y)) 93   3 - access("T"."OBJECT_ID">=TO_NUMBER(:X) AND 94              "T"."OBJECT_ID"<=TO_NUMBER(:Y)) 95 96 97Statistics 98---------------------------------------------------------- 99          1  recursive calls100          0  db block gets101        329  consistent gets102          0  physical reads103          0  redo size104        424  bytes sent via SQL*Net to client105        419  bytes received via SQL*Net from client106          2  SQL*Net roundtrips to/from client107          0  sorts (memory)108          0  sorts (disk)109          1  rows processed110111SYS@PROD1> SET AUTOT OFF112SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;113114  COUNT(*)115----------116    145006117118SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'advanced'));119120PLAN_TABLE_OUTPUT121----------------------------------------------------------------------------------------122SQL_ID  1r87sg98rdkuf, child number 0123-------------------------------------124SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x125AND :y126127Plan hash value: 2428225634128129--------------------------------------------------------------------------------------130| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |131--------------------------------------------------------------------------------------132|   0 | SELECT STATEMENT       |             |       |       |    90 (100)|          |133|   1 |  SORT AGGREGATE        |             |     1 |     5 |            |          |134|*  2 |   FILTER               |             |       |       |            |          |135|*  3 |    INDEX FAST FULL SCAN| IDX_OBJ_LHR |   145K|   708K|    90   (2)| 00:00:02 |136--------------------------------------------------------------------------------------137138Query Block Name / Object Alias (identified by operation id):139-------------------------------------------------------------140141   1 - SEL$1142   3 - SEL$1 / T@SEL$1143144Outline Data145-------------146147  /*+148      BEGIN_OUTLINE_DATA149      IGNORE_OPTIM_EMBEDDED_HINTS150      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')151      DB_VERSION('11.2.0.1')152      ALL_ROWS153      OUTLINE_LEAF(@"SEL$1")154      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("TEST_EXPLAIN_LHR"."OBJECT_ID"))155      END_OUTLINE_DATA156  */157158Peeked Binds (identified by position):159--------------------------------------160161   1 - :X (NUMBER): 0162   2 - :Y (NUMBER): 100000163164Predicate Information (identified by operation id):165---------------------------------------------------166167   2 - filter(:X<=:Y)168   3 - filter(("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y))169170Column Projection Information (identified by operation id):171-----------------------------------------------------------172173   1 - (#keys=0) COUNT(*)[22]17417517653 rows selected.
  2SYS@PROD1> CREATE TABLE test_explain_lhr AS SELECT * FROM Dba_Objects;

  4Table created.

  6SYS@PROD1> INSERT INTO test_explain_lhr SELECT * FROM test_explain_lhr;
rows created.

 10SYS@PROD1> COMMIT;

 12Commit complete.

 14SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr;
 COUNT(*)
 17----------


 20SYS@PROD1> CREATE INDEX idx_obj_lhr ON  test_explain_lhr(object_id);

 22Index created.

 24SYS@PROD1> EXEC dbms_stats.gather_table_stats(USER,'test_explain_lhr',estimate_percent => 100,cascade => TRUE);

 26PL/SQL procedure successfully completed.

 28SYS@PROD1> VAR x NUMBER;
 29SYS@PROD1> VAR y NUMBER;
 30SYS@PROD1> EXEC :x := 0;

 32PL/SQL procedure successfully completed.

 34SYS@PROD1> EXEC :y := 100000;

 36PL/SQL procedure successfully completed.

 38SYS@PROD1> EXPLAIN PLAN FOR SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;

 40Explained.

 43SYS@PROD1> set line 9999
 44SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display);

 46PLAN_TABLE_OUTPUT
 47---------------------------------------------------------------------------------------------
 48Plan hash value: 3299589416

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

 59Predicate Information (identified by operation id):
 60---------------------------------------------------
  2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
- access("T"."OBJECT_ID">=TO_NUMBER(:X) AND
             "T"."OBJECT_ID"<=TO_NUMBER(:Y))
rows selected.

 68SYS@PROD1> set autot on
 69SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;
 COUNT(*)
 72----------


 75
 76Execution Plan
 77----------------------------------------------------------
 78Plan hash value: 3299589416

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

 89Predicate Information (identified by operation id):
 90---------------------------------------------------
  2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
- access("T"."OBJECT_ID">=TO_NUMBER(:X) AND
             "T"."OBJECT_ID"<=TO_NUMBER(:Y))

 97Statistics
 98----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed

111SYS@PROD1> SET AUTOT OFF
112SYS@PROD1> SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x AND :y ;
 COUNT(*)
115----------


118SYS@PROD1> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'advanced'));

120PLAN_TABLE_OUTPUT
121----------------------------------------------------------------------------------------
122SQL_ID  1r87sg98rdkuf, child number 0
123-------------------------------------
124SELECT COUNT(*) FROM test_explain_lhr t WHERE t.object_id BETWEEN :x
125AND :y

127Plan hash value: 2428225634

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

138Query Block Name / Object Alias (identified by operation id):
139-------------------------------------------------------------
  1 - SEL$1
- SEL$1 / T@SEL$1

144Outline Data
145-------------
 /*+
     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
 */

158Peeked Binds (identified by position):
159--------------------------------------
  1 - :X (NUMBER): 0
- :Y (NUMBER): 100000

164Predicate Information (identified by operation id):
165---------------------------------------------------
  2 - filter(:X<=:Y)
- filter(("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y))

170Column Projection Information (identified by operation id):
171-----------------------------------------------------------
  1 - (#keys=0) COUNT(*)[22]

rows selected.

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

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。


640?wx_fmt=gif
---------------优质麦课------------
640?wx_fmt=png
 详细内容可以添加麦老师微信或QQ私聊。

640?wx_fmt=gif

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621  QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
640?wx_fmt=gifDBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
640?wx_fmt=gif
640?wx_fmt=gif
640?wx_fmt=png喜欢就点击“好看”吧




                   


                   

                        这篇关于【DB笔试面试598】在Oracle中，如何得到真实的执行计划？的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
                    

                     

                 
                

                
                    

                

                
 
                
                

                    

                        
相关编程文章

                        更多>
                    

                    
                    
                    
                


            

        


        

             


    

        
    




             

        


    

    
    
        


    

        

            

            

            


        

        

            

                本站官方QQ
                

                    
官方QQ:

                    
23002807

                

            

            
        

        

           
            

                沪ICP备2023033072号-1
                
                  
                 
                
            

            

                      
                
            

        

    







    
    
    


        
        扫一扫关注最新编程教程