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

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

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

SQL> createtable t1( n number);

表已创建。

SQL> declare

2begin

3fori in 1 .. 10000

4loop

5insertinto t1 values(i);

6commit;

7end loop;

8end;

9/

PL/SQL 过程已成功完成。

SQL>selectcount(*)fromt1;

COUNT(*)

----------

10000

SQL> create indexidx_t1ont1(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 LONGCHUNKSIZE1000

SQL> SET LINESIZE 2000

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

NAMESQL_TEXT

TYPESTATUSFORCE_

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

SYS_SQLPROF_0162663bdb700000SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE N=1

MANUALENABLEDNO

SYS_SQLPROF_01626643a6130001SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 WHERE N=1

MANUALENABLEDYES

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) */*FROMT1 WHERE N=1 ;

N

----------

1

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

PLAN_TABLE_OUTPUT

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

SQL_IDgn8zuq00kd86g, child number 0

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

SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 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_ID866w0nx37z5kg, 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_IDVERSION_COUNT

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

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

9bvng6dz8ct9z1

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

866w0nx37z5kg1

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

2IF :sql_text IS NULL THEN

3RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

4END IF;

5END;

6/

SQL>SET TERM OFF;

SQL>BEGIN

2IF :other_xml IS NULL THEN

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

4END IF;

5END;

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

2IF :sql_text IS NULL THEN

3RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

4END IF;

5END;

6/

SQL>SET TERM OFF;

SQL>BEGIN

2IF :other_xml IS NULL THEN

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

4END IF;

5END;

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>REMcarlos.sierra@oracle.com

SQL>REM

SQL>REM SCRIPT

SQL>REMcoe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

SQL>REM

SQL>REM DESCRIPTION

SQL>REMThis script is generated by coe_xfr_sql_profile.sql

SQL>REMIt contains the SQL*Plus commands to create a custom

SQL>REMSQL Profile for SQL_ID gn8zuq00kd86g based on plan hash

SQL>REMvalue 3617692013.

SQL>REMThe custom SQL Profile to be created by this script

SQL>REMwill affect plans for SQL commands with signature

SQL>REMmatching the one for SQL Text below.

SQL>REMReview SQL Text and adjust accordingly.

SQL>REM

SQL>REM PARAMETERS

SQL>REMNone.

SQL>REM

SQL>REM EXAMPLE

SQL>REMSQL> START coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql;

SQL>REM

SQL>REM NOTES

SQL>REM1. Should be run as SYSTEM or SYSDBA.

SQL>REM2. User must have CREATE ANY SQL PROFILE privilege.

SQL>REM3. SOURCE and TARGET systems can be the same or similar.

SQL>REM4. To drop this custom SQL Profile after it has been created:

SQL>REMEXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_gn8zuq00kd86g_3617692013');

SQL>REM5. Be aware that using DBMS_SQLTUNE requires a license

SQL>REMfor the Oracle Tuning Pack.

SQL>REM

SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL>REM

SQL>VAR signature NUMBER;

SQL>REM

SQL>DECLARE

2sql_txt CLOB;

3hSYS.SQLPROF_ATTR;

4BEGIN

5sql_txt := q'[

6SELECT/*+NO_INDEX(T1 IDX_T1)

7*/*FROMT1 WHERE N=1

8]';

9h := SYS.SQLPROF_ATTR(

10q'[BEGIN_OUTLINE_DATA]',

11q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

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

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

14q'[ALL_ROWS]',

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

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

17q'[END_OUTLINE_DATA]');

18DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

19sql_text=> sql_txt,

20profile=> h,

21name=> 'coe_gn8zuq00kd86g_3617692013',

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

23category=> 'DEFAULT',

24validate=> TRUE,

25replace=> TRUE,

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

27END;

28/

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTINUE

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) */*FROMT1 WHERE N=1;

N

----------

1

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

PLAN_TABLE_OUTPUT

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

SQL_IDgn8zuq00kd86g, child number 0

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

SELECT/*+NO_INDEX(T1 IDX_T1) */*FROMT1 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行。