[20191213]toad12下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这样设计不好,不利于生产系统调优测试.
以上是 [20191213]toad12下BIND_AWARE提示无效.txt 的全部内容， 来源链接： utcz.com/z/531776.html
