[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測試與最佳化時要注意這個細節。