①、 coe_load_sql_profile.sql

可以使用coe_load_sql_profile.sql脚本直接固定执行计划，该脚本也可以实现直接把sqlprofile直接迁移到其它库中。

很多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.建立测试表和数据

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

         7          1

         5          1

         8          1

         3          1

         2          1

        10      87076

         4          1

         9          1

9 rows selected.

2.执行查询语句

执行原有的查询语句，查看执行计划发现走索引，实际上这时表中大部分行的OBJECT_ID都已经被更新为10，所以走索引是不合理的。

LHR@dlhr> set autot traceonly explain stat

LHR@dlhr>

LHR@dlhr> select * from scott.test where object_id=10;

87076 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3384190782

-------------------------------------------------------------------------------------------

| 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

      13060  consistent gets

          0  physical reads

          0  redo size

    9855485  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10;

87076 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 217508114

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    98 |   351   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   351   (2)| 00:00:05 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=10)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       6973  consistent gets

          0  physical reads

          0  redo size

    4159482  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

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

4.把coe_load_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。

5.使用coe_load_sql_profile.sql脚本

[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba

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                 .058

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.3')

0004 DB_VERSION('11.2.0.3')

0005 ALL_ROWS

0006 OUTLINE_LEAF(@"SEL$1")

0007 FULL(@"SEL$1" "TEST"@"SEL$1")

0008 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 LHR password to export staging table STGTAB_SQLPROF_cpk9jsg2qt52r

****************************************************************************

Export: Release 11.2.0.3.0 - Production on Tue Sep 12 10:39:16 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

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 LHR 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 => 'LHR' );

END;

/

  adding: coe_load_sql_profile_cpk9jsg2qt52r.log (deflated 76%)

  adding: STGTAB_SQLPROF_cpk9jsg2qt52r.dmp (deflated 89%)

  adding: coe_load_sql_profile.log (deflated 62%)

deleting: coe_load_sql_profile.log

coe_load_sql_profile completed.

SQL>

6.查看产生的sql profile，此时原语句在不加hint的情况下也走全表扫了
select * from dba_sql_profiles;

SQL>set line 9999

SQL> SELECT b.name,to_char(d.sql_text) sql_text,  extractvalue(value(h),'.') as hints

  2    FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,

       SYS.SQLOBJ$ B,

  4         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),

  5                                   '/outline_data/hint'))) h

 where a.signature = b.signature

  7     and a.category = b.category

  8     and a.obj_type = b.obj_type

  9     and a.plan_id = b.plan_id

 10     and a.signature=d.signature

 11     and D.name = 'CPK9JSG2QT52R_1357081020';

NAME                           SQL_TEXT                                           HINTS

------------------------------ -------------------------------------------------- --------------------------------------------------

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        BEGIN_OUTLINE_DATA

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        IGNORE_OPTIM_EMBEDDED_HINTS

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        DB_VERSION('11.2.0.3')

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        ALL_ROWS

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        OUTLINE_LEAF(@"SEL$1")

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        FULL(@"SEL$1" "TEST"@"SEL$1")

CPK9JSG2QT52R_1357081020       select * from scott.test where object_id=10        END_OUTLINE_DATA

7.验证SQL Profile是否生效

SYS@dlhr> set autot traceonly explain stat

SYS@dlhr> select * from scott.test where object_id=10;

87076 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 217508114

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    98 |   351   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   351   (2)| 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

       6973  consistent gets

          0  physical reads

          0  redo size

    4159482  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed