[20191213]toad 12下BIND_AWARE提示无效.txt
--//链接http://blog./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这样设计不好,不利于生产系统调优测试.
|