[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
来源链接：https://www.cnblogs.com/lfree/p/18662857
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容