[20161216]toad下显示真实的执行计划.txt

--大家都应该知道使用explain plan看执行计划,有时候显示的执行计划不是真实的执行计划.现在我虽然使用它看,仅仅作为参考.

--昨天看链接:

--才知道toad下如何在sql编辑界面上显示真实的执行计划.

--仅仅在执行计划上点击右键,勾上"load cached plan if possible" 就ok了.我自己写一个例子测试看看.

1.环境:

SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as select rownum id ,lpad('x',100,'x') name ,lpad('1',1) flag from dual connect by level<=1e5;

update t set flag='0' where id=1e5; commit ; create index i_t_flag on t(flag);

SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => '',TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 4',Cascade => True ,No_Invalidate => false)

PL/SQL procedure successfully completed.

--在flag字段上建立直方图.

2.测试:

Select /*+ BIND_AWARE */ * from t where flag=:x;

--实际情况下带入'0','1',两者执行计划不一样.而在toad下使用explain plan看就是全表扫描.

--先带入'0'测试看看.再sql下执行看看.

SCOTT@book> alter system flush shared_pool;

System altered.

SCOTT@book> variable x varchar2(1)

SCOTT@book> exec :x := '0'; PL/SQL procedure successfully completed.

SCOTT@book> SELECT /*+ BIND_AWARE */ * from t where flag=:x;

        ID NAME                                     F ---------- ---------------------------------------- -     100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0            xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx            xxxxxxxxxxxxxxxxxxxx

SCOTT@book> @ &r/dpc ''  ''

PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  7739acusdmc6c, child number 0 ------------------------------------- SELECT /*+ BIND_AWARE */ * from t where flag=:x Plan hash value: 120143814 ----------------------------------------------------------------------------------------- | Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |   108 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |       |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1    2 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - (VARCHAR2(30), CSID=852): '0' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("FLAG"=:X)

3.toad下查看,修改display mode 改成 dbms_xplan,这样不用帖图. SQL_ID  7739acusdmc6c, child number 0 ------------------------------------- SELECT /*+ BIND_AWARE */ * from t where flag=:x Plan hash value: 120143814 ----------------------------------------------------------------------------------------- | Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |   108 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |       |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1    2 - SEL$1 / T@SEL$1 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))       END_OUTLINE_DATA   */ Peeked Binds (identified by position): --------------------------------------    1 - (VARCHAR2(30), CSID=852): '0' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("FLAG"=:X)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "FLAG"[VARCHAR2,1]    2 - "T".ROWID[ROWID,10], "FLAG"[VARCHAR2,1]   Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level   --//可以发现执行计划走索引. --//修改SELECT 为sELECT,再看执行计划,看到的执行计划如下: Plan hash value: 1601196873 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |  50000 |  5273K|   435   (1)| 00:00:06 | |*  1 |  TABLE ACCESS FULL| T    |  50000 |  5273K|   435   (1)| 00:00:06 | ---------------------------------------------------------------------------   --很明显这个执行计划可能是不真实的.

3.另外我也发现问题,如果你跟踪toad操作,可以发现

declare

  v_ignore raw(100);   v_oldhash number;   v_hash number; begin   v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);   :outHash := v_hash; end;

SQLText=['sELECT /*+ BIND_AWARE */ * from t where flag=:x']

outHash=[0.334866302e+010]

Elapsed time: 0.003

-------------------------------------------------------------------------------- Timestamp: 2016/12/16 11:33:09

Select *

from v$sql_plan Where hash_value = '3348663027' and child_number =0 order by id

sqlhv=['3348663027']

cn=[0]

Elapsed time: 0.005

explain plan set statement_id='Administrator:121616113309' into SYS.PLAN_TABLE$ For sELECT /*+ BIND_AWARE */ * from t where flag=:x

Elapsed time: 0.005

--它仅仅查询v$sql_plan child_number =0的是否存在,有一些情况child_number =0是已经无效的执行计划,甚至不存在的执行计划.

--这样依旧调用explain plan.这个问题也存在于SGA TRACE的界面上.我一直希望有一个下拉列表,让dba选择对应的child_number.

4.还有这个测试我还发现toad界面下,提示BIND_AWARE无效.

你可以发现在toad下执行,带入变量'0',选择的执行计划依旧是全表扫描.

--//修改flag大写,在toad下执行,变量'0'.

sELECT /*+ BIND_AWARE */ * from t where FLAG=:x;

SCOTT@book> select sql_id,sql_text,executions  from v$sqlarea where upper(sql_text) like 'SELECT%BIND_AWARE%' and upper(sql_text) not like '%SQL_TEXT%';

SQL_ID        SQL_TEXT                                                     EXECUTIONS ------------- ------------------------------------------------------------ ---------- 3bsyf7a6jz1py SELEct /*+ BIND_AWARE */ * from t where flag=:x                       3 bh4qquz7sm25k sELECT /*+ BIND_AWARE */ * from t where FLAG=:x                       1 7739acusdmc6c SELECT /*+ BIND_AWARE */ * from t where flag=:x                       1

--看看sql_id='bh4qquz7sm25k'执行计划.

SCOTT@book> @ &r/dpc bh4qquz7sm25k  ''

PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  bh4qquz7sm25k, child number 0 ------------------------------------- sELECT /*+ BIND_AWARE */ * from t where FLAG=:x Plan hash value: 1601196873 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |   435 (100)|          | |*  1 |  TABLE ACCESS FULL| T    |  50000 |  5273K|   435   (1)| 00:00:06 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("FLAG"=:X)

--选择的是全表扫描.估计和工具显示返回行数有关,不过我打上auto trace测试结果也一样.不知道为什么无效.

--然后在sqlplus执行相同的语句: SCOTT@book> sELECT /*+ BIND_AWARE */ * from t where FLAG=:x;         ID NAME                                     F ---------- ---------------------------------------- -     100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0            xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx            xxxxxxxxxxxxxxxxxxxx

SCOTT@book> @ &r/dpc ''  '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  bh4qquz7sm25k, child number 1 ------------------------------------- sELECT /*+ BIND_AWARE */ * from t where FLAG=:x

Plan hash value: 120143814

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

| Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |   108 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |       |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1    2 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): --------------------------------------    1 - (VARCHAR2(30), CSID=852): '0' Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("FLAG"=:X)

SCOTT@book> @ &r/share bh4qquz7sm25k

SQL_TEXT                       = sELECT /*+ BIND_AWARE */ * from t where FLAG=:x SQL_ID                         = bh4qquz7sm25k ADDRESS                        = 000000007BCEEFB8 CHILD_ADDRESS                  = 000000007CA23160 CHILD_NUMBER                   = 0 REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>45</ID><reason>NLS Settings(0)</reason>                                  <size>2x4</size><SessionLengthSemantics>0</SessionLengthSemantics><CursorLengthSemantics>0                                  </CursorLengthSemantics></ChildNode> -------------------------------------------------- SQL_TEXT                       = sELECT /*+ BIND_AWARE */ * from t where FLAG=:x SQL_ID                         = bh4qquz7sm25k ADDRESS                        = 000000007BCEEFB8 CHILD_ADDRESS                  = 000000007BD006A8 CHILD_NUMBER                   = 1 LANGUAGE_MISMATCH              = Y REASON                         = -------------------------------------------------- PL/SQL procedure successfully completed.

--不知道为什么,以后研究看看把.

总之: 这个功能有比没有好,当然toad应该改进更好.