[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
–//这几个表都没有查询到提示信息,另外写一篇文章分析看看.