[20250109]19c使用or_expand提示遇到的问题.txt

[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;
/


posted @   lfree  阅读(2)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
点击右上角即可分享
微信分享提示