[20181007]12cR2 Using SQL Patch.txt
--//12cR2 已经把sql打补丁集成进入dbms_sqldiag,不是11g的 DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH .做一个记录.--//以前的链接:http://blog.itpub.net/267265/viewspace-751900/=>[20121231]给sql打补丁.txt 1.环境:SCOTT@test01p> @ ver1PORT_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 0SCOTT@test01p> @ desc_proc sys dbms_sqldiag create_sql_patchINPUT OWNER PACKAGE_NAME OBJECT_NAMEsample : @desc_proc sys dbms_stats gather_%_statsOWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED---------- -------------------- ---------------- -------- -------------------- -------------------- --------- -------------------- ----------SYS DBMS_SQLDIAG CREATE_SQL_PATCH 1 VARCHAR2 OUT VARCHAR2 N 2 SQL_ID VARCHAR2 IN VARCHAR2 N 3 HINT_TEXT CLOB IN CLOB N 4 NAME VARCHAR2 IN VARCHAR2 Y 5 DESCRIPTION VARCHAR2 IN VARCHAR2 Y 6 CATEGORY VARCHAR2 IN VARCHAR2 Y 7 VALIDATE PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y 1 VARCHAR2 OUT VARCHAR2 N 2 SQL_TEXT CLOB IN CLOB N 3 HINT_TEXT CLOB IN CLOB N 4 NAME VARCHAR2 IN VARCHAR2 Y 5 DESCRIPTION VARCHAR2 IN VARCHAR2 Y 6 CATEGORY VARCHAR2 IN VARCHAR2 Y 7 VALIDATE PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y14 rows selected.2.测试:SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10; DEPTNO DNAME LOC----------------- -------------------- ------------- 10 ACCOUNTING NEW YORKSCOTT@test01p> @ dpc '' ''PLAN_TABLE_OUTPUT-------------------------------------SQL_ID g0qybdz1796cn, child number 0-------------------------------------select /*+ full(dept) */ * from dept where deptno=10Plan hash value: 3383998547---------------------------------------------------------------------------| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3 (100)| ||* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("DEPTNO"=10)--//sql_id=g0qybdz1796cn,实际上走索引更佳.注意多执行几次保留在共享池.SCOTT@test01p> variable patch_name varchar2(2000);SCOTT@test01p> exec :patch_name := dbms_sqldiag.create_sql_patch(sql_id=>'g0qybdz1796cn',hint_text=>'index(dept pk_dept)');PL/SQL procedure successfully completed.--//现在居然scott用户就可以执行,我记忆里以前不行,必须sys用户执行.SCOTT@test01p> print :patch_namePATCH_NAME------------------------------------------SYS_SQLPTCH_01664e9a59810003--//相关信息记录在视图DBA_SQL_PATCHES.SCOTT@test01p> select NAME c30,SQL_TEXT from DBA_SQL_PATCHES;C30 SQL_TEXT------------------------------ ------------------------------------------------------------SYS_SQLPTCH_01664e9a59810003 select /*+ full(dept) */ * from dept where deptno=10SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10; DEPTNO DNAME LOC----------------- -------------------- ------------- 10 ACCOUNTING NEW YORKSCOTT@test01p> @ dpc '' ''PLAN_TABLE_OUTPUT-------------------------------------SQL_ID g0qybdz1796cn, child number 0-------------------------------------select /*+ full(dept) */ * from dept where deptno=10Plan hash value: 3383998547---------------------------------------------------------------------------| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3 (100)| ||* 1 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("DEPTNO"=10)Note----- - SQL patch "SYS_SQLPTCH_01664e9a59810003" used for this statement--//没有起作用.实际上不能使用这样的提示.执行如下:select * from dept where deptno=10;--//再看执行计划提示:SCOTT@test01p> @ dpc '' outline...Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO")) END_OUTLINE_DATA */--//使用提示INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO")).才行.SCOTT@test01p> exec dbms_sqldiag.drop_sql_patch(name=>'SYS_SQLPTCH_01664e9a59810003');PL/SQL procedure successfully completed.SCOTT@test01p> exec :patch_name := dbms_sqldiag.create_sql_patch(sql_id=>'g0qybdz1796cn',hint_text=>'INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))');PL/SQL procedure successfully completed.SCOTT@test01p> print :patch_namePATCH_NAME------------------------------------SYS_SQLPTCH_01664ea1bc190004SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10; DEPTNO DNAME LOC----------------- -------------------- ------------- 10 ACCOUNTING NEW YORKSCOTT@test01p> @ dpc '' ''PLAN_TABLE_OUTPUT-------------------------------------SQL_ID g0qybdz1796cn, child number 0-------------------------------------select /*+ full(dept) */ * from dept where deptno=10Plan hash value: 2852011669----------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 1 (100)| || 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 2 - SEL$1 / DEPT@SEL$1Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("DEPTNO"=10)Note----- - SQL patch "SYS_SQLPTCH_01664ea1bc190004" 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--//OK,现在起作用了.3.我个人认为sql打补丁最佳方式是加BIND_AWARE或者result_cache提示,其它情况我很少使用.--//我记忆里11g下不能加result_cache,再加这个提示看看.SCOTT@test01p> exec dbms_sqldiag.drop_sql_patch(name=>'SYS_SQLPTCH_01664ea1bc190004');PL/SQL procedure successfully completed.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> Select /*+ full(dept) */ * from dept where deptno=10; DEPTNO DNAME LOC----------------- -------------------- ------------- 10 ACCOUNTING NEW YORK--//我修改select=>Select.SCOTT@test01p> @ dpc '' ''PLAN_TABLE_OUTPUT-------------------------------------SQL_ID 4sg4rbwu9r59q, child number 0-------------------------------------Select /*+ full(dept) */ * from dept where deptno=10Plan hash value: 2852011669------------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 1 (100)| || 1 | RESULT CACHE | 364dg0urjj61xc7was3s7u5hcj | | | | || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / DEPT@SEL$1 3 - SEL$1 / DEPT@SEL$1Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("DEPTNO"=10)Result Cache Information (identified by operation id):------------------------------------------------------ 1 -Note----- - SQL patch "SYS_SQLPTCH_01664eab2c7a0006" 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--//OK,12c已经修复这个问题.4.自己还有1个疑问,如何看到以前的提示: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_idFROM sqlobj$ so, sqlobj$auxdata ad, sql$text st, sql$ sqWHERE 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--//这几个表都没有查询到提示信息,另外写一篇文章分析看看.