ITPub博客

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

原创 Oracle 作者:lhrbest 时间:2018-04-13 09:36:52 0 删除 编辑

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





  

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*PlusAUTOTRACE功能有部分是真实执行了SQL语句的(例如所有DML语句),但是,由于该命令所显示的执行计划来源于调用EXPLAIN PLAN命令,所以,其得到的执行计划依然可能不准确(特别是在使用了绑定变量的情况下)。那么,为什么EXPLAIN PLAN命令里显示的预估执行计划与该SQL真实的执行计划不一样呢?原因有多个方面,常见的情况包括以下几个方面:

① 绑定变量窥视(Bind Peeking):EXPLAIN PLAN里不会进行绑定变量窥视,但是Runtime Plan里会进行绑定变量窥视,所以,如果发生这种情况,那么会使这两个执行计划产生差异。

② 隐式转换:Explain Plan里不会考虑绑定变量的类型,但是Runtime Plan里会考虑类型,从而有可能会根据绑定变量的类型出现隐式转换,所以谓词(Predicate)会发生变化,使得执行计划也会产生差异。

③ 优化器参数:执行Explain PlanSessionRuntime PlanSession不是同一个。如果各个Session之间存在优化器参数差异,那么执行计划也会产生差异。

④ 统计信息收集参数:Explain Plan始终是用最新的统计信息产生执行计划,但是,Runtime Plan不一定会用最新的统计信息。因此也会产生执行计划差异。在收集统计信息时,一个与缓存的游标是否失效的很重要的参数为NO_INVALIDATE。在重新收集统计信息时,可以指定NO_INVALIDATE选项。该选项有TRUEFALSEDBMS_STATS.AUTO_INVALIDATE3个值。如果取值为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 FORSET 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”来创建。

2SQL*PlusAUTOTRACE功能,命令: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代表数据库的DBIDL_INST_NUM代表数据库的实例号,单机环境为1RAC环境填写具体的实例号,L_BID为开始的快照号,L_EID为结束的快照号,L_SQLID为要查看SQLSQL_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,那么就最先执行,首先,67913最右,所以,67最先执行做HASH JOIN,为675

第二,8有子节点,接下来是98

第三,HASH的结果和8的结果做FILTER过滤。

第四,10这个节点根据原则是11131210

第五,剩下依次是3210

所以,该图的执行顺序是675984111312103210







为什么预估执行计划与真实执行计划会有差异?

问题概要


对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的 Runtime Plan 真实执行计划,偶尔会发现两边有不一致的情况,为什么呢?为什么预估执行计划会不准确?怎样才能避免这种情况的发生?


问题解答

这是执行计划相关中会被经常问道的问题,也是困扰自己很长时间的问题。希望通过下面的分析能解释一部分原因。


对同一个 SQL 语句的 ExplainPlan 里显示的预估执行计划与通过 V$SQL_PLAN 视图获取的真实执行计划不一致的情况,其原因要比想象的更多种多样。

 

  • 绑定变量窥视(Bind Peeking):Explain Plan 里不会进行绑定变量窥视,但是 Runtime Plan 里会进行绑定变量窥视,所以,如果这种情况发生会使两个执行计划会产生差异。

  • 隐式转换:Explain Plan 里不会考虑绑定变量的类型,但是 Runtime Plan 里会考虑类型,从而有可能会根据绑定变量的类型出现隐式转换,所以谓词(Predicate)会发生变化,使得执行计划也会产生差异。

  • 优化器参数:执行 Explain Plan 的 Session 与 Runtime Plan 的 Session 不是同一个。如果各个 Session 之间存在优化器参数差异,执行计划也会产生差异。

  • 统计信息收集参数:Explain Plan 始终是用最新的统计信息产生执行计划,但是,Runtime Plan 不一定会用最新的统计信息。因此也会产生执行计划差异。

 

预估执行计划与实际执行计划产生差异的原因总结为上面几种情况,当然也有因 Oracle Bug 的原因也会有产生执行计划的差异情况。


下面通过几个测试,加深对上面的问题的理解。


测试环境

Oracle 版本是 11.2.0.1的情况。

 

SQL> SELECT * FROMV$VERSION WHERE ROWNUM <= 1;


BANNER

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

Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 Production


生成表 T1,T1 表有如下特点:


表名

列名

列类型

说明

T1

C1

Number

“1”值有10,000个,“1~10000”的值各一个,总共有10,000种值

C2

Varchar2

同上


之后,对列 C1、C2 分别生成单列索引 IDX_T1_C1 和IDX_T1_C2。

 

SQL> CREATE TABLET1 ( C1 INT , C2 VARCHAR2(10));

 

表已创建。

 

SQL> INSERT INTOT1 SELECT 1, '1' FROM DUAL CONNECT BY LEVEL <= 10000;

 

已创建10000行。

 

SQL> INSERT INTOT1 SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000;

 

已创建10000行。

 

SQL> CREATE INDEXIDX_T1_C1 ON T1(C1);

 

索引已创建。

 

SQL> CREATE INDEXIDX_T1_C2 ON T1(C2);

 

索引已创建。


对表T1进行统计信息收集。METHOD_OPT 的参数设为 ALLCOLUMNS SIZE 5 ,即,直方图的 BUCKETS 个数指定为5。但是列 C1 和 C2 有 10,000个不同的值,BUCKETS 个数为5的话,会生成等高直方图(HEIGHT BALANCED)。


SQL> EXECDBMS_STATS.gather_table_stats(user,'T1', method_opt =>'FOR ALL COLUMNS SIZE5');

 

PL/SQL 过程已成功完成。

 

收集统计信息以后如下:


--table stats

SELECT t1.TABLE_NAME,

       t1.num_rows,

       t1.SAMPLE_SIZE

FROM   dba_tables t1

WHERE  table_name = 'T1'

AND    t1.OWNER = user;

 

TABLE_NAME   NUM_ROWS SAMPLE_SIZE

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

T1              20000       20000

 

--column stats

SELECT t2.TABLE_NAME,

       t2.COLUMN_NAME,

      t2.NUM_DISTINCT,

       t2.NUM_NULLS,

       t2.DENSITY,

       t2.LOW_VALUE,

       t2.HIGH_VALUE,

       t2.HISTOGRAM

FROM   dba_tab_columns t2

WHERE  t2.table_name = 'T1'

AND    t2.OWNER = user';

 

TABLE COLUMNUM_DISTINCT NUM_NULLS DENSITY LOW_V HIGH_VALUE HISTOGRAM

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

T1    C1          10000         0  0.00005 C102 C302       HEIGHT BALANCED

T1    C2          10000         0  0.00005 31   39393939   HEIGHT BALANCED

 

--histogram stats

select t3.TABLE_NAME

       ,t3.COLUMN_NAME

       ,t3.ENDPOINT_NUMBER

       ,t3.ENDPOINT_VALUE

from  dba_tab_histograms t3

WHERE  t3.table_name = 'T1'

AND    t3.OWNER = user;

 

TABLE COLUM ENDPOINTENDPOINT_VALUE

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

T1    C1          2              1

T1    C1          3           2000

T1    C1          4           6000

T1    C1          5          10000

T1    C2          2 2.544225460682

T1    C2          3 2.607349087913

T1    C2          4 2.814229665870

T1    C2          5 2.971215519298


案列1:绑定变量窥视(Bind Peeking)

下面我们看下,因绑定变量窥视,而引起的预估执行计划与实际执行计划不一致的情况。首先,激活绑定变量窥视功能,默认值就是TRUE。

 

SQL> alter sessionset "_optim_peek_user_binds" = true;

 

会话已更改。

 

首先,我们输出预估执行计划。从下面可以看到,执行计划选择的是索引范围扫描(Index Range Scan)的方式。

 

SQL> var b1number;

SQL> exec :b1 :=1;

 

PL/SQL 过程已成功完成。

 

SQL> explain planfor

  2 select count(c2)

  3  fromt1

  4 where c1 = :b1;

 

已解释。

 

SQL> select * fromtable(dbms_xplan.display(null,null,'typical'));

 

 

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

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

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

|   0 | SELECT STATEMENT             |           |    1 |     6 |     2  (0)| 00:00:01 |

|   1 | SORT AGGREGATE              |           |    1 |     6 |            |          |

|   2 |  TABLE ACCESS BY INDEX ROWID| T1       |     2 |    12 |    2   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN          |IDX_T1_C1 |     2 |       |    1   (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   3 - access("C1"=TO_NUMBER(:B1))

 

绑定变量B1的实际值是“1”。T1表里值为1的记录数将近占50%,这种情况与其选择索引范围扫,不如选择全表扫(Table Full Scan)会有效率一些。但是 ExplainPlan命令不进行绑定变量的窥视,即,在创建预估执行计划的过程中,会把绑定变量的值设为未知(Uknown)来处理,不会考虑实际的绑定变量的值到底是什么。所以,ExplainPlan 不关心其值是不是“1”,而只考虑 Distinct Count 来建立执行计划。


等高直方图(HEIGHT BALANCED)存在的时候,预估行数会通过 DistinctCount 列进行计算。计算公式如下:

 

预估行数 = 全部行数 / Distinct Count = 20,000 /10,000  = 2 

   

但是,实际执行计划与上面的结果完全不一样,如下。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = :b1;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'typical'));

 

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

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

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

|   0 | SELECT STATEMENT   |     |       |       |   12 (100)|          |

|   1 | SORT AGGREGATE    |      |    1 |     6 |            |          |

|*  2 |  TABLE ACCESS FULL| T1   |  8000 | 48000 |    12  (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   2 - filter("C1"=:B1)


可以看到,使用了绑定变量窥视,即,优化器在创建执行计划前读取了绑定变量的实际的值(进行是窥视)。之后,参考绑定变量的值来创建执行计划。这个例子,使用了值“1”来创建了执行计划。所以,预估行数从 ExplainPlan 里的2 变成了8000。其原因如下:

预估行数 =  值“1”的 buckets 数 * buckets 的高度

               = 2 *(20000 / 5 ) = 8000


实际行数为10,001,预估值与实际值相当接近了。


使用 DBMS_XPLAN.DISPLAY_CURSOR函数的时候,参数里如果加上 +PEEKED_BBINDS 的话,执行计划里可以看到绑定变量窥视的值。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = :b1;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'all +peeked_binds'));

 

 

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

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

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

|   0 | SELECT STATEMENT   |     |       |       |   12 (100)|          |

|   1 | SORT AGGREGATE    |      |    1 |     6 |            |          |

|*  2 |  TABLE ACCESS FULL| T1   |  8000 | 48000 |    12  (0)| 00:00:01 |

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

 

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

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

 

   1 - SEL$1

   2 - SEL$1 / T1@SEL$1

 

Peeked Binds(identified by position):

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

 

   1 - :B1 (NUMBER): 1

 

Predicate Information(identified by operation id):

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

 

   2 - filter("C1"=:B1)

 

Column ProjectionInformation (identified by operation id):

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

 

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

   2 - "C2"[VARCHAR2,10]

 

绑定变量窥视(Binding Peeking)与绑定变量捕获(Bind Capture)经常弄混。绑定变量捕获(Bind Capture)是对特定 SQL 里使用的绑定变量值按照固定周期放到 SGA 里保存的情况。最初的绑定变量窥视与绑定变量捕获的时间是一样,约15分钟(900秒)后,绑定变量捕获会再次发生,周期性反复发生。下面可以查看绑定变量捕获的信息。

 

SELECT t4.NAME,

       t4.POSITION,

      t4.VALUE_STRING,

       t4.WAS_CAPTURED,

       t4.LAST_CAPTURED

FROM   V$sql_bind_capture t4

WHERE  sql_id = 'bqqp887001jj8';

 

NAME  POSITION VALUE WAS_C LAST_CAPTU

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

:B1          1 1     YES  11-4月 -18


案列2:绑定变量类型问题

首先,为了证明这个测试不是因为上面的绑定变量窥视而引起的不一致,所以把绑定变量窥视功能关掉了。


SQL> alter sessionset "_optim_peek_user_binds" = false;

 

会话已更改。

 

对 C2 列使用绑定变量,进行观察。从下面可以看到,预估执行计划里使用了索引。

 

SQL> var b2number;

SQL> exec :b2 :=1;

 

PL/SQL 过程已成功完成。

 

SQL> explain planfor

  2  selectcount(c2)

  3  fromt1

  4 where c2 = :b2;

 

已解释。

 

SQL> select * fromtable(dbms_xplan.display(null,null,'typical'));

 

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

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

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

|   0 | SELECT STATEMENT  |          |     1 |     4 |    1   (0)| 00:00:01 |

|   1 | SORT AGGREGATE   |           |    1 |     4 |            |         |

|*  2 |  INDEX RANGE SCAN| IDX_T1_C2 |    2 |     8 |     1  (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   2 - access("C2"=:B2)

 

ExplainPlan 命令只会查看是否存在绑定变量,而不会考虑绑定变量的类型是什么,其值是什么,始终会把绑定变量的类型设为 VARCHAR2 类型进行考虑。所以,上面的例子里不管对绑定变量B2如何定义,ExplainPlan 里预估执行计划始终是一样。


但是,真实执行计划里没有选择 INDEX RANGE SCAN,而是选择了 TABLE FULL SCAN。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c2 = :b2;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 

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

| Id  | Operation          | Name | Starts | E-Rows | A-Rows|   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT   |     |      1 |        |     1 |00:00:00.01 |      39 |

|   1 | SORT AGGREGATE    |      |     1 |      1 |      1 |00:00:00.01 |      39 |

|*  2 |  TABLE ACCESS FULL| T1   |      1 |     2 |  10001 |00:00:00.01 |      39 |

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

 

Predicate Information(identified by operation id):

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

 

   2 - filter(TO_NUMBER("C2")=:B2)

 

为什么会发生这种情况?绑定变量窥视功能已经关闭了,所以肯定不是绑定变量窥视的问题。这里需要注意的是,C2 列是 VARCHAR2 类型,绑定变量 B2 是 NUMBER类型。这时,Oracle 会进行隐式转换,VARCHAR2 类型会被转换成 NUMBER 类型,即,NUMBER 类型的优先级更高。所以,会对C2列进行隐式转换(VARCHAR2 →NUMBER),从而不能使用C2列的索引。可以在谓词信息(Predicate Information)中确认。

 

Predicate Information(identified by operation id):

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

 

   2 - filter(TO_NUMBER("C2")=:B2)

 

为了再次证明这个是因为隐式转换的问题,我们使用 VARCHAR2 类型的绑定变量 B3 进行测试。

 

SQL> var b3varchar2(10);

SQL> exec :b3 :='1';

 

PL/SQL 过程已成功完成。

 

SQL> select /*+gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c2 = :b3;

 

 COUNT(C2)

----------

     10001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 

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

| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

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

|   0 | SELECT STATEMENT  |          |      1 |        |     1 |00:00:00.01 |      20 |

|   1 | SORT AGGREGATE   |           |      1 |     1 |      1 |00:00:00.01 |      20 |

|*  2 |  INDEX RANGE SCAN| IDX_T1_C2 |     1 |      2 |  10001 |00:00:00.01 |      20 |

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

 

Predicate Information(identified by operation id):

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

 

   2 - access("C2"=:B3)

 

从上面可以看到,绑定变量类型是 VARCHAR2 的时候,没有进行隐式转换,产生了与预估执行计划相同的执行计划,使用了索引的范围扫描。


这个例子也说明,不能完全相信预估的执行计划。内部的一些转换(比如列的隐式转换)会使执行计划改变,甚至有时候会出现不希望的执行计划。


案列3:统计信息收集的参数问题

下面 SQL 的预估执行计划与实际执行计划完全一致。

 

 SQL> explain planfor

  2 select count(c2)

  3  fromt1

  4 where c1 = 2;

 

已解释。

 

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

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

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

|   0 | SELECT STATEMENT             |           |    1 |     6 |     2  (0)| 00:00:01 |

|   1 | SORT AGGREGATE              |           |    1 |     6 |            |          |

|   2 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |     6 |    2   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN          |IDX_T1_C1 |     1 |       |    1   (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   3 - access("C1"=2)

 

 

SQL> select  /*+ gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = 2;

 

 COUNT(C2)

----------

         1

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

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

| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time  | Buffers |

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

|   0 | SELECT STATEMENT             |           |      1 |       |      1 |00:00:00.01 |       3 |

|   1 | SORT AGGREGATE              |           |      1 |     1 |      1 |00:00:00.01 |       3 |

|   2 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |      1 |     1 |00:00:00.01 |       3 |

|*  3 |   INDEX RANGE SCAN          |IDX_T1_C1 |      1 |      1 |     1 |00:00:00.01 |       2 |

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

 

Predicate Information(identified by operation id):

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

 

   3 - access("C1"=2)

 

对 T1 表的 C1 = 2,C2=1 的值增加20,000个,之后重新收集统计信息,但是 NO_INVALIDATE 参数设为 NULL,NULL 的意思是让 Oracle 自动处理的意思。NO_INVALIDATE 其他参数情况参考如下:

 

  • NO_INVALIDATE=TRUE:更新统计信息,但对有从属(Dependency)关系的 SQL 不进行Invalidation。为了避免一次性大量的硬解析(Hard Parse)现象的发生。SQL 如果在 SGA 里 Age Out 后,再次执行的时候,才会用到更新后的统计信息。

  • NO_INVALIDATE=FALSE:更新统计信息,并对有从属(Dependency)关系的 SQL 马上进行 Invalidation。

  • NO_INVALIDATE=AUTO(NULL):更新统计信息,但对有从属关系的 SQL 不会一次性的进行 Invalidation,而是在最大5小时(18,000秒)内随机进行 Invalidation 的方式进行。可以说是 TRUE 与 FALSE 的中间形式。18,000秒是可以通过 _OPTIMIZER_INVALIDATION_PERIOD 参数进行设定。

 

现在对表T1增加数据,并收集统计信息,但是 NO_INVALIDATE 参数设为 NULL(默认值是 NULL)。

 

 SQL> insert intot1 select 2,'1' from dual connect by level <= 20000;

 

已创建20000行。

 

SQL> execdbms_stats.gather_table_stats(user,'T1',method_opt => 'for all columns size5',no_invalidate => null);

 

PL/SQL 过程已成功完成。

 

Explain Plan 命令始终是在用最新的统计信息,所以从下面可以看到,ExplainPlan 命令对 C1=2 的条件使用了最新的统计信息,执行计划选择了 Table Full Scan。预估行数为 16,000 行,与实际行数 20,001 行数相当接近。因为存在列的直方图,这种预估是可行的。

 

 SQL> explain planfor

  2 select count(t1.c2)

  3  fromsys.t1

  4 where t1.c1 = 2 ;

 

已解释。

 

SQL> select * fromtable(dbms_xplan.display());

 

 

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

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

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

|   0 | SELECT STATEMENT   |     |     1 |     6 |   20   (0)| 00:00:01 |

|   1 | SORT AGGREGATE    |      |    1 |     6 |            |          |

|*  2 |  TABLE ACCESS FULL| T1   | 16000 |96000 |    20   (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   2 - filter("T1"."C1"=2)

 

但是,在真实执行计划中仍然选择了 Index Range Scan,因为虽然统计信息更新了,但是相关的 SQL 还没有被 Invalidation。

 

 SQL> select  /*+ gather_plan_statistics */ count(c2)

  2  fromt1

  3 where c1 = 2;

 

 COUNT(C2)

----------

     20001

 

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

 

 

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

| Id  | Operation                      | Name     | Starts | E-Rows | A-Rows |  A-Time   | Buffers |

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

|   0 | SELECT STATEMENT               |           |     1 |        |      1 |00:00:00.02 |     102 |

|   1 | SORT AGGREGATE                |          |      1 |      1 |     1 |00:00:00.02 |     102 |

|   2 |  TABLE ACCESS BY INDEX ROWID | T1       |      1 |      2 | 20001 |00:00:00.02 |     102 |

|*  3 |   INDEX RANGE SCAN            | IDX_T1_C1 |      1 |     2 |  20001 |00:00:00.01 |      70 |

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

 

Predicate Information(identified by operation id):

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

 

   3 - access("C1"=2)

 

如果只看 Explain Plan 后就判断“执行计划的效率不错”是不可取的,会根据不同的情况产生很大的性能差异。这时可以通过 DBMS_SHARED_POOL.PURGE 存储过程,或使用 清理共享池(Shared Pool Flush)等方法强制反应最新的统计信息。


总结

预估执行计划与真实执行计划产生差异的原因,其实是多种多样的,在分析其原因的过程中发现需要相当多的知识点。


产生差异的原因,其中最普遍的有因绑定变量的窥视,也有因绑定变量的隐式转换,也有因参数差异,也有因统计信息收集参数等问题。


不能对预估执行计划100%信任,一定要实际执行以后验证其结果。如果这个过程中想解释执行计划异常的现象,需要了解 DBMS_XPLAN 包的使用方法与对其结果的正确理解。








About Me

.............................................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号:230161599(满)、618766405

● 微信群:可加我微信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友646634621,注明添加缘由

● 于 2018-04-01 06:00 ~ 2018-04-31 24:00 在魔都完成

● 最新修改时间:2018-04-01 06:00 ~ 2018-04-31 24:00

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用网络班http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2《DBA笔试面宝典》读者群小麦苗的微店

   小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面试宝典》读者群       小麦苗的微店

.............................................................................................................................................


DBA笔试面试讲解群
《DBA宝典》读者群 欢迎与我联系



请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1103
  • 访问量
    6998323