Manual类型的SQL Profile

发布时间：2020-08-09 05:14:46 来源：ITPUB博客阅读：180 作者：oracle单细胞栏目：关系型数据库

实验目的：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 ;

----------

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

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

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

| 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

----------

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

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

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

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

Manual类型的SQL Profile

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.

Manual类型的SQL Profile

四、将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 Manual类型的SQL Profile

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

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 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;

----------

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

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

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

| 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行。

向AI问一下细节

Manual类型的SQL Profile

猜你喜欢

最新资讯

相关推荐

相关标签