[20181007]12cR2 Using SQL Patch.txt

仙缘宝纂,acg宅,澳门大三巴广水信息

[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已经修复这个问题.

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_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

--//这几个表都没有查询到提示信息,另外写一篇文章分析看看.

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复