在Oracle中,如何得到真实的执行计划?

Oracle查看执行计划的几种方法:http://blog.itpub.net/26736162/viewspace-2136865/ 


我们提供的服务有:成都做网站、成都网站制作、微信公众号开发、网站优化、网站认证、泰宁ssl等。为超过千家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务,是有科学管理、有技术的泰宁网站制作公司

一、  如何得到真实的执行计划?

在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.

 

二、  如何在不执行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

-----------------------------------------------

| Id  | Operation                   | Name    |

-----------------------------------------------

|   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

---------------------------------------------------------------------------

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS FULL| FILE$ |     1 |     6 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

31 rows selected.

四、  给出一个执行计划的执行顺序

阅读如下的执行计划,给出SQL的执行顺序。

-----------------------------------------

| Id  | Operation

-----------------------------------------

|   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。



当前文章:在Oracle中,如何得到真实的执行计划?
文章分享:http://scyanting.com/article/gjjgcc.html