[20181007]12cR2 Using SQL Patch.txt

[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> @ 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> @ desc_proc sys dbms_sqldiag create_sql_patch INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      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       Y 14 rows selected. 2.测试: SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;            DEPTNO DNAME                LOC ----------------- -------------------- -------------                10 ACCOUNTING           NEW YORK SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g0qybdz1796cn, child number 0 ------------------------------------- select /*+ full(dept) */ * from dept where deptno=10 Plan 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$1 Predicate 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_name PATCH_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=10 SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;            DEPTNO DNAME                LOC ----------------- -------------------- -------------                10 ACCOUNTING           NEW YORK SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g0qybdz1796cn, child number 0 ------------------------------------- select /*+ full(dept) */ * from dept where deptno=10 Plan 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$1 Predicate 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_name PATCH_NAME ------------------------------------ SYS_SQLPTCH_01664ea1bc190004 SCOTT@test01p> select /*+ full(dept) */ * from dept where deptno=10;            DEPTNO DNAME                LOC ----------------- -------------------- -------------                10 ACCOUNTING           NEW YORK SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  g0qybdz1796cn, child number 0 ------------------------------------- select /*+ full(dept) */ * from dept where deptno=10 Plan 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$1 Predicate 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=10 Plan 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$1 Predicate 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已经修复这个问题.