首页 > 编程札记 > 编程

固定执行计划

阅读：评论：0

固定执行计划

一、历史执行计划固定

历史的执行计划找到一个合理的执行计划进行绑定

1. 存在多个执行计划的语句，按照索引是比较合适的，FULL SCAN不合适

select * from where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID 4hpk08j31nm7y, child number 0

-------------------------------------

select * from where deptno=30

Plan hash value: 1404472509

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPTNO"=30)

SQL_ID 4hpk08j31nm7y, child number 1

-------------------------------------

select * from where deptno=30

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|* 1 | TABLE ACCESS FULL| EMP | 6 | 228 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DEPTNO"=30)

存在两个执行计划，使之后的SQL语句都走Plan hash value: 1404472509 处理模

2、运行coe_xfr_sql_profile脚本来绑定

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

sys@GULL> @coe_xfr_sql_profile.SQL

Parameter 1:

SQL_ID (required)

输入 1 的值: 4hpk08j31nm7y

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

1404472509 .002

3956160932 .015

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值: 1404472509

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "4hpk08j31nm7y"

PLAN_HASH_VALUE: "1404472509"

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_4hpk08j31nm7y_1404472509.sql

on TARGET system in order to create a custom SQL Profile

with plan 1404472509 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

sys@GULL> @coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql

sys@GULL> REM

sys@GULL> REM $Header: 215187.1 coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql 11.4.3.5 2016/06/20 carlos.sierra $

sys@GULL> REM

sys@GULL> REM AUTHOR

sys@GULL> REM carlos.sierra@oracle

sys@GULL> REM

sys@GULL> REM SCRIPT

sys@GULL> REM coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql

sys@GULL> REM

sys@GULL> REM DESCRIPTION

sys@GULL> REM This script is generated by coe_xfr_sql_profile.sql

sys@GULL> REM It contains the SQL*Plus commands to create a custom

sys@GULL> REM SQL Profile for SQL_ID 4hpk08j31nm7y based on plan hash

sys@GULL> REM value 1404472509.

sys@GULL> REM The custom SQL Profile to be created by this script

sys@GULL> REM will affect plans for SQL commands with signature

sys@GULL> REM matching the one for SQL Text below.

sys@GULL> REM Review SQL Text and adjust accordingly.

sys@GULL> REM

sys@GULL> REM PARAMETERS

sys@GULL> REM None.

sys@GULL> REM

sys@GULL> REM EXAMPLE

sys@GULL> REM SQL> START coe_xfr_sql_profile_4hpk08j31nm7y_1404472509.sql;

sys@GULL> REM

sys@GULL> REM NOTES

sys@GULL> REM 1. Should be run as SYSTEM or SYSDBA.

sys@GULL> REM 2. User must have CREATE ANY SQL PROFILE privilege.

sys@GULL> REM 3. SOURCE and TARGET systems can be the same or similar.

sys@GULL> REM 4. To drop this custom SQL Profile after it has been created:

sys@GULL> REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_4hpk08j31nm7y_1404472509');

sys@GULL> REM 5. Be aware that using DBMS_SQLTUNE requires a license

sys@GULL> REM for the Oracle Tuning Pack.

sys@GULL> REM

sys@GULL> WHENEVER SQLERROR EXIT SQL.SQLCODE;

sys@GULL> REM

sys@GULL> VAR signature NUMBER;

sys@GULL> REM

sys@GULL> DECLARE

2 sql_txt CLOB;

3 h SYS.SQLPROF_ATTR;

4 BEGIN

5 sql_txt := q'[

6 select * from p where deptno=30

7 ]';

8 h := SYS.SQLPROF_ATTR(

9 q'[BEGIN_OUTLINE_DATA]',

10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

11 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',

12 q'[DB_VERSION('11.2.0.3')]',

13 q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',

14 q'[ALL_ROWS]',

15 q'[OUTLINE_LEAF(@"SEL$1")]',

16 q'[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))]',

17 q'[END_OUTLINE_DATA]');

18 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

19 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

20 sql_text => sql_txt,

21 profile => h,

22 name => 'coe_4hpk08j31nm7y_1404472509',

23 description => 'coe 4hpk08j31nm7y 1404472509 '||:signature||'',

24 category => 'DEFAULT',

25 validate => TRUE,

26 replace => TRUE,

27 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

28 END;

29 /

PL/SQL 过程已成功完成。

sys@GULL> WHENEVER SQLERROR CONTINUE

sys@GULL> SET ECHO OFF;

SIGNATURE

---------------------

7148830044791940844

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_4hpk08j31nm7y_1404472509 completed

执行COE_XFR_SQL_PROFILE_4hpk08j31nm7y_1404472509

3、再此重新执行语句

select * from where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID 4hpk08j31nm7y, child number 2

-------------------------------------

select * from where deptno=30

Plan hash value: 1404472509

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 10 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPTNO"=30)

Note

-----

- SQL profile coe_4hpk08j31nm7y_1404472509 used for this statement

SQL profile coe_4hpk08j31nm7y_1404472509 used for this statement，说明sql profile已经绑定上，执行计划已这个为最佳，为止绑定处理

二、自己来构造合理的执行计划

1、构造执行计划

以下例子中sql语句走的是全表扫描，没有走索引，构造一个走索引的语句，来替换全表扫描执行计划

alter session set optimizer_index_cost_adj=500

select * from where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID 4hpk08j31nm7y, child number 0

-------------------------------------

select * from where deptno=30

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

|* 1 | TABLE ACCESS FULL| EMP | 6 | 228 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DEPTNO"=30)

执行现存在的coe_xfr_sql_profile

sys@GULL> @coe_xfr_sql_profile.SQL

Parameter 1:

SQL_ID (required)

输入 1 的值: 4hpk08j31nm7y

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

3956160932 .041

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值: 3956160932

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "4hpk08j31nm7y"

PLAN_HASH_VALUE: "3956160932 "

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_4hpk08j31nm7y_3956160932.sql

on TARGET system in order to create a custom SQL Profile

with plan 3956160932 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

查看构造SQL的走索引执行计划coe_xfr_sql_profile

select /*+index(emp index_emp_deptno)*/ * from where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID 2hdyvqk9b09va, child number 0

-------------------------------------

select /*+index(emp index_emp_deptno)*/ * from where

deptno=30

Plan hash value: 1404472509

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 10 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPTNO"=30)

查看次构造SQL的coe_xfr_sql_profile

SQL>@coe_xfr_sql_profile.SQL 2hdyvqk9b09va

Parameter 1:

SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

1404472509 .001

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值: 1404472509

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "2hdyvqk9b09va"

PLAN_HASH_VALUE: "1404472509"

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_2hdyvqk9b09va_1404472509.sql

on TARGET system in order to create a custom SQL Profile

with plan 1404472509 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

2、替换outline data

查看coe_xfr_sql_profile_2hdyvqk9b09va_1404472509.sql信息，需要替换的是这段内容

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
q'[OPT_PARAM('optimizer_index_cost_adj' 500)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))]',
q'[END_OUTLINE_DATA]');

把这个内容替换到coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql 中

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
q'[OPT_PARAM('optimizer_index_cost_adj' 500)]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "EMP"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

这段信息后，执行coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql 这个脚本

SQL>@coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql

SQL>REM

SQL>REM $Header: 215187.1 coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.sql 11.4.3.5 2016/06/20 carlos.sierra $

SQL>REM

SQL>REM AUTHOR

SQL>REM carlos.sierra@oracle

SQL>REM

SQL>REM SCRIPT

SQL>REM coe_xfr_sql_profile_4hpk08j31nm7y_3956160932.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 4hpk08j31nm7y based on plan hash

SQL>REM value 3956160932.

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_4hpk08j31nm7y_3956160932.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_4hpk08j31nm7y_3956160932');

SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license

SQL>REM for the Oracle Tuning Pack.

SQL>REM

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL>REM

SQL>VAR signature NUMBER;

SQL>REM

SQL>DECLARE

2 sql_txt CLOB;

3 h SYS.SQLPROF_ATTR;

4 BEGIN

5 sql_txt := q'[

6 select * from p where deptno=30

7 ]';

8 h := SYS.SQLPROF_ATTR(

9 q'[BEGIN_OUTLINE_DATA]',

10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

11 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',

12 q'[DB_VERSION('11.2.0.3')]',

13 q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',

14 q'[OPT_PARAM('optimizer_index_cost_adj' 500)]',

15 q'[ALL_ROWS]',

16 q'[OUTLINE_LEAF(@"SEL$1")]',

17 q'[INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))]',

18 q'[END_OUTLINE_DATA]');

19 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

20 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

21 sql_text => sql_txt,

22 profile => h,

23 name => 'coe_4hpk08j31nm7y_3956160932',

24 description => 'coe 4hpk08j31nm7y 3956160932 '||:signature||'',

25 category => 'DEFAULT',

26 validate => TRUE,

27 replace => TRUE,

28 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

29 END;

30 /

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTINUE

SQL>SET ECHO OFF;

SIGNATURE

---------------------

7148830044791940844

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_4hpk08j31nm7y_3956160932 completed

3、再次语句查看执行计划

select * from where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID 4hpk08j31nm7y, child number 0

-------------------------------------

select * from where deptno=30

Plan hash value: 1404472509

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 10 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPTNO"=30)

Note

-----

- SQL profile coe_4hpk08j31nm7y_3956160932 used for this statement

偷梁换柱成功，固定执行so easy

提供脚本文件COE_XFR_SQL_PROFILE.SQL

COE_XFR_SQL_PROFILE

参考《基于SQL的优化》

本文发布于:2024-01-30 02:16:24，感谢您对本站的认可！

本文链接：https://www.4u4v.net/it/170655218818521.html

上一篇：【oracle】固定执行计划

下一篇：coe

标签：计划

留言与评论（共有 0 条评论）