[20181007]12cR2 Using SQL Patch 2.txt

[20181007]12cR2 Using SQL Patch 2.txt

--//12cR2 已經把sql打補丁整合進入dbms_sqldiag,不是11g的 DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH .做一個記錄.
--//以前的連結:http://blog.itpub.net/267265/viewspace-751900/=>[20121231]給sql打補丁.txt
--//想看看打補丁的提示寫入那張表.奇怪沒有地方顯示hint_text資訊.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;
           DEPTNO DNAME                LOC
----------------- -------------------- -------------
               10 ACCOUNTING           NEW YORK

--//sql_id=g0qybdz1796cn

SCOTT@test01p> select text_vc c100 from dba_views where view_name='DBA_SQL_PATCHES';
C100
----------------------------------------------------------------------------------------------------
SELECT
    so.name, so.category, so.signature, st.sql_text,
    ad.created, ad.last_modified, ad.description,
    DECODE(BITAND(so.flags, 1), 1, 'ENABLED', 'DISABLED'),
    DECODE(BITAND(sq.flags, 1), 1, 'YES', 'NO'),
    ad.task_id, ad.task_exec_name, ad.task_obj_id, ad.task_fnd_id,
    ad.task_rec_id
FROM
    sqlobj$        so,
    sqlobj$auxdata ad,
    sql$text       st,
    sql$           sq
WHERE
    so.signature = st.signature AND
    so.signature = ad.signature AND
    so.category  = ad.category  AND
    so.signature = sq.signature AND
    so.obj_type = 3 AND
    ad.obj_type = 3

--//我查詢這些sqlobj$,sqlobj$auxdata,sql$text,sql$表,沒有對應的提示資訊.

2.跟蹤看看:

SCOTT@test01p> @ 10046on 12
Session altered.

SCOTT@test01p> exec :patch_name := dbms_sqldiag.create_sql_patch(sql_id=>'g0qybdz1796cn',hint_text=>'result_cache INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))');
PL/SQL procedure successfully completed.

SCOTT@test01p> @ 10046off
Session altered.

--//檢視跟蹤檔案可以發現如下,檢索result_cache 就可以發現如下:
PARSING IN CURSOR #31521616 len=1500 dep=1 uid=0 oct=189 lid=0 tim=9303948110 hv=2730219038 ad='7ff16b8ca68' sqlid='2azfx4fjbrphy'

MERGE /*+ INDEX(dest (signature category obj_type plan_id)) */
     INTO  sqlobj$data dest
     USING (SELECT :1 signature
                  ,:2 category
                  ,:3 obj_type
                  ,:4 plan_id
              FROM DUAL) src
        ON (    dest.signature = src.signature
            AND dest.category = src.category
            AND dest.obj_type = src.obj_type
            AND dest.plan_id = src.plan_id)
WHEN MATCHED
THEN
   UPDATE SET comp_data = :5
   DELETE
           WHERE     signature = :6
                 AND category = :7
                 AND obj_type = :8
                 AND plan_id = :9
                 AND :10 = 0
WHEN NOT MATCHED
THEN
   INSERT
              (
                 signature
                ,category
                ,obj_type
                ,plan_id
                ,comp_data
                ,spare1
                ,spare2
              )
       VALUES
              (
                 :11
                ,:12
                ,:13
                ,:14
                ,:15
                ,NULL
                ,NULL
              )
        WHERE (:16 > 0)

--//為了好看,我做了格式化處理.
END OF STMT
PARSE #31521616:c=0,e=548,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=9303948110
BINDS #813829336:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=2eca1bc8  bln=22  avl=03  flg=05
  value=336


...
 Bind#14
  oacdty=01 mxl=128(122) mxlc=00 mal=00 scl=00 pre=00
  oacflg=21 fl2=0000 frm=01 csi=852 siz=0 off=488
  kxsbbbfp=2ed21750  bln=128  avl=122  flg=01
  value="<outline_data><hint><![CDATA[result_cache INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))]]></hint></outline_data>"
 Bind#15
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=0000 frm=00 csi=00 siz=0 off=616
  kxsbbbfp=2ed217d0  bln=22  avl=03  flg=01
  value=122
EXEC #31521616:c=15600,e=258743,p=0,cr=148,cu=6,mis=1,r=1,dep=1,og=1,plh=4253447951,tim=9304207021
STAT #31521616 id=1 cnt=0 pid=0 pos=1 obj=0 op='MERGE  SQLOBJ$DATA (cr=1 pr=0 pw=1 str=1 time=237554 us)'
STAT #31521616 id=2 cnt=1 pid=1 pos=1 obj=0 op='VIEW  (cr=1 pr=0 pw=0 str=1 time=23 us)'
STAT #31521616 id=3 cnt=1 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=1 pr=0 pw=0 str=1 time=19 us cost=2 size=1066 card=1)'
STAT #31521616 id=4 cnt=1 pid=3 pos=1 obj=0 op='VIEW  (cr=0 pr=0 pw=0 str=1 time=4 us cost=2 size=62 card=1)'
STAT #31521616 id=5 cnt=1 pid=4 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'
STAT #31521616 id=6 cnt=0 pid=3 pos=2 obj=341 op='INDEX UNIQUE SCAN SQLOBJ$DATA_PKEY (cr=1 pr=0 pw=0 str=1 time=12 us cost=0 size=1004 card=1)'
CLOSE #31521616:c=0,e=10,dep=1,type=0,tim=9304208195

--//表SQLOBJ$DATA

SCOTT@test01p> @ pt2 'select * from sys.SQLOBJ$DATA'
          ROW_NUM           COL_NUM COL_NAME             COL_VALUE
----------------- ----------------- -------------------- ----------------------------------------------------------------------------------------------------
                1                 1 SIGNATURE            16064551385586871472
                                  2 CATEGORY             DEFAULT
                                  3 OBJ_TYPE             3
                                  4 PLAN_ID              0
                                  5 COMP_DATA            <outline_data><hint><![CDATA[result_cache INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))]]>

--//查詢依賴關係:
SCOTT@test01p> @ pt2 'select * from sys.SQLOBJ$DATA_DATAPUMP'
          ROW_NUM           COL_NUM COL_NAME             COL_VALUE
----------------- ----------------- -------------------- ----------------------------------------------------------------------------------------------------
                1                 1 SIGNATURE            16064551385586871472
                                  2 CATEGORY             DEFAULT
                                  3 OBJ_TYPE             3
                                  4 PLAN_ID              0
                                  5 COMP_DATA            <outline_data><hint><![CDATA[result_cache INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))]]>

SCOTT@test01p> select text_vc c100 from dba_views where view_name='SQLOBJ$DATA_DATAPUMP';
C100
----------------------------------------------------------------------------------------------------
SELECT "SIGNATURE","CATEGORY","OBJ_TYPE","PLAN_ID","COMP_DATA","SPARE1","SPARE2" FROM sqlobj$data

--//很奇怪,oracle設計沒有檢視很好的查詢相關資訊.