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