​[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"))之類提示.
--//為什麼那裡做錯了.那位知道.