我们知道outline 可以用来固定sql的执行计划，但是针对未使用绑定变量的sql来讲，可能就不行了。
我最近遇到一个case，有个sql消耗很大，没有使用绑定变量，但是执行计划经常发生变化，那么怎么弄呢？
我们知道10g开始oracle 提供了sql profile功能，我们可以利用该功能来进行处理。
由于涉及到客户信息，不便透露，所以我这里用创建一个测试表来进行简单的实验：
SQL> conn roger/roger
Connected.
SQL> drop table t1;
Table dropped.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(1) from t1;
COUNT(1)
----------
51033
SQL> update t1 set object_id=2000 where object_id >30000;
21580 rows updated.
SQL> commit;
Commit complete.
SQL> analyze table t1 compute statistics for table for all indexes for all indexed columns;
Table analyzed.
SQL> set autot traceonly exp
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select owner,object_name from t1 where object_id=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
SQL> select owner,object_name from t1 where object_id=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21774 | 680K| 198 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 21774 | 680K| 198 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2000)
我这里的目的就是需要在执行第2个sql时，其执行计划也走index，怎么办呢？这里我想到的办法就是
利用sql profile来进行偷梁换柱，首先我们需要查到sql语句对应的sql_id。
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%select owner%';
SQL_ID
-------------
SQL_TEXT
------------------------------------------------------------------------------------
b2dzpxjnh0dq2
select owner,object_name from t1 where object_id=1000
.....省略部分信息
55hpqz51suxax
select owner,object_name from t1 where object_id=2000
.....省略部分信息
7 rows selected.
获取到sql_id以后，我们用sqlt中的脚本来创建sql profile，注意，因为我们这里是需要用object_id=1000的执行计划来替换
object_id=2000的执行计划，所以我们这里需要用到第一个sql_id:
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: b2dzpxjnh0dq2
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
190799060
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 190799060
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "b2dzpxjnh0dq2"
PLAN_HASH_VALUE: "190799060"
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_b2dzpxjnh0dq2_190799060.sql
on TARGET system in order to create a custom SQL Profile
with plan 190799060 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
在当前目前会生成一个如下的文件：
[ora10g@killdb utl]$ ls -ltr coe_x*.sql
-rw-r--r-- 1 ora10g oinstall 18248 May 2 08:27 coe_xfr_sql_profile.sql
-rw-r--r-- 1 ora10g oinstall 3405 Sep 13 08:00 coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql
[ora10g@killdb utl]$
我们需要的文件就是coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql，该文件的命名格式一目了然，那就是：
coe_xfr_sql_profile+sql_id+plan_hash_value。
我们这些需要偷梁换柱，简单一点讲就是用第一个sqlid的东西进行替换，变成第2个sql_id的，那么需要修改哪些东西呢？
我们这里把上面脚本coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql中的一些内容进行替换，替换如下部分即可：
1. 替换sql_id
b2dzpxjnh0dq2 替换成 55hpqz51suxax
2. 将force_match改成true(默认是false)
改变以后，我们随便创建一个col_xfr_sql_profile_55hpqz51suxax.sql文件，内容如下：
SPO coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.log;
SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
REM
REM $Header: 215187.1 coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql 11.4.4.4 2012/09/13 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_b2dzpxjnh0dq2_190799060.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 b2dzpxjnh0dq2 based on plan hash
REM value 190799060.
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_b2dzpxjnh0dq2_190799060.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_b2dzpxjnh0dq2_190799060');
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 owner,object_name from t1 where object_id=1000 ]');
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.5')]',
q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))]',
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_55hpqz51suxax_190799060',
description => 'coe 55hpqz51suxax 190799060 '||:signature||' '||:signaturef||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* 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_55hpqz51suxax_190799060 completed
然后我们执行col_xfr_sql_profile_55hpqz51suxax.sql 脚本即可，如下：
SQL>@col_xfr_sql_profile_55hpqz51suxax.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_b2dzpxjnh0dq2_190799060.sql 11.4.4.4 2012/09/13 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_b2dzpxjnh0dq2_190799060.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 b2dzpxjnh0dq2 based on plan hash
SQL>REM value 190799060.
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_b2dzpxjnh0dq2_190799060.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_b2dzpxjnh0dq2_190799060');
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 owner,object_name from t1 where object_id=1000 ]');
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.5')]',
20 q'[OPT_PARAM('_optim_peek_user_binds' 'false')]',
21 q'[ALL_ROWS]',
22 q'[OUTLINE_LEAF(@"SEL$1")]',
23 q'[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))]',
24 q'[END_OUTLINE_DATA]');
25 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
26 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
27 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
28 sql_text => sql_txt,
29 profile => h,
30 name => 'coe_55hpqz51suxax_190799060',
31 description => 'coe 55hpqz51suxax 190799060 '||:signature||' '||:signaturef||'',
32 category => 'DEFAULT',
33 validate => TRUE,
34 replace => TRUE,
35 force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
36 DBMS_LOB.FREETEMPORARY(sql_txt);
37 END;
38 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
11682228169837078685
SIGNATUREF
---------------------
7649568398284820358
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_55hpqz51suxax_190799060 completed
SQL>
最后我们来看看是否达到我们需要的效果了,如下：
SQL> set lines 160
SQL> set autot traceonly
SQL> select owner,object_name from t1 where object_id=2000;
21581 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 190799060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21699 | 741K| 519 (0)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 21699 | 741K| 519 (0)| 00:00:07 |
|* 2 | INDEX RANGE SCAN | IDX_T1_ID | 21699 | | 52 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2000)
Note
-----
- SQL profile "coe_55hpqz51suxax_190799060" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3312 consistent gets
13 physical reads
0 redo size
815439 bytes sent via SQL*Net to client
16218 bytes received via SQL*Net from client
1440 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21581 rows processed
我们可以看到，我们的目的已经达到了。
发表回复