使用sql profle进行偷梁换柱的小例子
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 使用sql profle进行偷梁换柱的小例子
我们知道outline 可以用来固定sql的执行计划，但是针对未使用绑定变量的sql来讲，可能就不行了。
我最近遇到一个case，有个sql消耗很大，没有使用绑定变量，但是执行计划经常发生变化，那么怎么弄呢？
我们知道10g开始oracle 提供了sql profile功能，我们可以利用该功能来进行处理。
由于涉及到客户信息，不便透露，所以我这里用创建一个测试表来进行简单的实验：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
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。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
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. |
在当前目前会生成一个如下的文件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[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文件，内容如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
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 脚本即可，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
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> |
最后我们来看看是否达到我们需要的效果了,如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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 我们可以看到，我们的目的已经达到了。 |
2 Responses to “使用sql profle进行偷梁换柱的小例子”
[…] 参见昨天Roger的文章,exchange outline可以同样实现这种需求 参考JL的文章： Stored Outlines in Oracle 8 Stored Outlines in Oracle 9 […]
[…] 参见昨天Roger的文章,exchange outline可以同样实现这种需求 […]
Leave a Reply
You must be logged in to post a comment.