[20250109]dbms_xplan.display_cursor+peeked_binds无法查看绑定变量值.txt

来源：这里教程网时间：2026-03-03 21:17:30 作者：

[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

编辑推荐：

返回列表

[20250109]dbms_xplan.display_cursor+peeked_binds无法查看绑定变量值.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推