[20220414]toad与绑定变量peek.txt

[20220414]toad与绑定变量peek.txt --//有时候我会在toad下使用编辑sql语句，实际上我更多的使用schema browser，毕竟图形界面操作的显示比文本界面直观。 --//但是不知道从那个版本开始oracle执行sql语句自动关闭绑定变量peek，导致一些优化无法在toad界面下执行测试。 --//我很想通过提示控制打开绑定变量peek，不行，不知道toad如何实现的。通过例子说明： 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 2.分析： select /*+ find_me */ * from dept where dname =:a; --//sql_id=8rkykratyxjcf SCOTT@book> @ dpc 8rkykratyxjcf outline '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8rkykratyxjcf, child number 0 ------------------------------------- select /*+ find_me */ * from dept where dname =:a Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |    20 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@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" "DEPT"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("DNAME"=:A) --//你可以发现它自动加入提示里面_optim_peek_user_binds=false. --//如果你跟踪它的执行并没有用户会话里面设置这个参数，如果在toad界面执行 @ tpt/pd _optim_peek_user_binds 未选择任何行。 --//竟然没有返回行。为什么另外写一篇blog分析该问题。 --//或者在toad下执行如下： select    n.indx + 1 num  , to_char(n.indx + 1, 'XXXX') n_hex  , n.ksppinm pd_name  , c.ksppstvl pd_value  , n.ksppdesc pd_descr from sys.x$ksppi n, sys.x$ksppcv c where n.indx=c.indx and (    lower(n.ksppinm) || ' ' || lower(n.ksppdesc) like lower('%_optim_peek_user_binds%') --   or lower(n.ksppdesc) like lower('"%_optim_peek_user_binds%"') );        NUM N_HEX PD_NAME                PD_VALUE PD_DESCR                                                                         ---------- ----- ---------------------- -------- ----------------------------       2180   884 _optim_peek_user_binds TRUE     enable peeking of user binds                                                                                                                                      已选择 1 行。 --//可以发现toad缺省也是设置_optim_peek_user_binds=true. 3.继续： --//如果加入提示： select /*+ OPT_PARAM('_optim_peek_user_binds' 'true') */ * from dept where dname =:a; --//sql_id=1jzaar4wqdra8 SCOTT@book> @ dpc 1jzaar4wqdra8 outline '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  1jzaar4wqdra8, child number 0 ------------------------------------- select /*+ OPT_PARAM('_optim_peek_user_binds' 'true') */ * from dept where dname =:a Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |    20 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@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" "DEPT"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("DNAME"=:A) --//你可以发现我手工加入的提示无效，toad自动设置OPT_PARAM('_optim_peek_user_binds' 'false'),这导致一些sql语句优化我只能 --//带入实际值来测试。 select /*+ OPT_PARAM('_optim_peek_user_binds' 'true') OPT_PARAM('optimizer_index_caching',1)  */ * from dept where dname =:a; --//sql_id=3zkau33ww0hdy. SCOTT@book> @ dpc 3zkau33ww0hdy outline '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  3zkau33ww0hdy, child number 0 ------------------------------------- select /*+  OPT_PARAM('_optim_peek_user_binds' 'true') OPT_PARAM('optimizer_index_caching',1)  */ * from dept where dname =:a Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     3 (100)|          | |*  1 |  TABLE ACCESS FULL| DEPT |      1 |    20 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / DEPT@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')       OPT_PARAM('optimizer_index_caching' 1)       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "DEPT"@"SEL$1")       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("DNAME"=:A) --//你可以发现OPT_PARAM('optimizer_index_caching',1) 提示有效，但是toad自动修改_optim_peek_user_binds=false. --//不知道toad如何实现这个功能的。 --//如果直接带入值执行： select /*+ OPT_PARAM('_optim_peek_user_binds' 'true') OPT_PARAM('optimizer_index_caching',1)  */ * from dept where dname ='a'; Outline Data  /*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       DB_VERSION('11.2.0.4')       OPT_PARAM('optimizer_index_caching' 1)       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       FULL(@"SEL$1" "DEPT"@"SEL$1")       END_OUTLINE_DATA   */ --//只要没有绑定变量，toad的outline就没有OPT_PARAM('_optim_peek_user_binds' 'false')提示。 4.总结： --//toad下执行调式sql语句要注意这个细节。视乎从12.0版本开始就这样设计，不知道它如何实现的。 --//找了一个旧版本TOAD 9.6.0.27 测试(32位版本). select /*+ FINd_me */ * from dept where dname =:a; 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")       FULL(@"SEL$1" "DEPT"@"SEL$1")       END_OUTLINE_DATA   */ --//并没有这样的情况出现，总之在toad测试与优化时要注意这个细节。