一、coe_xfr_sql_profile脚本固定执行计划
1、原始sql
sql> select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
------------------------------------------------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 2 (100)| |
|* 1 | table access full| t | 1 | 20 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
create index idx_t on t(empno,ename);
sql> select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
--------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 2296882198
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 1 (100)| |
|* 1 | index range scan| idx_t | 1 | 20 | 1 (0)| 00:00:01 |
2、生成脚本
sql> @coe_xfr_sql_profile.sql 6wk7ggz469b7u 1601196873
parameter 1:
sql_id (required)
plan_hash_value avg_et_secs
--------------- -----------
2296882198 .002
parameter 2:
plan_hash_value (required)
values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sql_id : "6wk7ggz469b7u"
plan_hash_value: "1601196873"
sql>begin
2 if :sql_text is null then
3 raise_application_error(-20100, 'sql_text for sql_id &&sql_id. was not found in memory (gv$sqltext_with_newlines) or awr (dba_hist_sqltext).');
4 end if;
5 end;
6 /
sql>set term off;
sql>begin
2 if :other_xml is null then
3 raise_application_error(-20101, 'plan for sql_id &&sql_id. and phv &&plan_hash_value. was not found in memory (gv$sql_plan) or awr (dba_hist_sql_plan).');
4 end if;
5 end;
6 /
sql>set term off;
execute coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
on target system in order to create a custom sql profile
with plan 1601196873 linked to adjusted sql_text.
coe_xfr_sql_profile completed.
3、运行脚本
sql>@coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
sql>rem
sql>rem $header: 215187.1 coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql 11.4.4.4 2023/04/14 carlos.sierra $
sql>rem
sql>rem 米乐app官网下载 copyright (c) 2000-2012, oracle corporation. all rights reserved.
sql>rem
sql>rem author
sql>rem carlos.sierra@oracle.com
sql>rem
sql>rem script
sql>rem coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
sql>rem
sql>rem description
sql>rem this script is generated by coe_xfr_sql_profile.sql
sql>rem it contains the sql*plus commands to create a custom
sql>rem sql profile for sql_id 6wk7ggz469b7u based on plan hash
sql>rem value 1601196873.
sql>rem the custom sql profile to be created by this script
sql>rem will affect plans for sql commands with signature
sql>rem matching the one for sql text below.
sql>rem review sql text and adjust accordingly.
sql>rem
sql>rem parameters
sql>rem none.
sql>rem
sql>rem example
sql>rem sql> start coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql;
sql>rem
sql>rem notes
sql>rem 1. should be run as system or sysdba.
sql>rem 2. user must have create any sql profile privilege.
sql>rem 3. source and target systems can be the same or similar.
sql>rem 4. to drop this custom sql profile after it has been created:
sql>rem exec dbms_sqltune.drop_sql_profile('coe_6wk7ggz469b7u_1601196873');
sql>rem 5. be aware that using dbms_sqltune requires a license
sql>rem for the oracle tuning pack.
sql>rem 6. if you modified a sql putting hints in order to produce a desired
sql>rem plan, you can remove the artifical hints from sql text pieces below.
sql>rem by doing so you can create a custom sql profile for the original
sql>rem sql but with the plan captured from the modified sql (with hints).
sql>rem
sql>whenever sqlerror exit sql.sqlcode;
sql>rem
sql>var signature number;
sql>var signaturef number;
sql>rem
sql>declare
2 sql_txt clob;
3 h sys.sqlprof_attr;
4 procedure wa (p_line in varchar2) is
5 begin
6 dbms_lob.writeappend(sql_txt, length(p_line), p_line);
7 end wa;
8 begin
9 dbms_lob.createtemporary(sql_txt, true);
10 dbms_lob.open(sql_txt, dbms_lob.lob_readwrite);
11 -- sql text pieces below do not have to be of same length.
12 -- so if you edit sql text (i.e. removing temporary hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select ename,empno from t where empno=7788]');
15 dbms_lob.close(sql_txt);
16 h := sys.sqlprof_attr(
17 q'[begin_outline_data]',
18 q'[ignore_optim_embedded_hints]',
19 q'[optimizer_features_enable('19.1.0')]',
20 q'[db_version('19.1.0')]',
21 q'[all_rows]',
22 q'[outline_leaf(@"sel$1")]',
23 q'[full(@"sel$1" "t"@"sel$1")]',
24 q'[end_outline_data]');
25 :signature := dbms_sqltune.sqltext_to_signature(sql_txt);
26 :signaturef := dbms_sqltune.sqltext_to_signature(sql_txt, true);
27 dbms_sqltune.import_sql_profile (
28 sql_text => sql_txt,
29 profile => h,
30 name => 'coe_6wk7ggz469b7u_1601196873',
31 description => 'coe 6wk7ggz469b7u 1601196873 '||:signature||' '||:signaturef||'',
32 category => 'default',
33 validate => true,
34 replace => true,
35 force_match => true /* true:force (match even when different literals in sql). false:exact (similar to cursor_sharing) */ );
36 dbms_lob.freetemporary(sql_txt);
37 end;
38 /
pl/sql procedure successfully completed.
sql>whenever sqlerror continue
sql>set echo off;
signature
---------------------
5123411738372664700
signaturef
---------------------
3065794480876030305
... manual custom sql profile has been created
coe_xfr_sql_profile_6wk7ggz469b7u_1601196873 completed
4、验证
sql>select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
--------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 2 (100)| |
|* 1 | table access full| t | 1 | 20 | 2 (0)| 00:00:01 |
plan_table_output
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - sel$1 / t@sel$1
outline data
-------------
/*
plan_table_output
--------------------------------------------------------------------------------
begin_outline_data
ignore_optim_embedded_hints
optimizer_features_enable('19.1.0')
db_version('19.1.0')
all_rows
outline_leaf(@"sel$1")
full(@"sel$1" "t"@"sel$1")
end_outline_data
*/
predicate information (identified by operation id):
plan_table_output
--------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("empno"=7788)
column projection information (identified by operation id):
-----------------------------------------------------------
1 - "empno"[number,22], "ename"[varchar2,10]
hint report (identified by operation id / query block name / object alias):
total hints for statement: 5
plan_table_output
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
0 - statement
- all_rows
- db_version('19.1.0')
- ignore_optim_embedded_hints
- optimizer_features_enable('19.1.0')
1 - sel$1 / t@sel$1
- full(@"sel$1" "t"@"sel$1")
plan_table_output
--------------------------------------------------------------------------------
note
-----
- dynamic statistics used: dynamic sampling (level=2)
- sql profile coe_6wk7ggz469b7u_1601196873 used for this statement
query block registry:
---------------------
plan_table_output
--------------------------------------------------------------------------------
67 rows selected.
二、手工替换替换执行计划
1、原始执行计划
sql>select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
------------------------------------------------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 2296882198
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 1 (100)| |
|* 1 | index range scan| idx_t | 1 | 20 | 1 (0)| 00:00:01 |
2、目标执行计划
sql>select /* full(t) */ ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
------------------------------------------------------------------------------------------------------------------------
sql_id 9fzxjd60t16g9, child number 0
-------------------------------------
select /* full(t) */ ename,empno from t where empno=7788
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 2 (100)| |
|* 1 | table access full| t | 1 | 20 | 2 (0)| 00:00:01 |
3、生成绑定脚本
把需要调整的sqlid,想用的执行计划串一串
sql>@coe_xfr_sql_profile.sql 6wk7ggz469b7u 1601196873
parameter 1:
sql_id (required)
plan_hash_value avg_et_secs
--------------- -----------
2296882198 .001
parameter 2:
plan_hash_value (required)
values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sql_id : "6wk7ggz469b7u"
plan_hash_value: "1601196873"
sql>begin
2 if :sql_text is null then
3 raise_application_error(-20100, 'sql_text for sql_id &&sql_id. was not found in memory (gv$sqltext_with_newlines) or awr (dba_hist_sqltext).');
4 end if;
5 end;
6 /
sql>set term off;
sql>begin
2 if :other_xml is null then
3 raise_application_error(-20101, 'plan for sql_id &&sql_id. and phv &&plan_hash_value. was not found in memory (gv$sql_plan) or awr (dba_hist_sql_plan).');
4 end if;
5 end;
6 /
sql>set term off;
execute coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
on target system in order to create a custom sql profile
with plan 1601196873 linked to adjusted sql_text.
coe_xfr_sql_profile completed.
4、执行绑定脚本
sql>coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
sp2-0734: unknown command beginning "coe_xfr_sq..." - rest of line ignored.
sql>@coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
sql>rem
sql>rem $header: 215187.1 coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql 11.4.4.4 2023/04/14 carlos.sierra $
sql>rem
sql>rem 米乐app官网下载 copyright (c) 2000-2012, oracle corporation. all rights reserved.
sql>rem
sql>rem author
sql>rem carlos.sierra@oracle.com
sql>rem
sql>rem script
sql>rem coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
sql>rem
sql>rem description
sql>rem this script is generated by coe_xfr_sql_profile.sql
sql>rem it contains the sql*plus commands to create a custom
sql>rem sql profile for sql_id 6wk7ggz469b7u based on plan hash
sql>rem value 1601196873.
sql>rem the custom sql profile to be created by this script
sql>rem will affect plans for sql commands with signature
sql>rem matching the one for sql text below.
sql>rem review sql text and adjust accordingly.
sql>rem
sql>rem parameters
sql>rem none.
sql>rem
sql>rem example
sql>rem sql> start coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql;
sql>rem
sql>rem notes
sql>rem 1. should be run as system or sysdba.
sql>rem 2. user must have create any sql profile privilege.
sql>rem 3. source and target systems can be the same or similar.
sql>rem 4. to drop this custom sql profile after it has been created:
sql>rem exec dbms_sqltune.drop_sql_profile('coe_6wk7ggz469b7u_1601196873');
sql>rem 5. be aware that using dbms_sqltune requires a license
sql>rem for the oracle tuning pack.
sql>rem 6. if you modified a sql putting hints in order to produce a desired
sql>rem plan, you can remove the artifical hints from sql text pieces below.
sql>rem by doing so you can create a custom sql profile for the original
sql>rem sql but with the plan captured from the modified sql (with hints).
sql>rem
sql>whenever sqlerror exit sql.sqlcode;
sql>rem
sql>var signature number;
sql>var signaturef number;
sql>rem
sql>declare
2 sql_txt clob;
3 h sys.sqlprof_attr;
4 procedure wa (p_line in varchar2) is
5 begin
6 dbms_lob.writeappend(sql_txt, length(p_line), p_line);
7 end wa;
8 begin
9 dbms_lob.createtemporary(sql_txt, true);
10 dbms_lob.open(sql_txt, dbms_lob.lob_readwrite);
11 -- sql text pieces below do not have to be of same length.
12 -- so if you edit sql text (i.e. removing temporary hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select ename,empno from t where empno=7788]');
15 dbms_lob.close(sql_txt);
16 h := sys.sqlprof_attr(
17 q'[begin_outline_data]',
18 q'[ignore_optim_embedded_hints]',
19 q'[optimizer_features_enable('19.1.0')]',
20 q'[db_version('19.1.0')]',
21 q'[all_rows]',
22 q'[outline_leaf(@"sel$1")]',
23 q'[full(@"sel$1" "t"@"sel$1")]',
24 q'[end_outline_data]');
25 :signature := dbms_sqltune.sqltext_to_signature(sql_txt);
26 :signaturef := dbms_sqltune.sqltext_to_signature(sql_txt, true);
27 dbms_sqltune.import_sql_profile (
28 sql_text => sql_txt,
29 profile => h,
30 name => 'coe_6wk7ggz469b7u_1601196873',
31 description => 'coe 6wk7ggz469b7u 1601196873 '||:signature||' '||:signaturef||'',
32 category => 'default',
33 validate => true,
34 replace => true,
35 force_match => true /* true:force (match even when different literals in sql). false:exact (similar to cursor_sharing) */ );
36 dbms_lob.freetemporary(sql_txt);
37 end;
38 /
pl/sql procedure successfully completed.
sql>whenever sqlerror continue
sql>set echo off;
signature
---------------------
5123411738372664700
signaturef
---------------------
3065794480876030305
... manual custom sql profile has been created
coe_xfr_sql_profile_6wk7ggz469b7u_1601196873 completed
5、验证
sql>select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
--------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 2 (100)| |
|* 1 | table access full| t | 1 | 20 | 2 (0)| 00:00:01 |
exec dbms_sqltune.drop_sql_profile(name => ‘coe_6wk7ggz469b7u_1601196873’);
三、偷梁换柱
1、目标库慢sql
sql>select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
------------------------------------------------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 2296882198
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 1 (100)| |
|* 1 | index range scan| idx_t | 1 | 20 | 1 (0)| 00:00:01 |
##### 2、原库快sql
sql>select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
------------------------------------------------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 2 (100)| |
|* 1 | table access full| t | 1 | 20 | 2 (0)| 00:00:01 |
3、准备目标库和源库outline
- 目标库慢sql
sql>@coe_xfr_sql_profile 6wk7ggz469b7u 2296882198
parameter 1:
sql_id (required)
plan_hash_value avg_et_secs
--------------- -----------
1601196873 .003
parameter 2:
plan_hash_value (required)
values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sql_id : "6wk7ggz469b7u"
plan_hash_value: "2296882198"
sql>begin
2 if :sql_text is null then
3 raise_application_error(-20100, 'sql_text for sql_id &&sql_id. was not found in memory (gv$sqltext_with_newlines) or awr (dba_hist_sqltext).');
4 end if;
5 end;
6 /
sql>set term off;
sql>begin
2 if :other_xml is null then
3 raise_application_error(-20101, 'plan for sql_id &&sql_id. and phv &&plan_hash_value. was not found in memory (gv$sql_plan) or awr (dba_hist_sql_plan).');
4 end if;
5 end;
6 /
sql>set term off;
execute coe_xfr_sql_profile_6wk7ggz469b7u_2296882198.sql
on target system in order to create a custom sql profile
with plan 2296882198 linked to adjusted sql_text.
coe_xfr_sql_profile completed.
- 原库快sql
sql>@coe_xfr_sql_profile 6wk7ggz469b7u 1601196873
parameter 1:
sql_id (required)
plan_hash_value avg_et_secs
--------------- -----------
1601196873 .003
parameter 2:
plan_hash_value (required)
values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sql_id : "6wk7ggz469b7u"
plan_hash_value: "1601196873"
sql>begin
2 if :sql_text is null then
3 raise_application_error(-20100, 'sql_text for sql_id &&sql_id. was not found in memory (gv$sqltext_with_newlines) or awr (dba_hist_sqltext).');
4 end if;
5 end;
6 /
sql>set term off;
sql>begin
2 if :other_xml is null then
3 raise_application_error(-20101, 'plan for sql_id &&sql_id. and phv &&plan_hash_value. was not found in memory (gv$sql_plan) or awr (dba_hist_sql_plan).');
4 end if;
5 end;
6 /
sql>set term off;
execute coe_xfr_sql_profile_6wk7ggz469b7u_1601196873.sql
on target system in order to create a custom sql profile
with plan 1601196873 linked to adjusted sql_text.
coe_xfr_sql_profile completed.
4、替换outline
将慢sql得outline替换为快sql得outline
- 原库快sql
h := sys.sqlprof_attr(
q'[begin_outline_data]',
q'[ignore_optim_embedded_hints]',
q'[optimizer_features_enable('19.1.0')]',
q'[db_version('19.1.0')]',
q'[all_rows]',
q'[outline_leaf(@"sel$1")]',
q'[full(@"sel$1" "t"@"sel$1")]', --这里写着全表扫
q'[end_outline_data]');
- 目标库慢sql
h := sys.sqlprof_attr(
q'[begin_outline_data]',
q'[ignore_optim_embedded_hints]',
q'[optimizer_features_enable('19.1.0')]',
q'[db_version('19.1.0')]',
q'[all_rows]',
q'[outline_leaf(@"sel$1")]',
q'[index(@"sel$1" "t"@"sel$1" ("t"."empno" "t"."ename"))]',
q'[end_outline_data]');
5、验证
sql>select ename,empno from t where empno=7788;
ename empno
-------------------- ----------
scott 7788
sql>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
--------------------------------------------------------------------------------
sql_id 6wk7ggz469b7u, child number 0
-------------------------------------
select ename,empno from t where empno=7788
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | | | 2 (100)| |
|* 1 | table access full| t | 1 | 20 | 2 (0)| 00:00:01 |
plan_table_output
--------------------------------------------------------------------------------
该会话一直未关闭，启用的新执行计划直接使用了
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。