Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2812337
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2013-01-11 13:44:00

最近数据库SQL执行计划老是走错,正好需要使用SQL PROFILE固定执行计划,顺便
记录一下如何使用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   carlos.sierra@oracle.com
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   carlos.sierra@oracle.com
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'


阅读(4352) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~