​[20210929]sql打补丁使用rule提示问题.txt

[20210929]sql打补丁使用rule提示问题.txt --//前几天看awr的报表.如下: SQL ordered by Sharable Memory Only Statements with Sharable Memory greater than 1048576 are displayed Sharable Mem (b) Executions  % Total SQL Id         SQL Module SQL Text 205,589,416                     1.51 7ksrtc8rzpawc             SELECT a.object_name, c.sid, C... 117,101,264                     0.86 5r14h528vkacs             select to_char(min(start_time)... --//占用共享内存主要原因是子光标太多,一般有几种方法解决,使用sql profile或者给语句打补丁稳定执行计划,对于这类问题,我个人 --//喜欢使用sql profile来稳定执行计划.这次我选择打补丁的方式看看,不过在实施时遇到使用rule提示无效的问题. --//在测试环境重复测试看看. --//在测试前更正以前blog的错误.我以前使用cursor_sharing_exact提示时多写了一个),不过不影响当时的测试,也就是提示是有效的. --//参考链接: [20201127]sql打补丁问题.txt =>http://blog.itpub.net/267265/viewspace-2737425/ [20201126]使用cursor_sharing_exact与给sql打补丁.txt [20201126]使用cursor_sharing_exact与给sql打补丁2.txt [20201126]使用cursor_sharing_exact与给sql打补丁3.txt [20201127]使用cursor_sharing_exact与给sql打补丁4实战篇.txt declare    v_sql CLOB; begin    select distinct sql_text into v_sql from v$sql where sql_id='&sql_id';    sys.dbms_sqldiag_internal.i_create_patch(       sql_text  => v_sql,       hint_text => 'cursor_sharing_exact)',       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~       name      => 'user_extents_patch'); end; / --//注:多写了一个括号.如果在vim下注意颜色的显示就知道自己当时写的有点问题. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table t as select rownum id ,lpad('a',100,'a') vc,'1' flag from dual connect by level<=1e3; insert into t values (1e4+1,lpad('a',100,'a'),'0'); commit ; create index i_t_flag on t(flag); execute sys.dbms_stats.gather_table_stats ( OwnName => user, TabName => 'T', Estimate_Percent => NULL, Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 254 ', Cascade => True , No_Invalidate => false); execute sys.dbms_stats.gather_table_stats (OwnName => user,     TabName => 'T',     Estimate_Percent => NULL,     Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 254 ',     Cascade => True ,     No_Invalidate => false ); 2.测试: select * from t where flag='1'; ... SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ ---------  839307494 g9xwrhwt0dn76            0  3206d0e6 --//理论讲我建立了直方图在flag字段,flag='1'的记录很多,oracle优化器应该选择全表扫描.而如果加入提示rule,oracle会选择我建立 --//的索引.导致执行计划变坏. select /*+ rule */ * from t where flag='1'; ... --//改语句使用索引i_t_flag. SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX ---------- ------------- ------------ --------- 1233342479 1f8dka14s6n0g            0  4983500f $ cat sqlpatch.sql prompt sqlid hint_text name prompt exec sys.dbms_sqldiag.drop_sql_patch('pathch_&1'); declare    v_sql CLOB; begin    select distinct sql_text into v_sql from v$sql where sql_id='&1' and rownum=1;    sys.dbms_sqldiag_internal.i_create_patch(       sql_text  => v_sql,       hint_text => '&2',       name      => 'patch_&1'); end; / SYS@book> @ sqlpatch.sql g9xwrhwt0dn76 rule sqlid hint_text name exec sys.dbms_sqldiag.drop_sql_patch('pathch_g9xwrhwt0dn76') PL/SQL procedure successfully completed. SYS@book> select * from dba_sql_patches where name like 'patch_%'   2  @ prxx ============================== NAME                          : patch_g9xwrhwt0dn76 CATEGORY                      : DEFAULT SIGNATURE                     : 1320158213876906125 SQL_TEXT                      : select * from t where flag='1' CREATED                       : 2021-09-29 09:32:20.000000 LAST_MODIFIED                 : 2021-09-29 09:32:20.000000 DESCRIPTION                   : STATUS                        : ENABLED FORCE_MATCHING                : NO TASK_ID                       : TASK_EXEC_NAME                : TASK_OBJ_ID                   : TASK_FND_ID                   : TASK_REC_ID                   : PL/SQL procedure successfully completed. SYS@book> alter system flush shared_pool; System altered. SYS@book> alter system flush shared_pool; System altered. select * from t where flag='1'; SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g9xwrhwt0dn76, child number 0 ------------------------------------- select * from t where flag='1' Plan hash value: 1601196873 --------------------------------------------------------------------------- | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |        |       |     8 (100)|          | |*  1 |  TABLE ACCESS FULL| T    |   1000 |   104K|     8   (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("FLAG"='1') Note -----    - SQL patch "patch_g9xwrhwt0dn76" used for this statement    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 30 rows selected. --//注意看下划线,sql patch已经启用,但是执行计划还是全表扫描. SCOTT@book> column COMP_DATA format a100 SCOTT@book> select SIGNATURE,COMP_DATA from  sys.sqlobj$data;               SIGNATURE COMP_DATA ----------------------- ----------------------------------------------------------     1320158213876906125 <outline_data><hint><![CDATA[rule]]></hint></outline_data> SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500))           AS outline_hints   FROM XMLTABLE        (           '/outline_data/hint'           PASSING (SELECT xmltype (comp_data) xml                      FROM sys.sqlobj$data                     WHERE signature = 1320158213876906125 AND obj_type = 3)        ) x; OUTLINE_HINTS ------------- rule --//可以发现使用sql patch 方式,使用rule提示无效.做一个记录.cursor_sharing_exact提示可以,为什么rule提示无效. --//我知道加入使用索引,提示index(t i_t_flag )在sql patch下无效的,必须使用INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."FLAG"))之类提示. --//为什么那里做错了.那位知道.