[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"))之类提示.
--//为什么那里做错了.那位知道.