[20250109]dbms_xplan.display_cursor+peeked_binds无法查看绑定变量值.txt
--//在我使用自己写的dpc.sql脚本中我会加入peeked_binds参数查看绑定变量值，但是有时候会遇到无法查看的情况。
--//以前自己很少关注这个细节，应该有别的途径获取绑定变量值，最近在优化一条sql语句正好遇到，做一个例子，演示自己遇到的情
--//况，做一个简单探究。
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.建立测试环境：
SCOTT@book01p> create table t as select * from all_objects;
Table created.
SCOTT@book01p> create index I_T_OBJECT_ID on t(object_id);
Index created.
--//分析略。
--//建立测试脚本：
$ cat f2.txt
set term off
variable v_o_id number ;
exec :v_id := 76191;
set term on
select object_name from t where ( ( :v_id = '' or :v_id is null) or t.object_id = :v_id);
--//顺便提一下，我们团队开发或者一些开发人员喜欢这样写法，我再次提醒不要这样写sql代码，oracle目前没有这么智能，分开两者
--//情况考虑。几条问题还不大，一大堆数据库总有一天会垮掉。
3.测试：
SCOTT@book01p> @ f2.txt
OBJECT_NAME
------------------------------
DEPT
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 48hvkrha9qvzr, child number 0
-------------------------------------
select object_name from t where ( ( :v_id = '' or :v_id is null) or
t.object_id = :v_id)
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 412 (100)| | 1 |00:00:00.01 | 1482 |
|* 1 | TABLE ACCESS FULL| T | 1 | 3500 | 140K| 412 (1)| 00:00:01 | 1 |00:00:00.01 | 1482 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T"@"SEL$1"
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
~~~~~~~~~~~~~~~~~~~~~~~
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:V_ID IS NULL OR "T"."OBJECT_ID"=:V_ID))
--//注意看下划线，可以发现无法看到绑定变量值，为什么？
SCOTT@book01p> @ bind_cap 48hvkrha9qvzr ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING INST_ID
------------- ------------ --- ------ -------- ---------- ------------------- --------------- ------------ -------
48hvkrha9qvzr 0 YES :V_ID 1 22 2025-01-09 08:41:59 NUMBER 76191 1
--//查询我写的bind_cap查询gv$sql_bind_capture可以获得绑定变量值。为什么执行计划里面无法看到呢？
SCOTT@book01p> @ expand_sql_text 48hvkrha9qvzr
SELECT "A1"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T" "A1" WHERE :B1='' OR :B2 IS NULL OR "A1"."OBJECT_ID"=:B3
PL/SQL procedure successfully completed.
SCOTT@book01p> @ 10053x 48hvkrha9qvzr 0
PL/SQL procedure successfully completed.
SCOTT@book01p> @ t
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3853_a48hvkrha9qvzr.trc
SCOTT@book01p> @ 10053y ''
TRCLINE
----------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T" "T" WHERE :B1 IS NULL OR "T"."OBJECT_ID"=:B2
--//使用dbms_utility.expand_sql_text展开sql语句或者使用dbms_sqldiag.dump_trace可以发现sql语句发生了转换。
--//虽然两者不同，但是使用 :B1 :B2表示
--//变为如下：
SELECT "T"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T" "T" WHERE :B1 IS NULL OR "T"."OBJECT_ID"=:B2
--//也许正是这个原因导致执行dpc.sql看不见绑定变量值。
4.换一个方式执行：
$ cat f2a.txt
set term off
variable v_id number ;
exec :v_id := 76191;
set term on
select object_name from t where (t.object_id = :v_id or ( :v_id = '' or :v_id is null));
--//谓词条件做一下交换。
SCOTT@book01p> @ f2a.txt
OBJECT_NAME
------------------------------
DEPT
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bxvuhvqxz0dg5, child number 0
-------------------------------------
select object_name from t where (t.object_id = :v_id or ( :v_id = ''
or :v_id is null))
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 412 (100)| | 1 |00:00:00.01 | 1482 |
|* 1 | TABLE ACCESS FULL| T | 1 | 3500 | 140K| 412 (1)| 00:00:01 | 1 |00:00:00.01 | 1482 |
--------------------------------------------------------------------------------------------------------------------
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))
29 rows selected.
--//这次可以看到其中的绑定变量值。
SCOTT@book01p> @ expand_sql_text bxvuhvqxz0dg5
SELECT "A1"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T" "A1" WHERE "A1"."OBJECT_ID"=:B1 OR :B2='' OR :B3 IS NULL
PL/SQL procedure successfully completed.
SCOTT@book01p> @ 10053x bxvuhvqxz0dg5 0
PL/SQL procedure successfully completed.
SCOTT@book01p> @ t
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3853_abxvuhvqxz0dg5.trc
SCOTT@book01p> @ 10053y ''
TRCLINE
----------------------------------------------------------------------------------------------------
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."OBJECT_NAME" "OBJECT_NAME" FROM "SCOTT"."T" "T" WHERE "T"."OBJECT_ID"=:B1 OR :B2 IS NULL
--//虽然不知道为什么交换位置后，"T"."OBJECT_ID"=:B1在前就可以看到绑定变量值，但至少是一个线索。
5.如何优化：
--//可以发现执行计划都是全表扫描，这么简单的sql语句oracle都没有这么智能拆分2部分执行，何况生产系统的sql语句比这复杂的多。
--//开发应该醒一醒，不能再写这样的代码，这样写一辈子代码，永远被别人鄙视。
--//可以使用use_concat 或者 or_expand提示，12c以上推荐尝试or_expand。
$ cp f2.txt f2b.txt
--//修改f2b.txt 加入提示。
$ cat f2b.txt
set term off
variable v_id number ;
exec :v_id := 76191;
set term on
select
/*+ or_expand */
object_name from t where ( ( :v_id = '' or :v_id is null) or t.object_id = :v_id);
SCOTT@book01p> @ f2b.txt
OBJECT_NAME
------------------------------
DEPT
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID dy5z2h31r5cyc, child number 0
-------------------------------------
select /*+ or_expand */ object_name from t where ( ( :v_id = '' or
:v_id is null) or t.object_id = :v_id)
Plan hash value: 371444588
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 413 (100)| | 1 |00:00:00.01 | 4 |
| 1 | VIEW | VW_ORE_1B35BA0F | 1 | 69983 | 4510K| 413 (1)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 4 |
|* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
| 4 | TABLE ACCESS FULL | T | 0 | 69982 | 2460K| 411 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | I_T_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------------------------------------
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):
--------------------------------------
3 - (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
50 rows selected.
--//可以发现被拆分2部分，不要看id=4，starts=0，除非带入空值。
--//交换执行计划：
SCOTT@book01p> @ spsw dy5z2h31r5cyc 0 48hvkrha9qvzr 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 48hvkrha9qvzr')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 48hvkrha9qvzr',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
SCOTT@book01p> @ f2.txt
OBJECT_NAME
------------------------------
DEPT
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 48hvkrha9qvzr, child number 0
-------------------------------------
select object_name from t where ( ( :v_id = '' or :v_id is null) or
t.object_id = :v_id)
Plan hash value: 371444588
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 413 (100)| | 1 |00:00:00.01 | 4 |
| 1 | VIEW | VW_ORE_1B35BA0F | 1 | 69983 | 4510K| 413 (1)| 00:00:01 | 1 |00:00:00.01 | 4 |
| 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 4 |
|* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
| 4 | TABLE ACCESS FULL | T | 0 | 69982 | 2460K| 411 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 41 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | I_T_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------------------------------------
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):
--------------------------------------
3 - (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: 8
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('21.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('21.1.0')
4 - SET$2A13AF86_1 / "T"@"SET$2A13AF86_1"
- FULL(@"SET$2A13AF86_1" "T"@"SET$2A13AF86_1")
5 - SET$2A13AF86_2
- OR_EXPAND(@"SEL$1" (1) (2))
6 - SET$2A13AF86_2 / "T"@"SET$2A13AF86_2"
- BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A13AF86_2" "T"@"SET$2A13AF86_2")
- INDEX_RS_ASC(@"SET$2A13AF86_2" "T"@"SET$2A13AF86_2" ("T"."OBJECT_ID"))
Note
-----
- SQL profile switch tuning 48hvkrha9qvzr used for this statement
67 rows selected.
--//这样就可以稳定执行计划，仅仅能稳定一路，真正的应用sql语句会存在多个，目前的情况是仅仅稳定1路。其他情况还是全表扫描。
6.小结：
--//虽然还是不知道为什么dpc.sql看不见绑定变量的具体原因，至少知道与转换相关。
--//另外补充说明使用sql profile稳定执行计划，如果条件复杂，出现如下：
select object_name,object_type from t where
( ( :v_id = '' or :v_id is null) or t.object_id = :v_id)
and ( ( :v_name = '' or :v_name is null) or t.object_name = :v_name)
;
--//无法通过spsw.sql来稳定执行计划，我的测试要使用sql patch。
7.顺便附上测试中使用的相关sql脚本：
$ 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=''
@ dpcformat.sql
$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');
$ cat tpt/t.sql
SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File';
$ 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 a250
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 bind_cap.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
----------------------------------------------------------------------------------------------
--
-- Name: bind_cap.sql
-- Purpose: Display for binding variable types as well as for data information
--
-- Author: lfree
-- Usage:
-- @ bind_cap <sql_id> <column_name>
--
-----------------------------------------------------------------------------------------------
set verify off
column value_string format a50
column datatype_string format a15
break on sql_id on child_number skip 1
select replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;
SELECT sql_id
,child_number
,was_captured
,name
,position
,max_length
,last_captured
,datatype_string
,DECODE
(
datatype_string
,'DATE', TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss')
,'TIMESTAMP', TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')
,value_string
)
value_string
, inst_id
-- decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30
FROM gv$sql_bind_capture
WHERE sql_id = '&1'
AND was_captured = 'YES'
AND DUP_POSITION IS NULL
AND LOWER (name) LIKE LOWER ('%' || NVL ('&&2', name) || '%')
ORDER BY child_number, inst_id,was_captured, position;
clear break
$ cat expand_sql_text.sql
set long 20000
set serveroutput on
declare
L_sqltext clob := null;
l_version varchar2(3) := null;
l_sql clob := null;
l_result clob := null;
begin
select regexp_replace(version,'\..*') into l_version from v$instance;
select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1';
if l_version = '11' then
l_sql := 'begin
dbms_sql2.expand_sql_text( :a,:b );
end;';
elsif l_version >= '12' then
l_sql := 'begin
dbms_utility.expand_sql_text(:a,:b);
end;';
end if;
execute immediate l_sql using in l_sqltext,out l_result;
dbms_output.put_line(l_result);
end;
/
set serveroutput off
$ cat spsw.sql
-- @spsw good_sql_id 0 bad_sql_id 0 test true
-- @spsw good_sql_id 0 bad_sql_id 0 '' true
DECLARE
ar_profile_hints SYS.sqlprof_attr;
cl_sql_text CLOB;
BEGIN
SELECT EXTRACTVALUE (VALUE (d), '/hint') AS outline_hints
BULK COLLECT INTO ar_profile_hints
FROM XMLTABLE (
'/*/outline_data/hint'
PASSING (SELECT xmltype (other_xml) AS xmlval
FROM v$sql_plan
WHERE sql_id = '&&1'
AND child_number = &&2
AND other_xml IS NOT NULL)) d;
SELECT SQL_FULLTEXT
INTO cl_sql_text
FROM -- replace with dba_hist_sqltext
-- if required for AWR based
-- execution
gv$sqlarea
-- sys.dba_hist_sqltext
WHERE sql_id = '&&3'and rownum=1;
-- plan_hash_value = &&2;
DBMS_SQLTUNE.import_sql_profile (sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&5',
DESCRIPTION => 'switch &&1 => &&3',
name => 'switch tuning &&3' -- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
,
force_match => &&6);
END;
/
prompt =================================================================================================================================================
prompt if drop or alter sql profile ,run :
prompt execute dbms_sqltune.drop_sql_profile(name => 'switch tuning &&3')
prompt execute dbms_sqltune.alter_sql_profile(name => 'switch tuning &&3',attribute_name=>'STATUS',value=>'DISABLED')
prompt =================================================================================================================================================
prompt
prompt