千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

Manual类型的SQL Profile

发表于：2026-11-02 作者：千家信息网编辑
千家信息网最后更新 2026年11月02日，实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.DROP_SQL_PROFIL
千家信息网最后更新 2026年11月02日Manual类型的SQL Profile

实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定

关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.DROP_SQL_PROFILE、 coe_xfr_sql_profile.sql

一、创建表，收集表统计信息

SQL> create table t1( n number);

表已创建。

SQL> declare

2 begin

3 for i in 1 .. 10000

4 loop

5 insert into t1 values(i);

6 commit;

7 end loop;

8 end;

9 /

PL/SQL 过程已成功完成。

SQL> select count(*) from t1;

COUNT(*)

----------

10000

SQL> create index idx_t1 on t1(n);

索引已创建。

SQL> exec dbms_stats.gather_table_stats( ownname =>'TEST' , tabname =>'T1' , method_opt =>'for all columns size 1', CASCADE => TRUE);

PL/SQL 过程已成功完成。

二是查找到SQL_PROFILE,并将相应的SQL_PROFILE删除

SQL> SET LONG 9000

SQL> SET LONGCHUNKSIZE 1000

SQL> SET LINESIZE 2000

SQL> SELECT NAME ,SQL_TEXT, TYPE,STATUS,FORCE_MATCHING FROM DBA_SQL_PROFILES WHERE SQL_TEXT LIKE 'SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE%';

NAME SQL_TEXT

TYPE STATUS FORCE_

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

SYS_SQLPROF_0162663bdb700000 SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1

MANUAL ENABLED NO

SYS_SQLPROF_01626643a6130001 SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1

MANUAL ENABLED YES

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_01626643a6130001');

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0162663bdb700000');

PL/SQL 过程已成功完成。

三是调用coe_xfr_sql_profile.sql，产生Manual类型的SQL PROFILE脚本

SQL> SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1 ;

N

----------

1

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID gn8zuq00kd86g, child number 0

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

SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1

Plan hash value: 3617692013

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

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

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

| 0 | SELECT STATEMENT | | | | 7 (100)| |

|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

PLAN_TABLE_OUTPUT

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

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T1"@"SEL$1")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

1 - filter("N"=1)

Column Projection Information (identified by operation id):

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

1 - "N"[NUMBER,22]

已选择42行。

SQL> SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3;

Nse

----------

3

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 866w0nx37z5kg, child number 0

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

SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3

Plan hash value: 1369807930

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

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)| |

|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

PLAN_TABLE_OUTPUT

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

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

1 - access("N"=3)

Column Projection Information (identified by operation id):

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

1 - "N"[NUMBER,22]

已选择42行。

SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%';

SQL_TEXT

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

SQL_ID VERSION_COUNT

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

SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%'

9bvng6dz8ct9z 1

SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3

866w0nx37z5kg 1

SQL> SELECT PLAN_HASH_VALUE FROM V$SQL WHERE SQL_ID='866w0nx37z5kg';

PLAN_HASH_VALUE

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

1369807930

SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

输入 1 的值: 866w0nx37z5kg --条件为 N=3 的SQL_ID

PLAN_HASH_VALUE AVG_ET_SECS

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

1369807930 .001

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值: 1369807930

Values passed to coe_xfr_sql_profile:

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

SQL_ID : "866w0nx37z5kg"

PLAN_HASH_VALUE: "1369807930"

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

on TARGET system in order to create a custom SQL Profile

with plan 1369807930 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

输入 1 的值: gn8zuq00kd86g --条件为 N=1的SQL_ID

PLAN_HASH_VALUE AVG_ET_SECS

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

3617692013 .002

Parameter 2:

PLAN_HASH_VALUE (required)

输入 2 的值: 3617692013

Values passed to coe_xfr_sql_profile:

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

SQL_ID : "gn8zuq00kd86g"

PLAN_HASH_VALUE: "3617692013"

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

on TARGET system in order to create a custom SQL Profile

with plan 3617692013 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

四、将coe_xfr_sql_profile_866w0nx37z5kg_1369807930.sql中HINT组合

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

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

q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]',

q'[END_OUTLINE_DATA]');

替换 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

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

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

q'[END_OUTLINE_DATA]');

:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

并将 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql中的参数 FORCE_MATCH 的值由 FALSE 替换 TRUE

五、执行脚本，调整执行计划

SQL> @C:\Users\YX\coe_xfr_sql_profile_gn8zuq00kd86g_3617692013（修改后）.sql

SQL>REM

SQL>REM $Header: 215187.1 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql 11.4.3.5 2018/03/28 carlos.sierra $

SQL>REM

SQL>REM Copyright (c) 2000-2011, 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_gn8zuq00kd86g_3617692013.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 gn8zuq00kd86g based on plan hash

SQL>REM value 3617692013.

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

SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license

SQL>REM for the Oracle Tuning Pack.

SQL>REM

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL>REM

SQL>VAR signature NUMBER;

SQL>REM

SQL>DECLARE

2 sql_txt CLOB;

3 h SYS.SQLPROF_ATTR;

4 BEGIN

5 sql_txt := q'[

6 SELECT /*+ NO_INDEX(T1 IDX_T1)

7 */ * FROM T1 WHERE N=1

8 ]';

9 h := SYS.SQLPROF_ATTR(

10 q'[BEGIN_OUTLINE_DATA]',

11 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

12 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

13 q'[DB_VERSION('11.2.0.4')]',

14 q'[ALL_ROWS]',

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

16 q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]',

17 q'[END_OUTLINE_DATA]');

18 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

19 sql_text => sql_txt,

20 profile => h,

21 name => 'coe_gn8zuq00kd86g_3617692013',

22 description => 'coe gn8zuq00kd86g 3617692013 '||:signature||'',

23 category => 'DEFAULT',

24 validate => TRUE,

25 replace => TRUE,

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

27 END;

28 /

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTIN UE

SQL>SET ECHO OFF;

SIGNATURE

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

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_gn8zuq00kd86g_3617692013 completed

六、查看执行计划

SQL> SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1;

N

----------

1

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID gn8zuq00kd86g, child number 0

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

SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1

Plan hash value: 1369807930

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

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)| |

|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

PLAN_TABLE_OUTPUT

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

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

1 - access("N"=1)

Column Projection Information (identified by operation id):

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

1 - "N"[NUMBER,22]

Note

-----

PLAN_TABLE_OUTPUT

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

- SQL profile coe_gn8zuq00kd86g_3617692013 used for this statement

已选择46行。


很赞哦！
成功 过程 脚本 输入 类型 选择 条件 并将 信息 关键 关键词 参数 目的 索引 实验 组合 统计 调整 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 互联网科技工作委员会毛军 新华互联网科技学校 互联网通讯科技 疫情对互联网科技公司影响 奇士达互联网科技 互联网科技英文翻译 左右互联网络科技 恒生科技互联网指数分析 北京华百购互联网科技有限公司 浙江互联网科技研学营 岳阳华臻互联网科技有限公司 赶超互联网科技 微信多开 链环科技传统企业怎么转型互联网 互联网科技研发水平 互联网在线教育科技创业 科技互联网海报 卡尔迅互联网科技有限公司 互联网科技对化妆品行业 上海互联网科技园景观 中国主要互联网科技公司 北京互联网科技 互联网科技媒体 齐家互联网科技有限公司 团飞科技与伯堂互联网服务平台 互联网方面的科技前沿 广东工业互联网大赛深圳学泰科技 广州千川互联网科技有限公司 广州燕处互联网科技有限公司 长沙易易互联网科技有限公司 北京瑞云互联网科技有限公司

相关文章

0