在以前我们固定执行计划是使用stored outline，stored outline还是很方便的，修改一个参数，使用SQL语句就可以创建stored outline，但是它有很多不好的地方，ORACLE准备在以后的版本中不在支持stored outline这个功能了，这里我们介绍使用profile来固定执行计划，其中使用到的脚本也来致于metalink sqlt中的一个脚本。
我们的测试SQL为
SELECT * FROM scott.test a, scott.test1 b WHERE a.object_id = b.object_id AND a.owner = b.owner AND a.object_id = 100;
1，查看SQL的执行计划
SQL>SELECT * 2 FROM scott.test a, scott.test1 b 3 WHERE a.object_id = b.object_id AND a.owner = b.owner AND a.object_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 2597673609 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 186 | 38 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 186 | 38 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TEST1 | 1 | 93 | 35 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_TEST_OBJECT_OWNER | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("B"."OBJECT_ID"=100) 4 - access("A"."OBJECT_ID"=100 AND "A"."OWNER"="B"."OWNER")
2，修改表的统计信息，来达到修改相同SQL的执行计划
2，修改表的统计信息，使执行计划发生变化 修改统计信息里面的行数， SQL>exec dbms_stats.set_table_stats('SCOTT','TEST',numrows=>0); SQL>SELECT * 2 FROM scott.test a, scott.test1 b 3 WHERE a.object_id = b.object_id AND a.owner = b.owner AND a.object_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 464285522 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 186 | 32 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID | TEST1 | 1 | 93 | 20 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 186 | 32 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 12 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | INK_TEST_OBJECT_ID | 8 | | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IND_TEST1_OWNER | 592 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"."OBJECT_ID"=100) 4 - access("A"."OBJECT_ID"=100) 5 - access("A"."OWNER"="B"."OWNER")
这里我们发现了相同的SQL的执行计划已经发生了变化。我们使用profile来固定这个SQL的执行计划跟1出现的计划一样
3，创建profile
profile中使用1步骤出现的执行计划 3.1 生成profile的相当SQL语句 查询到SQL_ID为fy4rgwrkya6r5 SQL>@coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) Enter value for 1: fy4rgwrkya6r5 SQL_ID SQL_CHILD_NUMBER CHILD_NUMBER PLAN_HASH_VALUE AVG_ET_SECS -------------------- ------------ --------------- ----------- fy4rgwrkya6r5 0 2597673609 .008 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 2597673609 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "fy4rgwrkya6r5" PLAN_HASH_VALUE: "2597673609" 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_fy4rgwrkya6r5_2597673609.sql on TARGET system in order to create a custom SQL Profile with plan 2597673609 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. 这里生成了coe_xfr_sql_profile_fy4rgwrkya6r5_2597673609.sql这个文件，下面手动使用coe_xfr_sql_profile_fy4rgwrkya6r5_2597673609.sql文件来创建profile 3.2 创建PROFILE SQL>@coe_xfr_sql_profile_fy4rgwrkya6r5_2597673609.sql SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_fy4rgwrkya6r5_2597673609.sql 11.4.3.5 2013/04/16 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_fy4rgwrkya6r5_2597673609.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 fy4rgwrkya6r5 based on plan hash SQL>REM value 2597673609. 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_fy4rgwrkya6r5_2597673609.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_fy4rgwrkya6r5_2597673609'); 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 * 7 FROM scott.test a, 8 scott.test1 b 9 WHERE a.object_id = b.object_id AND a.owner = b.owner AND a.object_id = 100 10 ]'; 11 h := SYS.SQLPROF_ATTR( 12 q'[BEGIN_OUTLINE_DATA]', 13 q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 14 q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]', 15 q'[ALL_ROWS]', 16 q'[OUTLINE_LEAF(@"SEL$1")]', 17 q'[FULL(@"SEL$1" "B"@"SEL$1")]', 18 q'[INDEX(@"SEL$1" "A"@"SEL$1" ("TEST"."OBJECT_ID" "TEST"."OWNER"))]', 19 q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")]', 20 q'[USE_NL(@"SEL$1" "A"@"SEL$1")]', 21 q'[END_OUTLINE_DATA]'); 22 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); 23 DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 24 sql_text => sql_txt, 25 profile => h, 26 name => 'coe_fy4rgwrkya6r5_2597673609', 27 description => 'coe fy4rgwrkya6r5 2597673609 '||:signature||'', 28 category => 'DEFAULT', 29 validate => TRUE, 30 replace => TRUE, 31 force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); 32 END; 33 / PL/SQL procedure successfully completed. SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE --------------------- 12691825819620547740 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_fy4rgwrkya6r5_2597673609 completed
显示已经创建成功，可以通过dba_sql_profiles这个视图去查看相当的profile的信息。
4、测试profiler是否生效
SQL>set lines 170 SQL>SELECT * 2 FROM scott.test a, scott.test1 b 3 WHERE a.object_id = b.object_id AND a.owner = b.owner AND a.object_id = 100; 16 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2597673609 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 186 | 38 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 3 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 186 | 38 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL | TEST1 | 1 | 93 | 35 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IND_TEST_OBJECT_OWNER | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("B"."OBJECT_ID"=100) 4 - access("A"."OBJECT_ID"=100 AND "A"."OWNER"="B"."OWNER") Note ----- - SQL profile "coe_fy4rgwrkya6r5_2597673609" used for this statement
从SQL profile “coe_fy4rgwrkya6r5_2597673609” used for this statement这里，我们可以发现profile已经生效，执行计划也是跟步骤1一样的。
使用profile来固定执行计划：等您坐沙发呢！