[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设计没有视图很好的查询相关信息.