Oracle执行计划绑定
在Oracle中时长会出现一直运行正常的SQL突然运行很慢的问题。检查发现执行计划发生了改变，这时候就需要绑定执行计划。在Oracle 10g以后的版本中，可以使用SQL Profile或SPM（11g以后）来绑定执行计划。
1、SQL Profile
Oracle 10g中的SQL Profile是Oracle 9i中的stored outline的进化升级版，相比stored outline有以下优点：
更容易生成、更改和控制。
对SQL语句的支持做的更高，适用范围广。
SQL Profile有两种类型：
①Automatic类型
②Manual类型。
1.Automatic类型
Automatic类型的SQL Profile起始就是针对目标SQL的一些额外的调整信息，这些信息存储在数据字典中，有了Automatic类型的SQL Profile之后Oracle就会根据它目标SQL所涉及的统计信息等内容做相应的调整。
从某种意义上说Automatic类型的SQL Profile并不能完全起到稳定目标SQL执行计划的作用，但是的确可以用来调整执行计划。
缺点：只能用于单条语句固定执行计划，并不能广泛应用于生产中。
SQL> create table tab_1(id number);
Table created.
SQL> declare
2 begin
3 for i in 1..10000
4 loop
5 insert into tab_1 values(i);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(1) from tab_1;
COUNT(1)
----------
10000
SQL> create index id_ind on tab_1(id);
Index created.
SQL> conn / as sysdba
Connected.
SQL> exec dbms_stats.gather_table_stats('scott','tab_1');
PL/SQL procedure successfully completed.
SQL> conn scott/scott
Connected.
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
--发现id=100执行计划为 TABLE ACCESS FULL
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6dmxvq99wtg3h, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
--创建一个SQL_TUNNING
SQL>declare
2 my_task_name varchar2(30);
3 sql_text clob;
4 begin
5 sql_text :='select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100';
6 my_task_name := dbms_sqltune.create_tuning_task(
7 sql_text =>sql_text,
8 user_name =>'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit =>60,
11 task_name =>'my_sql_tunning',
12 description=>'task to tune a query on tabe tab_1');
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
--执行SQL_TUNNING
SQL> begin
2 dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tunning');
3 end;
4 /
PL/SQL procedure successfully completed.
--查看SQL_TUNNING结果
SQL> set long 9000;
SQL> set longchunksize 1000;
SQL> set linesize 800;
SQL> select dbms_sqltune.report_tuning_task('my_sql_tunning') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNNING')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tunning
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/02/2019 05:08:52
Completed at : 05/02/2019 05:08:56
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 7494g56v1caaz
SQL Text : select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
--为此句找到了性能更好的执行计划。
Recommendation (estimated benefit: 90.91%)
------------------------------------------
- Consider accepting the recommended SQL profile.
--考虑接受推荐的SQL概要文件
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tunning',
task_owner => 'SCOTT', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .00019 .000016 91.57 %
CPU Time (s): .000199 0 100 %
User I/O Time (s): 0 0
Buffer Gets: 22 2 90.9 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
2- Using SQL Profile
--------------------
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=100)
-------------------------------------------------------------------------------
--执行接受更优的执行计划
SQL> execute dbms_sqltune.accept_sql_profile(task_name=>'my_sql_tunning',task_owner=>'SCOTT',replace=>TRUE);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
--再次查看执行计划，发现执行计划已经更改
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bu4t0t209yx51, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
Note
-----
- SQL profile SYS_SQLPROF_016a75433f370000 used for this statement
46 rows selected.
--当换了id值之后发现之前绑定的执行计划已经失效。
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=200;
ID
----------
200
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bsxxutpyyphsd, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=200
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=200)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
2.Manual类型
Manual类型的SQL Profile本质上就是一堆hint的组合，它可以在不更改SQL文本的前提下启到很大的稳定目标SQL执行计划的作用。
步骤：
①找出问题SQL并记录SQL_ID和PLAN_HASH_VALUE，使用hint优化该SQL，记录优化好的SQL_ID和PLAN_HASH_VALUE。
②运行coe_xfr_sql_profile.sql，将原SQL生成的文本记录文本A，优化后SQL生成的文本记录文本B，将文本B中的hint部分“偷梁换柱”到文本A中，并将文本A中force_match参数改为TRUE。
③运行修改后的文本A，再次执行原SQL确认执行计划。做到了不修改SQL文本固定执行计划。
承接上述实验，删除上述automatic类型的Profile，SYS_SQLPROF_016a75433f370000。
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016a75433f370000');
PL/SQL procedure successfully completed.
--再次查看执行计划，发现已经是全表扫描。记录sql_id和plan_hash_value。
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bu4t0t209yx51, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
--对当前有问题的SQL进行hint优化，记录优化后的sql_id和plan_hash_value。
SQL> select /*+ index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9bb97qbdhpf3a, child number 0
-------------------------------------
select /*+ index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
--查看问题SQL优化前后的SQL_ID和PLAN_HASH_VALUE，此为测试环境，生产环境中应先记录原SQL的sql_id和PLAN_HASH_VALUE。
SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%* from tab_1 where id=100%';
SQL_TEXT SQL_ID VERSION_COUNT
------------------------------------------------------------ ------------- -------------
select sql_text,sql_id,version_count from v$sqlarea where sq 8jxznwzwkt82x 1
l_text like '%* from tab_1 where id=100%'
select /*+ index(tab_1 id_ind) */ * from tab_1 where id=100 9bb97qbdhpf3a 1
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=1 bu4t0t209yx51 1
00
SQL> select plan_hash_value from v$sql where sql_id='bu4t0t209yx51';
PLAN_HASH_VALUE
---------------
2157271952
SQL> select plan_hash_value from v$sql where sql_id='9bb97qbdhpf3a';
PLAN_HASH_VALUE
---------------
1273297064
--运行脚本，输入原SQL的sql_id和PLAN_HASH_VALUE。输出一个文件。
SQL> @/home/oracle/coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: bu4t0t209yx51
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2157271952 .042
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 2157271952
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "bu4t0t209yx51"
PLAN_HASH_VALUE: "2157271952"
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_bu4t0t209yx51_2157271952.sql
on TARGET system in order to create a custom SQL Profile
with plan 2157271952 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>1
1* DECLARE
--运行脚本，输入优化后的SQL的sql_id和PLAN_HASH_VALUE。输出一个文件。
SQL>@/home/oracle/coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 9bb97qbdhpf3a
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1273297064 .001
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1273297064
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "9bb97qbdhpf3a"
PLAN_HASH_VALUE: "1273297064"
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_9bb97qbdhpf3a_1273297064.sql
on TARGET system in order to create a custom SQL Profile
with plan 1273297064 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>
--查看生成的SQL的文件
[oracle@renCAP ~]$ cat coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql
SPO coe_xfr_sql_profile_bu4t0t209yx51_2157271952.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql 11.4.3.5 2019/05/02 carlos.sierra $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql
REM
REM DESCRIPTION
REM This script is generated by coe_xfr_sql_profile.sql
REM It contains the SQL*Plus commands to create a custom
REM SQL Profile for SQL_ID bu4t0t209yx51 based on plan hash
REM value 2157271952.
REM The custom SQL Profile to be created by this script
REM will affect plans for SQL commands with signature
REM matching the one for SQL Text below.
REM Review SQL Text and adjust accordingly.
REM
REM PARAMETERS
REM None.
REM
REM EXAMPLE
REM SQL> START coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql;
REM
REM NOTES
REM 1. Should be run as SYSTEM or SYSDBA.
REM 2. User must have CREATE ANY SQL PROFILE privilege.
REM 3. SOURCE and TARGET systems can be the same or similar.
REM 4. To drop this custom SQL Profile after it has been created:
REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_bu4t0t209yx51_2157271952');
REM 5. Be aware that using DBMS_SQLTUNE requires a license
REM for the Oracle Tuning Pack.
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+ no_index(tab_1 id_ind)
*/ * from tab_1 where id=100
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "TAB_1"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_bu4t0t209yx51_2157271952',
description => 'coe bu4t0t209yx51 2157271952 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO COE_XFR_SQL_PROFILE_bu4t0t209yx51_2157271952 completed
[oracle@renCAP ~]$
[oracle@renCAP ~]$
[oracle@renCAP ~]$
[oracle@renCAP ~]$ cat coe_xfr_sql_profile_9bb97qbdhpf3a_1273297064.sql
SPO coe_xfr_sql_profile_9bb97qbdhpf3a_1273297064.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 coe_xfr_sql_profile_9bb97qbdhpf3a_1273297064.sql 11.4.3.5 2019/05/02 carlos.sierra $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_xfr_sql_profile_9bb97qbdhpf3a_1273297064.sql
REM
REM DESCRIPTION
REM This script is generated by coe_xfr_sql_profile.sql
REM It contains the SQL*Plus commands to create a custom
REM SQL Profile for SQL_ID 9bb97qbdhpf3a based on plan hash
REM value 1273297064.
REM The custom SQL Profile to be created by this script
REM will affect plans for SQL commands with signature
REM matching the one for SQL Text below.
REM Review SQL Text and adjust accordingly.
REM
REM PARAMETERS
REM None.
REM
REM EXAMPLE
REM SQL> START coe_xfr_sql_profile_9bb97qbdhpf3a_1273297064.sql;
REM
REM NOTES
REM 1. Should be run as SYSTEM or SYSDBA.
REM 2. User must have CREATE ANY SQL PROFILE privilege.
REM 3. SOURCE and TARGET systems can be the same or similar.
REM 4. To drop this custom SQL Profile after it has been created:
REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_9bb97qbdhpf3a_1273297064');
REM 5. Be aware that using DBMS_SQLTUNE requires a license
REM for the Oracle Tuning Pack.
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+ index(tab_1 id_ind)
*/ * from tab_1 where id=100
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_9bb97qbdhpf3a_1273297064',
description => 'coe 9bb97qbdhpf3a 1273297064 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO COE_XFR_SQL_PROFILE_9bb97qbdhpf3a_1273297064 completed
[oracle@renCAP ~]$
将优化后的SQL产生的文件（coe_xfr_sql_profile_9bb97qbdhpf3a_1273297064.sql）中的部分“偷梁换柱”到原SQL产生的文件（coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql）中。并将force_match改为true。
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+ no_index(tab_1 id_ind)
*/ * from tab_1 where id=100
]';
替换后的内容
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_bu4t0t209yx51_2157271952',
description => 'coe bu4t0t209yx51 2157271952 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
--运行修改后的文件coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql
SQL>
SQL>@/home/oracle/coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_bu4t0t209yx51_2157271952.sql 11.4.3.5 2019/05/02 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2011, 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_bu4t0t209yx51_2157271952.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 bu4t0t209yx51 based on plan hash
SQL>REM value 2157271952.
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_bu4t0t209yx51_2157271952.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_bu4t0t209yx51_2157271952');
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 /*+ no_index(tab_1 id_ind)
7 */ * from tab_1 where id=100
8 ]';
9 h := SYS.SQLPROF_ATTR(
10 q'[BEGIN_OUTLINE_DATA]',
11 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
12 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
13 q'[DB_VERSION('11.2.0.4')]',
14 q'[ALL_ROWS]',
15 q'[OUTLINE_LEAF(@"SEL$1")]',
16 q'[INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))]',
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_bu4t0t209yx51_2157271952',
23 description => 'coe bu4t0t209yx51 2157271952 '||:signature||'',
24 category => 'DEFAULT',
25 validate => TRUE,
26 replace => TRUE,
27 force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
28 END;
29 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
13033897815385143953
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_bu4t0t209yx51_2157271952 completed
--创建成功之后再次查看执行计划，发现已经使用了manual类型的profile，进行了执行计划绑定。
SQL>select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
SQL>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bu4t0t209yx51, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- SQL profile coe_bu4t0t209yx51_2157271952 used for this statement
46 rows selected.
SQL>
--再次更换id值发现仍然使用优化的执行计划。
SQL>select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=200;
ID
----------
200
SQL>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bsxxutpyyphsd, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=200
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
1 - access("ID"=200)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- SQL profile coe_bu4t0t209yx51_2157271952 used for this statement
46 rows selected.
SQL>
2、SPM（for Oracle 11g and later）
实际上SQL Profile是一种亡羊补牢的手段，相对被动。而SPM（SQL plan management）是一种主动的稳定执行计划手段，只有被验证过的执行计划才会被启用，彻底解决执行计划稳定性的问题。它既能主动的稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。当启用SPM之后每一个SQL都会存在对应的SQL plan baseline，这个SQL plan baseline是存储在执行计划中的，可以从DBA_SQL_PLAN_BASELINES中查看目标SQL的所有SQL plan baseline。只有DBA_SQL_PLAN_BASELINES中列ENABLED和ACCEPTED两个列都是“YES”，SQL plan baseline对应的执行计划才会被Oracle启用。如果一个SQL有超过1个以上ENABLED和2个ACCEPTED列都是“YES”，则Oracle会从中选择成本最低的执行计划作为该SQL的执行计划。
实际上在Oracle升级或者正常运行中都需要使用SPM来稳定执行计划，主要有两种方法：
①自动捕获。
②手工生成/批量导入（Oracle大版本升级中适用）。
参数OPTIMIZER_CAPTURE_SQL_PALN_BASELINES用于控制是否启用自动捕获SQL plan baseline，默认值是FALSE，不开启自动捕获SQL plan baseline。该参数设置为TRUE后，Oracle会对该参数影响范围的所有重复执行的SQL自动捕获其SQL plan baseline，并且针对SQL第一次捕获的SQL plan baseline的ENABLED和ACCEPTED两个列都是“YES”。
当该SQL执行计划发生改变再次捕获到SQL plan baseline的ENABLED依然是“YES”，但是ACCEPTED列为“NO”，表示后续变更执行计划虽然被捕获了，但是仍然使用第一次捕获的SQL plan baseline对应的执行计划。
参数OPTIMIZER_USE_SQL_PALN_BASELINES用于控制是否使用SQL plan baseline，默认为TRUE，默认Oracle在生成执行计划就会生成SPM，使用以后的SQL plan baseline。
1.自动捕获
查看当前参数，修改为会话级别的自动捕获，不开启SQL plan baseline。
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered.
SQL> alter session set optimizer_use_sql_plan_baselines=false;
Session altered.
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean FALSE
SQL> select object_id ,object_name from tab_2 where object_id between 101 and 109;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
102 I_FIXED_OBJ$_OBJ#
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
109 VIEWCON$
9 rows selected.
--查看此时的执行计划是index range scan
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bmvhs6tfr1ja7, child number 0
-------------------------------------
select object_id ,object_name from tab_2 where object_id between 101 and 109
Plan hash value: 1855613224
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_2 | 10 | 300 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJ_IND | 10 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
2 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=101 AND "OBJECT_ID"<=109)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
46 rows selected.
--由于是第一次执行SQL，并查不到自动捕获的SQL plan baseline。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id ,object_name from tab_2%';
no rows selected
--再次执行SQL
SQL> select object_id ,object_name from tab_2 where object_id between 101 and 109;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
102 I_FIXED_OBJ$_OBJ#
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
109 VIEWCON$
9 rows selected.
--执行计划仍然是index range scan。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bmvhs6tfr1ja7, child number 0
-------------------------------------
select object_id ,object_name from tab_2 where object_id between 101 and 109
Plan hash value: 1855613224
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_2 | 10 | 300 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJ_IND | 10 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
2 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=101 AND "OBJECT_ID"<=109)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
46 rows selected.
--此时已经重复执行了目标SQL，可以看到Oracle自动捕获的SQL plan baseline。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------------------------------
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq365249919 AUTO-CAPTURE YES YES select object_id ,object_name from tab_2 where object_id bet
--增大索引OBJ_IND的聚簇因子，使SQL不使用索引。 ween 101 and 109
SQL> exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'OBJ_IND',clstfct=>40000000,no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> select index_name,clustering_factor from dba_indexes where owner='SCOTT' and index_name='OBJ_IND';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
OBJ_IND 40000000
--再次执行目标SQL。
SQL> select object_id ,object_name from tab_2 where object_id between 101 and 109;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
102 I_FIXED_OBJ$_OBJ#
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
109 VIEWCON$
9 rows selected.
--发现此时执行计划已经变成full table scan。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bmvhs6tfr1ja7, child number 0
-------------------------------------
select object_id ,object_name from tab_2 where object_id between 101 and 109
Plan hash value: 2215152728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 345 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_2 | 10 | 300 | 345 (1)| 00:00:05 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_ID"<=109 AND "OBJECT_ID">=101))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
43 rows selected.
--查看此时的SQL plan baseline，发现已经捕获了新的SQL plan baseline，由于没有开启optimizer_use_sql_plan_baselines参数，不实用SQL plan baseline，所以此时的SQL执行计划是full table scan。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------------------------------
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq32be364ab AUTO-CAPTURE YES NO select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq365249919 AUTO-CAPTURE YES YES select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
--修改参数，关闭自动捕获，开启使用SQL plan baseline
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
Session altered.
SQL> alter session set optimizer_use_sql_plan_baselines=true;
Session altered.
SQL> show parameter sql_plan;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> select index_name,clustering_factor from dba_indexes where owner='SCOTT' and index_name='OBJ_IND';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
OBJ_IND 40000000
SQL> select object_id ,object_name from tab_2 where object_id between 101 and 109;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
102 I_FIXED_OBJ$_OBJ#
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
109 VIEWCON$
9 rows selected.
--发现此时执行计划使用之前捕获的 SQL plan baseline（SQL_PLAN_395tyvgzp0nq365249919）
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bmvhs6tfr1ja7, child number 2
-------------------------------------
select object_id ,object_name from tab_2 where object_id between 101 and 109
Plan hash value: 1855613224
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4497 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_2 | 10 | 300 | 4497 (1)| 00:00:54 |
|* 2 | INDEX RANGE SCAN | OBJ_IND | 10 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
2 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=101 AND "OBJECT_ID"<=109)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_395tyvgzp0nq365249919 used for this statement
50 rows selected.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------------------------------
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq32be364ab AUTO-CAPTURE YES NO select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq365249919 AUTO-CAPTURE YES YES select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109 ween 101 and 109
--如果想改变SQL使用的SQL plan baseline，在Oracle11.1可以直接使用evolve_sql_plan_baseline关闭SQL_PLAN_395tyvgzp0nq365249919即可，但是在11.2以后需要先开启SQL_PLAN_395tyvgzp0nq32be364ab 在关闭SQL_PLAN_395tyvgzp0nq365249919。
--开启SQL_PLAN_395tyvgzp0nq32be364ab
SQL> var temp varchar2(1000);
SQL> exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_34973edbff5052c3',plan_name=>'SQL_PLAN_395tyvgzp0nq32be364ab',verify=>'NO',commit=>'YES');
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------------------------------
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq32be364ab AUTO-CAPTURE YES YES select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq365249919 AUTO-CAPTURE YES YES select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
--关闭SQL_PLAN_395tyvgzp0nq32be364ab
SQL> exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle=>'SQL_34973edbff5052c3',plan_name=>'SQL_PLAN_395tyvgzp0nq365249919',attribute_name=>'ENABLED',attribute_value=>'NO');
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ------------------------------------------------------------
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq32be364ab AUTO-CAPTURE YES YES select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
SQL_34973edbff5052c3 SQL_PLAN_395tyvgzp0nq365249919 AUTO-CAPTURE NO YES select object_id ,object_name from tab_2 where object_id bet
ween 101 and 109
--此时再次执行该SQL，已经使用SQL_PLAN_395tyvgzp0nq32be364ab SQL plan baseline，即full table scan。
SQL> select object_id ,object_name from tab_2 where object_id between 101 and 109;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
102 I_FIXED_OBJ$_OBJ#
103 MIGRATE$
104 DEPENDENCY$
105 ACCESS$
106 I_DEPENDENCY1
107 I_DEPENDENCY2
108 I_ACCESS1
109 VIEWCON$
9 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bmvhs6tfr1ja7, child number 1
-------------------------------------
select object_id ,object_name from tab_2 where object_id between 101 and 109
Plan hash value: 2215152728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 345 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_2 | 10 | 300 | 345 (1)| 00:00:05 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_ID"<=109 AND "OBJECT_ID">=101))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_395tyvgzp0nq32be364ab used for this statement
47 rows selected.
2.手工生成SQL Plan Baseline
其本质是使用dbms_spm.load_plans_from_cursor_cache通过同SQL Profile一样“偷梁换柱”的方式修改目标SQL的执行计划。
继续使用上述实验语句，加入hint使其走Full Table Scan。
SQL> select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5y5awha0zwr70, child number 0
-------------------------------------
select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101
Plan hash value: 2215152728
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 345 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_2 | 1 | 30 | 345 (1)| 00:00:05 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TAB_2"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=101)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
43 rows selected.
--查看此时的SQL plan baseline，由于没有开启自动捕获，此时查到的为空。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';
no rows selected
--手动生成SQL plan baseline，这里的SQL_id是原SQL产生执行计划的SQL_id,plan_hash_value是原SQL产生执行计划的plan_hash_value，即以上执行计划的内容。
SQL> var temp number;
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'5y5awha0zwr70',plan_hash_value=>2215152728);
PL/SQL procedure successfully completed.
--再次查看SQL plan baseline，发现已经手动产生了baseline，origin=manual-load，此时的SQL plan baseline是full table scan 。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ---------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6 SQL_PLAN_5shy2dbabx2p62be364ab MANUAL-LOAD YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--对原SQL进行hint调优，记录优化后的SQL_id，和plan_hash_value。
SQL> select /*+ index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
--查看执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3r73vgbjb12ww, child number 0
-------------------------------------
select /*+ index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101
Plan hash value: 1855613224
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 465 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_2 | 1 | 30 | 465 (0)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | OBJ_IND | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
2 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=101)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
46 rows selected.
--再次查看此时的SQL plan baseline，记录SQL_HANDLE
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ----------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6 SQL_PLAN_5shy2dbabx2p62be364ab MANUAL-LOAD YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--修改原SQL的SQL plan baseline，这里sql_id是优化后SQL的sql_id，plan_hash_value是优化后SQL的plan_hash_value，sql_handle是原SQL产生SQL plan baseline的sql_handle。
SQL> exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>'3r73vgbjb12ww',plan_hash_value=>1855613224,sql_handle=>'SQL_5c43c26ad4be8aa6');
PL/SQL procedure successfully completed.
--查看此时的SQL plan baseline，发现已经对优化后的SQL产生了新的SQL plan baseline，注意手动产生的SQL plan baseline ENABLED和ACCEPTED状态都是YES。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ----------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6 SQL_PLAN_5shy2dbabx2p62be364ab MANUAL-LOAD YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
SQL_5c43c26ad4be8aa6 SQL_PLAN_5shy2dbabx2p665249919 MANUAL-LOAD YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--删除原SQL的SQL plan baseline，sql_handle是原SQL的sql_handle，plan_name是原SQL的plan_name。
SQL> exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_5c43c26ad4be8aa6',plan_name=>'SQL_PLAN_5shy2dbabx2p62be364ab');
PL/SQL procedure successfully completed.
--查看修改后的SQL plan baseline，已经是index range scan类型的执行计划了。
SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT
------------------------------ ------------------------------ -------------- --- --- ----------------------------------------------------------------------------
SQL_5c43c26ad4be8aa6 SQL_PLAN_5shy2dbabx2p665249919 MANUAL-LOAD YES YES select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where ob
--运行原SQL再次查看执行计划，此时已经使用索引扫描了。
SQL> select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
101 FIXED_OBJ$
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5y5awha0zwr70, child number 1
-------------------------------------
select /*+ no_index(tab_2 OBJ_IND) */ object_id ,object_name from tab_2 where object_id=101
Plan hash value: 1855613224
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 465 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_2 | 1 | 30 | 465 (0)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | OBJ_IND | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_2@SEL$1
2 - SEL$1 / TAB_2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TAB_2"@"SEL$1" ("TAB_2"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=101)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "TAB_2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL plan baseline SQL_PLAN_5shy2dbabx2p665249919 used for this statement
50 rows selected.
SQL>
附录：
coe_xfr_sql_profile.sql
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.5.5 2013/03/01 carlos.sierra $
REM
REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM This script generates another that contains the commands to
REM create a manual custom SQL Profile out of a known plan from
REM memory or AWR. The manual custom profile can be implemented
REM into the same SOURCE system where the plan was retrieved,
REM or into another similar TARGET system that has same schema
REM objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM 1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM 1. SQL_ID (required)
REM 2. Plan Hash Value for which a manual custom SQL Profile is
REM needed (required). A list of known plans is presented.
REM You may choose from list provided or enter a valid phv
REM from a version of the SQL modified with Hints.
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as user with access to data dictionary.
REM Do not use SYS.
REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM plan hash value (parameters can be passed inline or until
REM requested).
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM 1. For possible errors see coe_xfr_sql_profile.log
REM 2. If SQLT is installed in SOURCE, you can use instead:
REM sqlt/utl/sqltprofile.sql
REM 3. Be aware that using DBMS_SQLTUNE requires a license for
REM Oracle Tuning Pack.
REM 4. Use a DBA user but not SYS.
REM 5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"
REM Then you may consider this change (only in a test and disposable system):
REM create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed to coe_xfr_sql_profile:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;
VAR sql_text CLOB;
VAR sql_text2 CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :sql_text2 := NULL;
EXEC :other_xml := NULL;
-- get sql_text from memory
DECLARE
l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
FOR i IN (SELECT DISTINCT piece, sql_text
FROM gv$sqltext_with_newlines
WHERE sql_id = TRIM('&&sql_id.')
ORDER BY 1, 2)
LOOP
IF :sql_text IS NULL THEN
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
END IF;
-- removes NUL characters
l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
-- adds a NUL character at the end of each line
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
END LOOP;
-- if found in memory then sql_text is not null
IF :sql_text IS NOT NULL THEN
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text FROM DUAL;
-- get sql_text from awr
DECLARE
l_sql_text VARCHAR2(32767);
l_clob_size NUMBER;
l_offset NUMBER;
BEGIN
IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
SELECT sql_text
INTO :sql_text2
FROM dba_hist_sqltext
WHERE sql_id = TRIM('&&sql_id.')
AND sql_text IS NOT NULL
AND ROWNUM = 1;
END IF;
-- if found in awr then sql_text2 is not null
IF :sql_text2 IS NOT NULL THEN
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);
l_offset := 1;
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
-- store in clob as 64 character pieces plus a NUL character at the end of each piece
WHILE l_offset < l_clob_size
LOOP
IF l_clob_size - l_offset > 64 THEN
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');
ELSE -- last piece
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');
END IF;
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
l_offset := l_offset + 64;
END LOOP;
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text2 FROM DUAL;
SELECT :sql_text FROM DUAL;
-- validate sql_text
SET TERM ON;
BEGIN
IF :sql_text IS NULL THEN
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).');
END IF;
END;
/
SET TERM OFF;
-- get other_xml from memory
BEGIN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from memory from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
SELECT :other_xml FROM DUAL;
-- validate other_xml
SET TERM ON;
BEGIN
IF :other_xml IS NULL THEN
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).');
END IF;
END;
/
SET TERM OFF;
-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
l_pos NUMBER;
l_clob_size NUMBER;
l_offset NUMBER;
l_sql_text VARCHAR2(32767);
l_len NUMBER;
l_hint VARCHAR2(32767);
BEGIN
DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql');
DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom');
DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash');
DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..');
DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script');
DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature');
DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.');
DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
DBMS_OUTPUT.PUT_LINE('REM None.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM NOTES');
DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.');
DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.');
DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.');
DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:');
DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license');
DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.');
DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired');
DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.');
DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original');
DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;');
DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');
DBMS_OUTPUT.PUT_LINE('END wa;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');
DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');
DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');
DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);
l_offset := 1;
WHILE l_offset < l_clob_size
LOOP
l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);
IF l_pos > 0 THEN
l_len := l_pos - l_offset;
ELSE -- last piece
l_len := l_clob_size - l_pos + 1;
END IF;
l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);
/* cannot do such 3 replacement since a line could end with a comment using "--"
l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP
l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP
l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP
*/
l_offset := l_offset + l_len + 1;
IF l_len > 0 THEN
IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN
l_sql_text := '['||l_sql_text||']';
ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN
l_sql_text := '{'||l_sql_text||'}';
ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN
l_sql_text := '<'||l_sql_text||'>';
ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN
l_sql_text := '('||l_sql_text||')';
ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN
l_sql_text := '"'||l_sql_text||'"';
ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN
l_sql_text := '|'||l_sql_text||'|';
ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN
l_sql_text := '~'||l_sql_text||'~';
ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN
l_sql_text := '^'||l_sql_text||'^';
ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN
l_sql_text := '@'||l_sql_text||'@';
ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN
l_sql_text := '#'||l_sql_text||'#';
ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN
l_sql_text := '%'||l_sql_text||'%';
ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN
l_sql_text := '$'||l_sql_text||'$';
ELSE
l_sql_text := CHR(96)||l_sql_text||CHR(96);
END IF;
DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');
DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
LOOP
l_hint := i.hint;
WHILE NVL(LENGTH(l_hint), 0) > 0
LOOP
IF LENGTH(l_hint) <= 500 THEN
DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
l_hint := NULL;
ELSE
l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
l_hint := ' '||SUBSTR(l_hint, l_pos);
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,');
DBMS_OUTPUT.PUT_LINE('profile => h,');
DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'',');
DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');
DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'',');
DBMS_OUTPUT.PUT_LINE('validate => TRUE,');
DBMS_OUTPUT.PUT_LINE('replace => TRUE,');
DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
DBMS_OUTPUT.PUT_LINE('PRINT signature');
DBMS_OUTPUT.PUT_LINE('PRINT signaturef');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
DBMS_OUTPUT.PUT_LINE('SPO OFF;');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
SET SERVEROUT OFF;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
CL COL
PRO
PRO COE_XFR_SQL_PROFILE completed.