可以使用coe_load_sql_profile.sql脚本直接固定执行计划，该脚本也可以实现直接把sqlprofile直接迁移到其它库中。
SQLT工具包含一个脚本，SQLT可以在Doc ID 215187.1中下载。
很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定SQL执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间SQL执行计划的固定。最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。
需要注意的是，该脚本不能以SYS用户执行，否则会报如下的错误：
DECLARE
*
ERROR at line 1:
ORA-19381: cannot create staging table in SYS schema
ORA-06512: at "SYS.DBMS_SMB", line 313
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6306
ORA-06512: at line 64
示例如下：
1.1. 建立测试表和数据
SYS@dlhr> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production LHR@dlhr> create table scott.test as select * from dba_objects; Table created. LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id); Index created. ---收集统计信息 LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true); PL/SQL procedure successfully completed. LHR@dlhr> update scott.test set object_id=10 where object_id>10; LHR@dlhr> commit; Commit complete. LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID; OBJECT_ID COUNT(1) ------------------- ---------- 6 1 2 1 5 1 4 1 8 1 3 1 7 1 10 87008 9 1 9 rows selected.
可以看到object_id=10的值有87008记录，远远大于该列总记录数的5%
1.2. 执行查询语句
执行原有的查询语句，查看执行计划发现走索引，实际上这时表中大部分行的OBJECT_ID都已经被更新为10，所以走索引是不合理的。至于为什么优化器没有选择正确的执行计划是因为执行完"update scott.test set object_id=10 where object_id>10;"后会导致统计信息过期，而又没有重新收集统计信息，优化器并不知道数据发生了变化（未重新收集统计信息），所以仍然走了索引。
-----不显示记录集，只显示expain+statistics与 set autotrace traceonly等价 LHR@dlhr> set autot traceonly explain stat LHR@dlhr> LHR@dlhr> select * from scott.test where object_id=10; 87008 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2317948335 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 13036 consistent gets 0 physical reads 0 redo size 9839009 bytes sent via SQL*Net to client 64323 bytes received via SQL*Net from client 5802 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 87008 rows processed SQL> LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10; 87008 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 347 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=10) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6960 consistent gets 0 physical reads 0 redo size 4081838 bytes sent via SQL*Net to client 64323 bytes received via SQL*Net from client 5802 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 87008 rows processed SQL>
1.3. 查询上面两个语句的SQL_ID、PLAN_HASH_VALUE
LHR@dlhr> set autot off LHR@dlhr> LHR@dlhr> col sql_text format a100 LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select * from scott.test where object_id=10%'; SQL_TEXT SQL_ID PLAN_HASH_VALUE ------------------------------------------- --------------- ------------------- select * from scott.test where object_id=10 cpk9jsg2qt52r 2317948335 LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%'; SQL_TEXT SQL_ID PLAN_HASH_VALUE -------------------------------------------------------- ----------------- ------------ select /*+ full(test)*/* from scott.test where object_id=10 06c2mucgn6t5g 1357081020 --cpk9jsg2qt52r - 这是原始语句的SQL ID --06c2mucgn6t5g - 这是使用hint的SQL ID --1357081020 - 这是需要替换的plan hash value.
1.4. 把coe_load_sql_profile.sql放在$ORACLE_HOME/下
1.5. 使用coe_load_sql_profile.sql脚本
--这两个计划都需要在缓存或AWR中 --需要以具有DBA权限的用户身份连接，例如SYSTEM [ZHLHRSPMDB2:oracle]:/oracle>cd /home/oracle [ZHLHRSPMDB2:oracle]:/home/oracle>sqlplus system/oracle SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options LHR@dlhr > @/home/oracle/coe_load_sql_profile.sql Parameter 1: ORIGINAL_SQL_ID (required) Enter value for 1: cpk9jsg2qt52r Parameter 2: MODIFIED_SQL_ID (required) Enter value for 2: 06c2mucgn6t5g PLAN_HASH_VALUE AVG_ET_SECS -------------------- -------------------- 1357081020 .052 Parameter 3: PLAN_HASH_VALUE (required) Enter value for 3: 1357081020 Values passed to coe_load_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ORIGINAL_SQL_ID: "cpk9jsg2qt52r" MODIFIED_SQL_ID: "06c2mucgn6t5g" PLAN_HASH_VALUE: "1357081020" SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_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 modified SQL_ID &&modified_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> SQL>SET ECHO OFF; 0001 BEGIN_OUTLINE_DATA 0002 IGNORE_OPTIM_EMBEDDED_HINTS 0003 OPTIMIZER_FEATURES_ENABLE('11.2.0.4') 0004 DB_VERSION('11.2.0.4') 0005 OPT_PARAM('_b_tree_bitmap_plans' 'false') 0006 OPT_PARAM('_optim_peek_user_binds' 'false') 0007 OPT_PARAM('_bloom_filter_enabled' 'false') 0008 OPT_PARAM('_optimizer_extended_cursor_sharing' 'none') 0009 OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') 0010 OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') 0011 OPT_PARAM('_optimizer_use_feedback' 'false') 0012 ALL_ROWS 0013 OUTLINE_LEAF(@"SEL$1") 0014 FULL(@"SEL$1" "TEST"@"SEL$1") 0015 END_OUTLINE_DATA dropping staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" did not exist creating staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" packaging new sql profile into staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" PROFILE_NAME ------------------------------ CPK9JSG2QT52R_1357081020 SQL>REM SQL>REM SQL Profile SQL>REM ~~~~~~~~~~~ SQL>REM SQL>SELECT signature, name, category, type, status 2 FROM dba_sql_profiles WHERE name = :name; SIGNATURE NAME CATEGORY TYPE STATUS -------------------- ------------------------------------------------------------ ------------------------------------------------------------ -------------- ---------------- 10910590721604799112 CPK9JSG2QT52R_1357081020 DEFAULT MANUAL ENABLED SQL>SELECT description 2 FROM dba_sql_profiles WHERE name = :name; DESCRIPTION ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORIGINAL:CPK9JSG2QT52R MODIFIED:06C2MUCGN6T5G PHV:1357081020 SIGNATURE:10910590721604799112 CREATED BY COE_LOAD_SQL_PROFILE.SQL SQL>SET ECHO OFF; **************************************************************************** * Enter SYSTEM password to export staging table STGTAB_SQLPROF_cpk9jsg2qt52r **************************************************************************** Export: Release 11.2.0.4.0 - Production on Fri Oct 16 13:37:08 2020 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion) Note: grants on tables/views/sequences/roles will not be exported Note: indexes on tables will not be exported Note: constraints on tables will not be exported About to export specified tables via Conventional Path ... . . exporting table STGTAB_SQLPROF_CPK9JSG2QT52R 1 rows exported Export terminated successfully without warnings. If you need to implement this Custom SQL Profile on a similar system, import and unpack using these commands: imp SYSTEM file=STGTAB_SQLPROF_cpk9jsg2qt52r.dmp tables=STGTAB_SQLPROF_cpk9jsg2qt52r ignore=Y BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( profile_name => 'CPK9JSG2QT52R_1357081020', replace => TRUE, staging_table_name => 'STGTAB_SQLPROF_cpk9jsg2qt52r', staging_schema_owner => 'SYSTEM' ); END; / adding: coe_load_sql_profile_cpk9jsg2qt52r.log (deflated 77%) adding: STGTAB_SQLPROF_cpk9jsg2qt52r.dmp (deflated 88%) adding: coe_load_sql_profile.log (deflated 62%) SQL>
1.6. 查看产生的sql profile，此时原语句在不加hint的情况下也走全表扫了
SQL> select name,CATEGORY,SQL_TEXT from dba_sql_profiles; NAME CATEGORY SQL_TEXT --------------------------- ---------- ---------------------------------------- CPK9JSG2QT52R_1357081020 DEFAULT select * from scott.test where object_id=10 SQL>set line 9999 SQL> SELECT b.name, to_char(d.sql_text) sql_text, extractvalue(value(h), '.') as hints FROM dba_sql_profiles d, SYS.SQLOBJ$DATA A, SYS.SQLOBJ$ B, TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA), '/outline_data/hint'))) h where a.signature = b.signature and a.category = b.category and a.obj_type = b.obj_type and a.plan_id = b.plan_id and D.name = 'CPK9JSG2QT52R_1357081020';
1.7. 验证SQL Profile是否生效
SYS@dlhr> set autot traceonly explain stat SYS@dlhr> select * from scott.test where object_id=10; 87010 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 98 | 347 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 347 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=10) Note ----- - SQL profile "CPK9JSG2QT52R_1357081020" used for this statement Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6960 consistent gets 0 physical reads 0 redo size 4081917 bytes sent via SQL*Net to client 64323 bytes received via SQL*Net from client 5802 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 87010 rows processed SQL>