最近数据库SQL执行计划老是走错，正好需要使用SQL PROFILE固定执行计划，顺便
记录一下如何使用SQL PROFILE固定当前SHARED POOL的执行计划。
如果采用手工的方法，需要用到 sqlprof_attr 类型。
自己手工构造相对麻烦点。
ORACLE提供了一个脚本coe_xfr_sql_profile.sql 用来用共享池中获得SQL语句及其执行计划，
并生成一个创建SQL PROFLE的脚本，这个脚本附属在SQLT工具中。
看一下这个脚本的使用方法。
SQL> desc test
名称 是否为空? 类型
----------------------------------------------------- -------- ------------------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> set autot traceonly exp
SQL> select count(1) from test where object_type='SYNONYM';
执行计划
----------------------------------------------------------
Plan hash value: 3958077978
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 35 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_CPIC_01 | 19221 | 168K| 35 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='SYNONYM')
SQL> set autot off
SQL> column sql_text format a40
SQL> select sql_id,sql_text from v$sql where sql_text like 'select count(1) from test where object_type=%';
SQL_ID SQL_TEXT
------------- ----------------------------------------
8ma7qaqmmt5bn select count(1) from test where object_t
ype='SYNONYM'
SQL> select plan_hash_value from v$sql_plan where sql_id='8ma7qaqmmt5bn' and rownum=1;
PLAN_HASH_VALUE
---------------
3958077978
SQL>
下面我们用coe_xfr_sql_profile.sql脚本生成创建SQL PROFILE的脚本。
SQL> @coe_xfr_sql_profile.sql 8ma7qaqmmt5bn 3958077978
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3958077978
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "8ma7qaqmmt5bn"
PLAN_HASH_VALUE: "3958077978"
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_8ma7qaqmmt5bn_3958077978.sql
on TARGET system in order to create a custom SQL Profile
with plan 3958077978 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>
生成的脚本coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql内容如下：
SPO coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql 11.4.4.4 2013/01/11 carlos.sierra $
REM
REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.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 8ma7qaqmmt5bn based on plan hash
REM value 3958077978.
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_8ma7qaqmmt5bn_3958077978.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_8ma7qaqmmt5bn_3958077978');
REM 5. Be aware that using DBMS_SQLTUNE requires a license
REM for the Oracle Tuning Pack.
REM 6. If you modified a SQL putting Hints in order to produce a desired
REM Plan, you can remove the artifical Hints from SQL Text pieces below.
REM By doing so you can create a custom SQL Profile for the original
REM SQL but with the Plan captured from the modified SQL (with Hints).
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
VAR signature NUMBER;
VAR signaturef NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
PROCEDURE wa (p_line IN VARCHAR2) IS
BEGIN
DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
END wa;
BEGIN
DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
-- SQL Text pieces below do not have to be of same length.
-- So if you edit SQL Text (i.e. removing temporary Hints),
-- there is no need to edit or re-align unmodified pieces.
wa(q'[select count(1) from test where object_type='SYNONYM' ]');
DBMS_LOB.CLOSE(sql_txt);
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_FFS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_TYPE"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
:signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_8ma7qaqmmt5bn_3958077978',
description => 'coe 8ma7qaqmmt5bn 3958077978 '||:signature||' '||:signaturef||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
DBMS_LOB.FREETEMPORARY(sql_txt);
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRINT signaturef
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_8ma7qaqmmt5bn_3958077978 completed
我们可以直接使用这个脚本，也可以自己根据实际情况进行修改后在执行。
SQL>@coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql 11.4.4.4 2013/01/11 carlos.sierra $
SQL>REM
SQL>REM 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_8ma7qaqmmt5bn_3958077978.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 8ma7qaqmmt5bn based on plan hash
SQL>REM value 3958077978.
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_8ma7qaqmmt5bn_3958077978.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_8ma7qaqmmt5bn_3958077978');
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 count(1) from test where object_type='SYNONYM' ]');
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('10.2.0.1')]',
20 q'[ALL_ROWS]',
21 q'[OUTLINE_LEAF(@"SEL$1")]',
22 q'[INDEX_FFS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_TYPE"))]',
23 q'[END_OUTLINE_DATA]');
24 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
25 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
26 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
27 sql_text => sql_txt,
28 profile => h,
29 name => 'coe_8ma7qaqmmt5bn_3958077978',
30 description => 'coe 8ma7qaqmmt5bn 3958077978 '||:signature||' '||:signaturef||'',
31 category => 'DEFAULT',
32 validate => TRUE,
33 replace => TRUE,
34 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
35 DBMS_LOB.FREETEMPORARY(sql_txt);
36 END;
37 /
PL/SQL 过程已成功完成。
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
5779641010256874563
SIGNATUREF
---------------------
2423517717540587142
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_8ma7qaqmmt5bn_3958077978 completed
SQL>select name,sql_text,status from dba_sql_profiles;
NAME SQL_TEXT STATUS
------------------------------ -------------------------------------------------------------------------------- --------
coe_8ma7qaqmmt5bn_3958077978 select count(1) from test where object_type='SYNONYM' ENABLED
SQL>select sql_text,sql_profile from v$sql where sql_id='8ma7qaqmmt5bn';
SQL_TEXT SQL_PROFILE
------------------------------ ------------------------------
select count(1) from test wher
e object_type='SYNONYM'
SQL>comment on table test is '';--将当期的执行计划淘汰出去
注释已创建。
SQL>select sql_text,sql_profile from v$sql where sql_id='8ma7qaqmmt5bn';
未选定行
SQL>set autot traceonly exp
SQL>select count(1) from test where object_type='SYNONYM';
执行计划
----------------------------------------------------------
Plan hash value: 3958077978
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 35 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_CPIC_01 | 19221 | 168K| 35 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='SYNONYM')
Note
-----
- SQL profile "coe_8ma7qaqmmt5bn_3958077978" used for this statement <-- 可以看到SQL PROFILE已经正常工作了。
SQL>set autot off
SQL>select sql_text,sql_profile from v$sql where sql_id='8ma7qaqmmt5bn';
SQL_TEXT SQL_PROFILE
------------------------------ ------------------------------
select count(1) from test wher coe_8ma7qaqmmt5bn_3958077978
e object_type='SYNONYM'
coe_xfr_sql_profile.rar