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