使用SQL PROFILE固定SHARED POOL中的执行计划

如何使用SQL PROFILE固定当前SHARED POOL的执行计划。

 

如果采用手工的方法，需要用到 sqlprof_attr 类型。

自己手工构造相对麻烦点。

ORACLE提供了一个脚本coe_xfr_sql_profile.sql 用来用共享池中获得SQL语句及其执行计划，

并生成一个创建SQL PROFLE的脚本，这个脚本附属在SQLT工具中。

看一下这个脚本的使用方法。

 

 

 

SQL> desc test

 名称                                                  是否为空? 类型

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

 OWNER                                                 NOT NULL VARCHAR2(30)

 OBJECT_NAME                                           NOT NULL VARCHAR2(30)

 SUBOBJECT_NAME                                                 VARCHAR2(30)

 OBJECT_ID                                             NOT NULL NUMBER

 DATA_OBJECT_ID                                                 NUMBER

 OBJECT_TYPE                                                    VARCHAR2(19)

 CREATED                                               NOT NULL DATE

 LAST_DDL_TIME                                         NOT NULL DATE

 TIMESTAMP                                                      VARCHAR2(19)

 STATUS                                                         VARCHAR2(7)

 TEMPORARY                                                      VARCHAR2(1)

 GENERATED                                                      VARCHAR2(1)

 SECONDARY                                                      VARCHAR2(1)

 

SQL> set autot traceonly exp

SQL> select count(1) from test where object_type='SYNONYM';

 

执行计划

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

Plan hash value: 3958077978

 

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

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

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

|   0 | SELECT STATEMENT      |             |     1 |     9 |    35   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE       |             |     1 |     9 |            |          |

|*  2 |   INDEX FAST FULL SCAN| IDX_CPIC_01 | 19221 |   168K|    35   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OBJECT_TYPE"='SYNONYM')

 

SQL> set autot off

SQL> column sql_text format a40

SQL> select sql_id,sql_text from v$sql where sql_text like 'select count(1) from test where object_type=%';

 

SQL_ID        SQL_TEXT

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

8ma7qaqmmt5bn select count(1) from test where object_t

              ype='SYNONYM'

 

 

SQL> select plan_hash_value from v$sql_plan where sql_id='8ma7qaqmmt5bn' and rownum=1;

 

PLAN_HASH_VALUE

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

     3958077978

 

SQL>

 

下面我们用coe_xfr_sql_profile.sql脚本生成创建SQL PROFILE的脚本。

 

SQL> @coe_xfr_sql_profile.sql 8ma7qaqmmt5bn 3958077978

 

Parameter 1:

SQL_ID (required)

 

 

 

PLAN_HASH_VALUE AVG_ET_SECS

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

     3958077978

 

Parameter 2:

PLAN_HASH_VALUE (required)

 

 

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID         : "8ma7qaqmmt5bn"

PLAN_HASH_VALUE: "3958077978"

 

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_8ma7qaqmmt5bn_3958077978.sql

on TARGET system in order to create a custom SQL Profile

with plan 3958077978 linked to adjusted sql_text.

 

 

COE_XFR_SQL_PROFILE completed.

SQL>

 

 

生成的脚本coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql内容如下：

 

SPO coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.log;

SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;

REM

REM $Header: 215187.1 coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql 11.4.4.4 2013/01/11 carlos.sierra $

REM

REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.

REM

REM AUTHOR

REM   [e-mail]

REM

REM SCRIPT

REM   coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.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 8ma7qaqmmt5bn based on plan hash

REM   value 3958077978.

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_8ma7qaqmmt5bn_3958077978.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_8ma7qaqmmt5bn_3958077978');

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 count(1) from test where object_type='SYNONYM' ]');

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.1')]',

q'[ALL_ROWS]',

q'[OUTLINE_LEAF(@"SEL$1")]',

q'[INDEX_FFS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_TYPE"))]',

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_8ma7qaqmmt5bn_3958077978',

description => 'coe 8ma7qaqmmt5bn 3958077978 '||:signature||' '||:signaturef||'',

category    => 'DEFAULT',

validate    => TRUE,

replace     => TRUE,

force_match => FALSE /* 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_8ma7qaqmmt5bn_3958077978 completed

 

 

我们可以直接使用这个脚本，也可以自己根据实际情况进行修改后在执行。

 

SQL>@coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql

SQL>REM

SQL>REM $Header: 215187.1 coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.sql 11.4.4.4 2013/01/11 carlos.sierra $

SQL>REM

SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.

SQL>REM

SQL>REM AUTHOR

SQL>REM   [e-mail]

SQL>REM

SQL>REM SCRIPT

SQL>REM   coe_xfr_sql_profile_8ma7qaqmmt5bn_3958077978.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 8ma7qaqmmt5bn based on plan hash

SQL>REM   value 3958077978.

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_8ma7qaqmmt5bn_3958077978.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_8ma7qaqmmt5bn_3958077978');

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 count(1) from test where object_type='SYNONYM' ]');

 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.1')]',

 20  q'[ALL_ROWS]',

 21  q'[OUTLINE_LEAF(@"SEL$1")]',

 22  q'[INDEX_FFS(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_TYPE"))]',

 23  q'[END_OUTLINE_DATA]');

 24  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

 25  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);

 26  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

 27  sql_text    => sql_txt,

 28  profile     => h,

 29  name        => 'coe_8ma7qaqmmt5bn_3958077978',

 30  description => 'coe 8ma7qaqmmt5bn 3958077978 '||:signature||' '||:signaturef||'',

 31  category    => 'DEFAULT',

 32  validate    => TRUE,

 33  replace     => TRUE,

 34  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

 35  DBMS_LOB.FREETEMPORARY(sql_txt);

 36  END;

 37  /

 

PL/SQL 过程已成功完成。

 

SQL>WHENEVER SQLERROR CONTINUE

SQL>SET ECHO OFF;

 

            SIGNATURE

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

  5779641010256874563

 

 

           SIGNATUREF

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

  2423517717540587142

 

 

... manual custom SQL Profile has been created

 

 

COE_XFR_SQL_PROFILE_8ma7qaqmmt5bn_3958077978 completed

 

 

SQL>select name,sql_text,status from dba_sql_profiles;

 

NAME                           SQL_TEXT                                                                 STATUS

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

coe_8ma7qaqmmt5bn_3958077978   select count(1) from test where object_type='SYNONYM'                    ENABLED

 

SQL>select sql_text,sql_profile from v$sql where sql_id='8ma7qaqmmt5bn';

 

SQL_TEXT                       SQL_PROFILE

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

select count(1) from test wher

e object_type='SYNONYM'

 

 

SQL>comment on table test is '';--将当期的执行计划淘汰出去

 

注释已创建。

 

SQL>select sql_text,sql_profile from v$sql where sql_id='8ma7qaqmmt5bn';

 

未选定行

 

SQL>set autot traceonly exp

SQL>select count(1) from test where object_type='SYNONYM';

 

执行计划

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

Plan hash value: 3958077978

 

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

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

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

|   0 | SELECT STATEMENT      |             |     1 |     9 |    35   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE       |             |     1 |     9 |            |          |

|*  2 |   INDEX FAST FULL SCAN| IDX_CPIC_01 | 19221 |   168K|    35   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OBJECT_TYPE"='SYNONYM')

 

Note

-----

   - SQL profile "coe_8ma7qaqmmt5bn_3958077978" used for this statement  <-- 可以看到SQL PROFILE已经正常工作了。

 

SQL>set autot off

SQL>select sql_text,sql_profile from v$sql where sql_id='8ma7qaqmmt5bn';

 

SQL_TEXT                       SQL_PROFILE

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

select count(1) from test wher coe_8ma7qaqmmt5bn_3958077978

e object_type='SYNONYM'