如何使用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'
相关推荐
oracle sqlt工具脚本,用来使用sql_profile绑定内存中已有的执行计划
通过分析SQL语句的执行计划优化SQL,F5执行计划如何优化
SQLSERVER执行计划 解剖 SQL SERVER 执行计划
摘要:本文描述了11g的新特性之一:执行计划管理,介绍了引入该新特性的原因,以及该新特性的相关特点、工作原理等。最后通过引入一个测试案例来介绍如何... 分析了如何固定优化过的执行计划,避免被sql优化器CBO覆盖
SQL语句性能调整 ORACLE的执行计划.
db2查看sql执行计划
其中的关键在于如何得到SQL语句的执行计划和如何从SQL语句的执行计划中发现问题。总是想将日常经验的点点滴滴总结一下,但是直到最近才下定决心,总共花了3个周末时间,才将其整理成册,便于自己日常工作。不好意思...
Mybatis中执行String类型的自己拼写的sql,不执行配置文件中的sql mybatis sql connection mybatis中使用sql,不实用配置文件中的sql实例
由于sql文件过大,超过了100M,再数据库的窗口执行,结果超出内存了,对于特别大的sql文件可以使用sqlcmd进行执行 ###1.打开cmd窗口 运行–cmd–进入到sql文件所在的文件夹。 如果是win7可按Shift+右键 在此窗口打开...
Oracle在执行一个SQL之前,首先需要看一下SQL的执行计划,然后在按照执行计划执行SQL,分析执行计划的工作是由优化器来执行的,在不同的条件下,一个SQL可能存在多条执行计划,但是在某个特定的时间点,特定的环境下...
剖析SQL Server执行计划
SQL ORACLE 执行计划 分析 数据库
config/sql.txt 中指定需要定时执行的sql语句,使用<<-sql->>分隔各语句。如: update deviceInfo set ps=1 where status=1 <<-sql->> delete from deviceInfo set ps=1 where status=0 <<-sql->> update deviceInfo...
Oracle SQL执行计划分析器功能的创建3步曲: 1 首先,编译XYG_ALD_SESS_PKG的Package头。 (XYG_ALD_SESS_PKG.sql) 2 接着要建立好下面的4个视图对象。因为XYG_ALD_SESS_PKG包体会用到。(View Create Script v...
WEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB页面执行SQLWEB...
执行计划是SQL优化中最为复杂也是最为关键的部分,只有知道了ORACLE在内部到底是如何执行该SQL语句后,我们才能知道优化器选择的执行计划是否为最优的。执行计划对于DBA来说,就象财务报表对于财务人员一样重要。...
如何取得 sql 2005的执行计划 query plan
获取 SQL 语句的执行计划 方法 1:使用 autotrace 查看执行计划 配置使用 autotrace 下面的例子为用户 scott 配置使用 autotrace。 创建角色 PLUSTRACE,并将角色授予用户 scott
详解一个sql server执行计划样例,有借鉴意义哦