[20250807]绑定变量peeking的问题.txt

--//昨天看了绑定变量peeking的问题，https://jonathanlewis.wordpress.com/2025/08/01/bind-peeking-2/
--//作者做了一个很有兴趣的演示，实际上只要发生查询转换，就可能出现这样的情况,自己重复演示看看。

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试环境建立：
create table t1 as select * from all_objects ;
alter table t1 add constraint t1_pk primary key(object_id);

3.测试：

variable b1 number
exec :b1 := 20

select  * from ( select object_id, object_name, object_type, owner from t1 order by object_id) where rownum <= :b1;

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  cjnhuz394pm2r, child number 0
-------------------------------------
select  * from ( select object_id, object_name, object_type, owner from
t1 order by object_id) where rownum <= :b1
Plan hash value: 3766500789
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |        |       |     4 (100)|          |
|*  1 |  COUNT STOPKEY                |       |        |       |            |          |
|   2 |   VIEW                        |       |     20 |  3160 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |  69950 |  3825K|     4   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | T1_PK |     20 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / "from$_subquery$_001"@"SEL$1"
   3 - SEL$2 / "T1"@"SEL$2"
   4 - SEL$2 / "T1"@"SEL$2"
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 20

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:B1)

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
41 rows selected.
--//可以看到绑定变量。

SCOTT@book01p> @ 10053x cjnhuz394pm2r 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3463_acjnhuz394pm2r.trc

SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."OBJECT_ID" "OBJECT_ID","from$_subquery$_001"."OBJECT_NAME" "OBJECT_NAME","from$_subquery$_
001"."OBJECT_TYPE" "OBJECT_TYPE","from$_subquery$_001"."OWNER" "OWNER" FROM  (SELECT "T1"."OBJECT_ID" "OBJECT_ID","T1"."
OBJECT_NAME" "OBJECT_NAME","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."OWNER" "OWNER" FROM "SCOTT"."T1" "T1" ORDER BY "T1"."O
BJECT_ID") "from$_subquery$_001" WHERE ROWNUM<=:B1

variable b2 number
variable b3 number

exec :b2 := 15; :b3 := 5

select  * from ( select object_id, object_name, object_type, owner from t1 order by object_id) where rownum <= :b2 + :b3 ;

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gzgba1wjq8999, child number 0
-------------------------------------
select  * from ( select object_id, object_name, object_type, owner from
t1 order by object_id) where rownum <= :b2 + :b3
Plan hash value: 270731910
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |        |       |       |  1369 (100)|          |       |       |          |
|*  1 |  COUNT STOPKEY          |      |        |       |       |            |          |       |       |          |
|   2 |   VIEW                  |      |  69950 |    10M|       |  1369   (1)| 00:00:01 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|      |  69950 |  3825K|  4672K|  1369   (1)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL   | T1   |  69950 |  3825K|       |   412   (1)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / "from$_subquery$_001"@"SEL$1"
   3 - SEL$2
   4 - SEL$2 / "T1"@"SEL$2"

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:B2+:B3)
   3 - filter(ROWNUM<=:B2+:B3)

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
37 rows selected.
--//换成2个变量执行计划就无法看到2个绑定变量值。而且因为无法看到绑定变量值，导致执行计划id=4选择全表扫描。
--//https://jonathanlewis.wordpress.com/2025/08/01/bind-peeking-2/的解析：
The reason this omission is worth pursuing shows up in an article I wrote a couple of months ago about the "fetch next"
syntax. If you use bind variables for the offset and fetch size then Oracle will rewrite your SQL to produce a predicate
which effectively reduces to: row_number() <= :offset + :fetch_size and, as with the rownum clause above, will not work
out that the sum of the two bind variables means that an indexed access path would be the best choice.

In fact, although I've chosen to demonstrate the problem with a sum of two bind variables, any arithmetic applied at
this point is enough to disable the use of peeked valued – ":b1 + 0" and ":b1 * 1" both result in a critical loss of
information even though one feels that the optimizer ought to be able to transform the constants out of the unparsed
query in these extreme cases.

SCOTT@book01p> @ 10053x gzgba1wjq8999 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3463_agzgba1wjq8999.trc

SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."OBJECT_ID" "OBJECT_ID","from$_subquery$_001"."OBJECT_NAME" "OBJECT_NAME","from$_subquery$_
001"."OBJECT_TYPE" "OBJECT_TYPE","from$_subquery$_001"."OWNER" "OWNER" FROM  (SELECT "T1"."OBJECT_ID" "OBJECT_ID","T1"."
OBJECT_NAME" "OBJECT_NAME","T1"."OBJECT_TYPE" "OBJECT_TYPE","T1"."OWNER" "OWNER" FROM "SCOTT"."T1" "T1" ORDER BY "T1"."O
BJECT_ID") "from$_subquery$_001" WHERE ROWNUM<=:B1+:B2

--//你可以这样理解当存在2个变量做运算时，oracle不知道该计算结果值的返回，只能选择忽略绑定变量peeking。

variable n2 number
variable n3 number

exec :n2 := 15; :n3 := 5

select * from dept where deptno = :n2 + :n3;

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  35njt8u5smf74, child number 0
-------------------------------------
select * from dept where deptno = :n2 + :n3
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "DEPT"@"SEL$1"
   2 - SEL$1 / "DEPT"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 15
   2 - (NUMBER): 5
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=:N2+:N3)

--//这样的情况能看到绑定变量值。

SCOTT@book01p> @ 10053x 35njt8u5smf74 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3829_a35njt8u5smf74.trc

SCOTT@book01p> @ 10053y

TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","DEPT"."LOC" "LOC" FROM "SCOTT"."DEPT" "DEPT" WHERE "DEPT"."DEPTN
O"=:B1+:B2

4.再举一个例子：
$ cat f2.txt
set term off
variable v_id number ;
exec :v_id := 76191;
set term on
select /*+ &&1 */ object_name from t1 t  where   :v_id is null or t.object_id = :v_id;

SCOTT@book01p> @ f2.txt ''
OBJECT_NAME
------------------------------
DEPT

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a879btuz0wwwu, child number 0
-------------------------------------
select /*+  */ object_name from t1 t  where   :v_id is null or
t.object_id = :v_id
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |   412 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   3498 |   140K|   412   (1)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
   2 - (NUMBER, Primary=1)
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:V_ID IS NULL OR "T"."OBJECT_ID"=:V_ID))
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
--//一样看不到绑定变量值。

SCOTT@book01p> @ 10053x a879btuz0wwwu 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3658_aa879btuz0wwwu.trc

SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T1" "T" WHERE :B1 IS NULL OR "T"."OBJECT_ID"=:B2

$ cat f2a.txt
set term off
variable v_id number ;
exec :v_id := 76191;
set term on
select /*+ &&1 */ object_name from t1 t  where  t.object_id = :v_id or :v_id is null ;
--//仅仅将2个or交换一下。

SCOTT@book01p> @ f2a.txt ''

OBJECT_NAME
------------------------------
DEPT

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b4hqfmf3khhu8, child number 0
-------------------------------------
select /*+  */ object_name from t1 t  where  t.object_id = :v_id or
:v_id is null
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |       |   412 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   3498 |   140K|   412   (1)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 76191
   ~~~~~~~~~~~~~~~~~~~
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:V_ID IS NULL OR "T"."OBJECT_ID"=:V_ID))
--//这种情况就能看到绑定变量值。

SCOTT@book01p> @ 10053x b4hqfmf3khhu8 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3658_ab4hqfmf3khhu8.trc

SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T1" "T" WHERE "T"."OBJECT_ID"=:B1 OR :B2 IS NULL
--//不知道是否可以这样理解，原来的一个变量:v_id 变成了2个变量 :B1 ,:B2. oracle仅仅传给了:B1,这样第2种写法就会peek。
--//而第1种写法查询条件是WHERE :B1 IS NULL OR "T"."OBJECT_ID"=:B2 不知道:b2的值，peeking失效。

--//加入提示or_expand看看：
SCOTT@book01p> @ f2.txt 'or_expand'
OBJECT_NAME
------------------------------
DEPT

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g45nraa1syn5f, child number 0
-------------------------------------
select /*+ or_expand */ object_name from t1 t  where   :v_id is null or
t.object_id = :v_id
Plan hash value: 2746111223
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |        |       |   413 (100)|          |
|   1 |  VIEW                          | VW_ORE_1B35BA0F |  69951 |  4508K|   413   (1)| 00:00:01 |
|   2 |   UNION-ALL                    |                 |        |       |            |          |
|*  3 |    FILTER                      |                 |        |       |            |          |
|   4 |     TABLE ACCESS FULL          | T1              |  69950 |  2459K|   411   (1)| 00:00:01 |
|*  5 |    FILTER                      |                 |        |       |            |          |
|   6 |     TABLE ACCESS BY INDEX ROWID| T1              |      1 |    41 |     2   (0)| 00:00:01 |
|*  7 |      INDEX UNIQUE SCAN         | T1_PK           |      1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$2A13AF86   / "VW_ORE_1B35BA0F"@"SEL$1B35BA0F"
   2 - SET$2A13AF86
   3 - SET$2A13AF86_1
   4 - SET$2A13AF86_1 / "T"@"SET$2A13AF86_1"
   5 - SET$2A13AF86_2
   6 - SET$2A13AF86_2 / "T"@"SET$2A13AF86_2"
   7 - SET$2A13AF86_2 / "T"@"SET$2A13AF86_2"

Peeked Binds (identified by position):
--------------------------------------
   2 - (NUMBER, Primary=1)
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:V_ID IS NULL)
   5 - filter(LNNVL(:V_ID IS NULL))
   7 - access("T"."OBJECT_ID"=:V_ID)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   5 -  SET$2A13AF86_2
           -  or_expand
--//一样看不见绑定变量值。

SCOTT@book01p> @ 10053x g45nraa1syn5f 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3658_ag45nraa1syn5f.trc

SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_ORE_1B35BA0F"."ITEM_1" "OBJECT_NAME" FROM  ( (SELECT "T"."OBJECT_NAME" "ITEM_1" FROM "SCOTT"."T1" "T" WHERE :
B1 IS NULL) UNION ALL  (SELECT "T"."OBJECT_NAME" "ITEM_1" FROM "SCOTT"."T1" "T" WHERE "T"."OBJECT_ID"=:B2 AND LNNVL(:B3
IS NULL))) "VW_ORE_1B35BA0F"
--//展开2个union all。

SCOTT@book01p> @ f2a.txt 'or_expand'
OBJECT_NAME
------------------------------
DEPT

SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gmx4vmyrmzs8s, child number 0
-------------------------------------
select /*+ or_expand */ object_name from t1 t  where  t.object_id =
:v_id or :v_id is null
Plan hash value: 4114514004
--------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |        |       |   414 (100)|          |
|   1 |  VIEW                         | VW_ORE_1B35BA0F |  69950 |  4508K|   414   (1)| 00:00:01 |
|   2 |   UNION-ALL                   |                 |        |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1              |      1 |    41 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | T1_PK           |      1 |       |     1   (0)| 00:00:01 |
|*  5 |    FILTER                     |                 |        |       |            |          |
|*  6 |     TABLE ACCESS FULL         | T1              |  69949 |  2800K|   412   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$2A13AF86   / "VW_ORE_1B35BA0F"@"SEL$1B35BA0F"
   2 - SET$2A13AF86
   3 - SET$2A13AF86_1 / "T"@"SET$2A13AF86_1"
   4 - SET$2A13AF86_1 / "T"@"SET$2A13AF86_1"
   5 - SET$2A13AF86_2
   6 - SET$2A13AF86_2 / "T"@"SET$2A13AF86_2"
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 76191
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T"."OBJECT_ID"=:V_ID)
   5 - filter(:V_ID IS NULL)
   6 - filter(LNNVL("T"."OBJECT_ID"=:V_ID))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

   5 -  SET$2A13AF86_2
           -  or_expand
--//这次可以看到。

SCOTT@book01p> @ 10053x gmx4vmyrmzs8s 0
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3658_agmx4vmyrmzs8s.trc

SCOTT@book01p> @ 10053y ''
TRCLINE
------------------------------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_ORE_1B35BA0F"."ITEM_1" "OBJECT_NAME" FROM  ( (SELECT "T"."OBJECT_NAME" "ITEM_1" FROM "SCOTT"."T1" "T" WHERE "
T"."OBJECT_ID"=:B1) UNION ALL  (SELECT "T"."OBJECT_NAME" "ITEM_1" FROM "SCOTT"."T1" "T" WHERE :B2 IS NULL AND LNNVL("T".
"OBJECT_ID"=:B3))) "VW_ORE_1B35BA0F"

5.小结：
--//我以前总以为出现查询转换就有可能出现这类情况。实际上一些情况相对复杂，具体的细节还是很不清楚。
--//总之以后做sql优化，要注意这个细节，查看执行计划的Peeked Binds (identified by position)部分。

6.附上测试使用sql语句代码：

$ cat 10053x.sql
set term off
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');
set term on
set head off
@ t
set head on
define 1=&trc

$ cat 10053y.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
------------------------------------------------------------------------------------------------------------
--
-- File name:   10053y.sql
-- Purpose:     display Final query after transformations
--
-- Author:      lfree
--
-- Usage:
--     @ 10053y <trc_file>
--
-------------------------------------------------------------------------------------------------------------

SET TERM OFF
COLUMN trc_file  NEW_VALUE v_trc_file
--DEFINE trc_file = &1
SELECT NVL('&1','&TRC') trc_file FROM DUAL ;
SELECT SUBSTR ('&v_trc_file', INSTR ('&v_trc_file', '/', -1) + 1) trc_file FROM DUAL;
SET TERM ON

DEFINE trc_file = &v_trc_file
--DEFINE trc_file = &1

--COL trace_filename FOR A45
--COL adr_home FOR A45
--SELECT trace_filename, to_char(change_time, 'dd-mm-yyyy hh24:mi:ss') AS change_time, to_char(modify_time, 'dd-mm-yyyy hh24:mi:ss') AS modify_time, adr_home, con_id
--FROM gv$diag_trace_file
--WHERE lower(trace_filename) LIKE lower('%&v_trc_file%')
--ORDER BY modify_time;

column trcline format a120
SELECT trcline
  FROM gv$diag_trace_file_contents
          MATCH_RECOGNIZE
          (
             PARTITION BY trace_filename
             ORDER BY line_number
             MEASURES payload AS trcline
             ALL ROWS PER MATCH
             PATTERN (a | b nc * | c | f n)
             DEFINE a AS (payload LIKE 'qksptfSQM_GetTxt(): Anonymous Block%')
            ,b AS (payload LIKE 'qksptfSQM_GetTxt(): Macro Text%')
            ,nc AS (payload NOT LIKE 'qksptfSQM_Template(): Template Text%')
            ,c AS (payload LIKE 'qksptfSQM_Template(): Template Text%')
            ,f AS (payload LIKE 'Final query after%')
          )
          x
 WHERE trace_filename = '&v_trc_file';

$ cat dpc.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- File name:   dpc.sql
-- Purpose:     display sql of plan
--
-- Author:      lfree
--
-- Usage:
--     @ dpc <sql_id> <format> <child_number>
--     argument1=sql_id argument2=format argument3=child_number
--------------------------------------------------------------------------------

--set term off
--column 3 new_value 3
--select null "3" from dual where 1=2;
--set term on

--variable a varchar2(10)
--exec :a := nvl ('&3',null)

-- argument1=sql_id argument2=format argument3=child_number
set verify off
--select * from table(dbms_xplan.display_cursor(nvl('&1',null),null,'all allstats last peeked_binds cost partition note -projection -outline &2'));
select * from table(dbms_xplan.display_cursor(nvl('&1',null),nvl('&3',null),'all allstats last peeked_binds cost partition note -projection -outline &2'));
def 3=''