[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测试与优化时要注意这个细节。