[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已經修復這個問題.