[20250109]19c使用or_expand提示遇到的问题.txt
--//生产系统使用19c,在使用or_expand提示时遇到的问题，在测试环境演示并做分析。
1.环境：
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 f3.txt
set term off
variable v_id number ;
variable v_name varchar2(32) ;
exec :v_id := 76191;
exec :v_name := NULL;
set term on
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)
;
--//我们团队开发一种常见写法!!
--//优化很简单，加入or_expand提示，复杂的话要加入Query Block Name。
$ cat f3a.txt
set term off
variable v_id number ;
variable v_name varchar2(32) ;
exec :v_id := 76191;
exec :v_name := NULL;
set term on
select /*+ or_expand */ 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)
;
--//分别执行，记录各自的sql_id，然后使用spsw.sql脚本完成交换执行计划的outline。
SCOTT@book01p> @ spsw 32dn17b91frjg 0 1z3f571t20s0u 0 '' true
PL/SQL procedure successfully completed.
=================================================================================================================================================
if drop or alter sql profile ,run :
execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 1z3f571t20s0u')
execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 1z3f571t20s0u',attribute_name=>'STATUS',value=>'DISABLED')
=================================================================================================================================================
3.交换后测试：
SCOTT@book01p> @ f3.txt
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEPT TABLE
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1z3f571t20s0u, child number 0
-------------------------------------
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)
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 | 175 | 8925 | 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"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(((:V_NAME IS NULL OR "T"."OBJECT_NAME"=:V_NAME) AND (:V_ID IS NULL OR "T"."OBJECT_ID"=:V_ID)))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 8 (U - Unused (1), N - Unresolved (3))
---------------------------------------------------------------------------
0 - STATEMENT
- ALL_ROWS
- DB_VERSION('21.1.0')
- IGNORE_OPTIM_EMBEDDED_HINTS
- OPTIMIZER_FEATURES_ENABLE('21.1.0')
0 - SET$2A13AF86_1
N - FULL(@"SET$2A13AF86_1" "T"@"SET$2A13AF86_1")
0 - SET$2A13AF86_2
N - BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A13AF86_2" "T"@"SET$2A13AF86_2")
N - INDEX_RS_ASC(@"SET$2A13AF86_2" "T"@"SET$2A13AF86_2" ("T"."OBJECT_ID"))
1 - SEL$1
U - OR_EXPAND(@"SEL$1" (1) (2))
Note
-----
- SQL profile switch tuning 1z3f571t20s0u used for this statement
--//可以发现并没有使用交换的执行计划，提示U - OR_EXPAND(@"SEL$1" (1) (2))。
--//我看了以前sql profile脚本失效的相关笔记,遇到这类情况选择sql patch简单一些。
SYS@book01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 1z3f571t20s0u')
PL/SQL procedure successfully completed.
SYS@book01p> @ sqlpatch 1z3f571t20s0u 'OR_EXPAND(@"SEL$1" )'
input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_1z3f571t20s0u');
display sql path message , run @spext 1z3f571t20s0u
PL/SQL procedure successfully completed.
--//以sys用户执行
--//注：尝试使用@ sqlpatch 1z3f571t20s0u 'OR_EXPAND(@"SEL$1" (1) (2) )'不行。
SCOTT@book01p> @ f3.txt
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEPT TABLE
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1z3f571t20s0u, child number 0
-------------------------------------
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)
Plan hash value: 2282446254
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 414 (100)| | 1 |00:00:00.01 | 4 |
| 1 | VIEW | VW_ORE_1B35BA0F | 1 | 3500 | 270K| 414 (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 | 3499 | 157K| 412 (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 | 51 | 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)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:V_ID IS NULL)
4 - filter((:V_NAME IS NULL OR "T"."OBJECT_NAME"=:V_NAME))
5 - filter(LNNVL(:V_ID IS NULL))
6 - filter((:V_NAME IS NULL OR "T"."OBJECT_NAME"=:V_NAME))
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(@"SEL$1" )
Note
-----
- SQL patch "sqlpatch_1z3f571t20s0u" used for this statement
4.继续：
--//正常情况下object_name字段应该有索引，这里不用，也许其他sql语句要使用，建立object_name索引看看。
SCOTT@book01p> create index i_t_object_name on t(object_name);
Index created.
SCOTT@book01p> @ f3.txt
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEPT TABLE
SCOTT@book01p> @ dpc '' outline ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1z3f571t20s0u, child number 0
-------------------------------------
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)
Plan hash value: 3783172993
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 418 (100)| | 1 |00:00:00.01 | 4 |
| 1 | VIEW | VW_ORE_1B35BA0F | 1 | 69985 | 5399K| 418 (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 | 3143K| 412 (1)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 5 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 0 | 1 | 46 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 7 | INDEX RANGE SCAN | I_T_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 8 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 51 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 |
|* 10 | INDEX RANGE SCAN | I_T_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 11 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 0 | 1 | 51 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 13 | INDEX RANGE SCAN | I_T_OBJECT_NAME | 0 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$171C43EC / "VW_ORE_1B35BA0F"@"SEL$1B35BA0F"
2 - SET$171C43EC
3 - SET$171C43EC_1
4 - SET$171C43EC_1 / "T"@"SET$171C43EC_1"
5 - SET$171C43EC_2
6 - SET$171C43EC_2 / "T"@"SET$171C43EC_2"
7 - SET$171C43EC_2 / "T"@"SET$171C43EC_2"
8 - SET$171C43EC_3
9 - SET$171C43EC_3 / "T"@"SET$171C43EC_3"
10 - SET$171C43EC_3 / "T"@"SET$171C43EC_3"
11 - SET$171C43EC_4
12 - SET$171C43EC_4 / "T"@"SET$171C43EC_4"
13 - SET$171C43EC_4 / "T"@"SET$171C43EC_4"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('21.1.0')
DB_VERSION('21.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SET$171C43EC_4")
OUTLINE_LEAF(@"SET$171C43EC_3")
OUTLINE_LEAF(@"SET$171C43EC_2")
OUTLINE_LEAF(@"SET$171C43EC_1")
OUTLINE_LEAF(@"SET$171C43EC")
OUTLINE_LEAF(@"SEL$47D9A6EC")
OR_EXPAND(@"SEL$1" (1) (2) (3) (4))
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$47D9A6EC" "VW_ORE_1B35BA0F"@"SEL$1B35BA0F")
FULL(@"SET$171C43EC_1" "T"@"SET$171C43EC_1")
INDEX_RS_ASC(@"SET$171C43EC_2" "T"@"SET$171C43EC_2" ("T"."OBJECT_NAME"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_2" "T"@"SET$171C43EC_2")
INDEX_RS_ASC(@"SET$171C43EC_3" "T"@"SET$171C43EC_3" ("T"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_3" "T"@"SET$171C43EC_3")
INDEX_RS_ASC(@"SET$171C43EC_4" "T"@"SET$171C43EC_4" ("T"."OBJECT_NAME"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SET$171C43EC_4" "T"@"SET$171C43EC_4")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
3 - (NUMBER, Primary=1)
6 - (VARCHAR2(30), CSID=852, Primary=4)
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((:V_NAME IS NULL AND :V_ID IS NULL))
5 - filter((LNNVL(:V_NAME IS NULL) AND :V_ID IS NULL))
7 - access("T"."OBJECT_NAME"=:V_NAME)
8 - filter((LNNVL(:V_ID IS NULL) AND :V_NAME IS NULL))
10 - access("T"."OBJECT_ID"=:V_ID)
11 - filter((LNNVL(:V_NAME IS NULL) AND LNNVL(:V_ID IS NULL)))
12 - filter("T"."OBJECT_ID"=:V_ID)
13 - access("T"."OBJECT_NAME"=:V_NAME)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
11 - SET$171C43EC_4
- OR_EXPAND(@"SEL$1" )
Note
-----
- SQL patch "sqlpatch_1z3f571t20s0u" used for this statement
101 rows selected.
--//outline记录的是 OR_EXPAND(@"SEL$1" (1) (2) (3) (4))
--//视乎这样的情况oracle使用or_expand更加智能，完美的展开各种条件，可惜无法在11.2.0.4上使用，11g只能使用use_concat.
--//现在有点明白为什么使用我写的spsw.sql交换执行计划不行，因为这部分内容会变化，只能使用sql patch方式稳定执行计划。
5.附上测试使用的sqlpatch.sql脚本：
$ cat sqlpatch.sql
prompt
prompt input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12)
prompt drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');;
prompt display sql path message , run @spext &1
define noprint='noprint'
set term off
col tpt_version_old &noprint new_value _tpt_version_old
col tpt_version_new &noprint new_value _tpt_version_new
col tpt_noprint &noprint new_value _tpt_noprint
WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
,CASE WHEN v > 11 THEN '' ELSE '--' END tpt_version_new
FROM version;
set term on
declare
v_sql CLOB;
patch_name VARCHAR2 (100);
begin
select sql_fulltext into v_sql from gv$sqlarea where sql_id='&1' and rownum=1;
-- select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='&1' and rownum=1;
&&_tpt_version_old sys.dbms_sqldiag_internal.i_create_patch(
&&_tpt_version_old sql_text => v_sql,
&&_tpt_version_old hint_text => '&2',
&&_tpt_version_old name => 'sqlpatch_&1');
&&_tpt_version_new patch_name :=
&&_tpt_version_new sys.DBMS_SQLDIAG.create_sql_patch
&&_tpt_version_new (
&&_tpt_version_new sql_text => v_sql
&&_tpt_version_new ,hint_text => '&2'
&&_tpt_version_new ,name => 'sqlpatch_&1'
&&_tpt_version_new );
end;
/