[20191213]toad 12下BIND_AWARE提示无效.txt

[20191213]toad 12下BIND_AWARE提示无效.txt --//链接http://blog.itpub.net/267265/viewspace-2130781/的测试,发现当时测试的错误.有空再次验证看看. 1.环境: SCOTT@book> @ 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. 2.测试: --//先写sql语句在toad sql编辑窗口: select  /*+ bind_aware gggg*/ * from t where flag=:x; --//然后选择执行,代入参数'0'.查询字串gggg,获取sql_id=ddgfa29wynq6d. SCOTT@book> @ dpc ddgfa29wynq6d outline PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  ddgfa29wynq6d, child number 0 ------------------------------------- select  /*+ bind_aware gggg*/ * 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 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       OPT_PARAM('_optim_peek_user_binds' 'false')       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "T"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("FLAG"=:X) --//注意看下划线,不知道为什么toad要加入修改参数'_optim_peek_user_binds' 'false',不做绑定变量peek, --//还可以一个细节就是没有获取绑定变量的值.导致提示bind_aware失效. 3.继续测试: --//取消load cached plan if possible.看到的测试结果也是一样. --//总之不知道为什么toad 12的版本为什么执行时将会话的_optim_peek_user_binds=false.而导致的提示失效. --//这个应该在优化与调试sql语句是应该引起注意. --//另外一个简单的验证就是在sqlplus执行: SCOTT@book> variable x varchar2(1) SCOTT@book> exec :x := '0'; PL/SQL procedure successfully completed. SCOTT@book> @ dpc '' outline PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8srddvbs5ydfv, child number 0 ------------------------------------- select  /*+ bind_aware OPT_PARAM('_optim_peek_user_binds' 'false') */ * 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 Outline Data -------------   /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       OPT_PARAM('_optim_peek_user_binds' 'false')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "T"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("FLAG"=:X) --//bind_aware提示无效. --//如果语句在sqlplus下先执行再在toad下观察呢? SCOTT@book> select  /*+ bind_aware pppp */ * from t where flag=:x;         ID NAME                                     F ---------- ---------------------------------------- -     100000 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 0            xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx            xxxxxxxxxxxxxxxxxxxx SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8b15sjx54pvfw, child number 0 ------------------------------------- select  /*+ bind_aware pppp */ * 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 | ----------------------------------------------------------------------------------------- --//然后在toad下执行,代入参数'0'; SQL_ID  8b15sjx54pvfw, child number 0 ------------------------------------- select  /*+ bind_aware pppp */ * 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   SQL_ID  8b15sjx54pvfw, child number 1 ------------------------------------- select  /*+ bind_aware pppp */ * 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   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       OPT_PARAM('_optim_peek_user_binds' 'false')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "T"@"SEL$1")       END_OUTLINE_DATA   */   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("FLAG"=:X)   Column Projection Information (identified by operation id): -----------------------------------------------------------      1 - "T"."ID"[NUMBER,22], "T"."NAME"[VARCHAR2,100], "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 --//你可以看到生成新的子光标.感觉toad这样设计不好,不利于生产系统调优测试.