最麻煩的sqlprof_attr('FULL(t1@SEL$1)')是這裡的格式如何寫.在mos上的文章note 215187.1中的sqlt.zip的目錄utl中提供了指令碼coe_xfr_sql_profile.sql可以生成這些資訊.

1.建立測試表和資料

SYS@dlhr> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

LHR@dlhr> create table scott.test as select * from dba_objects;

Table created.

LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id);

Index created.

LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true);

PL/SQL procedure successfully completed.

LHR@dlhr> update scott.test set object_id=10 where object_id>10;

LHR@dlhr> commit;

Commit complete.

LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID;

OBJECT_ID   COUNT(1)

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

         6          1

         7          1

         5          1

         8          1

         3          1

         2          1

        10      87076

         4          1

         9          1

9 rows selected.

2.執行查詢語句
--執行原有的查詢語句,檢視執行計劃發現走索引,實際上這時表中大部分行的object_id都已經被更新為10,所以走索引是不合理的.

LHR@dlhr>

LHR@dlhr> set autot traceonly explain stat

LHR@dlhr>

LHR@dlhr> select * from scott.test where object_id=10;

87076 rows selected.

Execution Plan

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

Plan hash value: 3384190782

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

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

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

|   0 | SELECT STATEMENT            |             |     1 |    98 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    98 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_01 |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=10)

Statistics

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

          0  recursive calls

          0  db block gets

      13060  consistent gets

          0  physical reads

          0  redo size

    9855485  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10;

87076 rows selected.

Execution Plan

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

Plan hash value: 217508114

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    98 |   351   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   351   (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"=10)

Statistics

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

          1  recursive calls

          0  db block gets

       6973  consistent gets

          0  physical reads

          0  redo size

    4159482  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

3.查詢上面兩個語句的sql_id,plan_hash_value

LHR@dlhr> set autot off

LHR@dlhr>

LHR@dlhr> col sql_text format a100

LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql

  2  where sql_text like 'select * from scott.test where object_id=10%';

SQL_TEXT                                                                                             SQL_ID        PLAN_HASH_VALUE

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

select * from scott.test where object_id=10                                                          cpk9jsg2qt52r      3384190782

LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql

  2  where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%';

SQL_TEXT                                                                                             SQL_ID        PLAN_HASH_VALUE

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

select /*+ full(test)*/* from scott.test where object_id=10                                          06c2mucgn6t5g       217508114

4.把coe_xfr_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。

5.對上面的兩個sql產生outline data的sql.

[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>

[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SYS@dlhr> @$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql cpk9jsg2qt52r 3384190782

Parameter 1:

SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS

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

     3384190782        .046

Parameter 2:

PLAN_HASH_VALUE (required)

Values passed to coe_xfr_sql_profile:

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

SQL_ID         : "cpk9jsg2qt52r"

PLAN_HASH_VALUE: "3384190782"

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

on TARGET system in order to create a custom SQL Profile

with plan 3384190782 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>@$ORACLE_HOME/rdbms/admin/coe_xfr_sql_profile.sql 06c2mucgn6t5g 217508114

Parameter 1:

SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS

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

      217508114        .113

Parameter 2:

PLAN_HASH_VALUE (required)

Values passed to coe_xfr_sql_profile:

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

SQL_ID         : "06c2mucgn6t5g"

PLAN_HASH_VALUE: "217508114"

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

on TARGET system in order to create a custom SQL Profile

with plan 217508114 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

6.替換檔案coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql中的SYS.SQLPROF_ATTR部分,把它更改為
coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql中產生的SYS.SQLPROF_ATTR部分，其中：

coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql的SYS.SQLPROF_ATTR

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_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."OBJECT_ID"))]',

q'[END_OUTLINE_DATA]');

----coe_xfr_sql_profile_06c2mucgn6t5g_217508114.sql的SYS.SQLPROF_ATTR

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" "TEST"@"SEL$1")]',

q'[END_OUTLINE_DATA]');

生成的檔案在當前目錄：

7.執行替換過SYS.SQLPROF_ATTR的SQL,coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

SQL> @/tmp/coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

SQL>@coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql

SQL>REM

SQL>REM $Header: 215187.1 coe_xfr_sql_profile_cpk9jsg2qt52r_3384190782.sql 11.4.4.4 2016/05/26 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_cpk9jsg2qt52r_3384190782.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 cpk9jsg2qt52r based on plan hash

SQL>REM   value 3384190782.

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

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 * from scott.test where object_id=10]');

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('11.2.0.4')]',

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

21  q'[ALL_ROWS]',

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

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

24  q'[END_OUTLINE_DATA]');

25  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

26  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);

27  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

28  sql_text    => sql_txt,

29  profile     => h,

30  name        => 'coe_cpk9jsg2qt52r_3384190782',

31  description => 'coe cpk9jsg2qt52r 3384190782 '||:signature||' '||:signaturef||'',

32  category    => 'DEFAULT',

33  validate    => TRUE,

34  replace     => TRUE,

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

36  DBMS_LOB.FREETEMPORARY(sql_txt);

37  END;

38  /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE

SQL>SET ECHO OFF;

            SIGNATURE

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

10910590721604799112

           SIGNATUREF

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

15966118871002195466

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_cpk9jsg2qt52r_3384190782 completed

8.檢視產生的sql profile,此時原語句在不加hint的情況下也走全表掃了
select * from dba_sql_profiles;

SYS@dlhr> col sql_text for a50

SYS@dlhr> col hints for a50

SYS@dlhr>  SELECT b.name,to_char(d.sql_text) sql_text,  extractvalue(value(h),'.') as hints

  2     FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,

  3          SYS.SQLOBJ$ B,

  4          TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),

  5                                    '/outline_data/hint'))) h

  6    where a.signature = b.signature

  7      and a.category = b.category

  8      and a.obj_type = b.obj_type

  9      and a.plan_id = b.plan_id

10      and a.signature=d.signature

11      and D.name = 'coe_cpk9jsg2qt52r_3384190782';

NAME                           SQL_TEXT                                           HINTS

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

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        BEGIN_OUTLINE_DATA

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        IGNORE_OPTIM_EMBEDDED_HINTS

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        DB_VERSION('11.2.0.4')

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        ALL_ROWS

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        OUTLINE_LEAF(@"SEL$1")

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        FULL(@"SEL$1" "TEST"@"SEL$1")

coe_cpk9jsg2qt52r_3384190782   select * from scott.test where object_id=10        END_OUTLINE_DATA

8 rows selected.

SYS@dlhr>

9.驗證SQL Profile是否生效

SYS@dlhr> set autot traceonly explain stat

SYS@dlhr> select * from scott.test where object_id=10;

87076 rows selected.

Execution Plan

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

Plan hash value: 217508114

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |    98 |   351   (2)| 00:00:05 |

|*  1 |  TABLE ACCESS FULL| TEST |     1 |    98 |   351   (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"=10)

Note

-----

   - SQL profile "coe_cpk9jsg2qt52r_3384190782" used for this statement

Statistics

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

          0  recursive calls

          0  db block gets

       6973  consistent gets

          0  physical reads

          0  redo size

    4159482  bytes sent via SQL*Net to client

      64375  bytes received via SQL*Net from client

       5807  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      87076  rows processed

注意:
1.這個測試只是為了演示通過coe_xfr_sql_profile.sql實現手動加hint的方法,實際上面的語句問題的處理最佳的方法應該是重新收集scott.test的統計資訊才對.
2.當一條sql既有sql profile又有stored outline時,優化器優先選擇stored outline.
3.force_match引數,TRUE:FORCE (match even when different literals in SQL),FALSE:EXACT (similar to CURSOR_SHARING).
4.通過sql profile手動加hint的方法很簡單,而為sql新增最合理的hint才是關鍵.
5.測試完後,可以通過 exec dbms_sqltune.drop_sql_profile(name =>'coe_cpk9jsg2qt52r_3384190782' );刪除這個sql profile.

6.執行coe_xfr_sql_profile.sql指令碼的時候使用者需要對當前目錄有生成檔案的許可權，最好當前目錄是/tmp

2.2.2.2  SQL Profile使用示例--使用STA來生成SQL Profile

利用STA對語句進行優化後，STA會對語句進行分析，採用最優的優化策略，並給出優化後的查詢計劃。你可以按照STA給出的建議重寫語句。但是，有些情況下，你可能無法重寫語句（比如在生產環境中，你的語句又在一個包中）。這個時候就可以利用sql profile，將優化策略儲存在profile中，Oracle在構建這條語句的查詢計劃時，就不會使用已有相關統計資料，而使用profile的策略，生成新的查詢計劃。

[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SYS@dlhr>

SYS@dlhr>

SYS@dlhr>

SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;

Grant succeeded.

SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR;

Grant succeeded.

SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR;

Grant succeeded.

SYS@dlhr> conn lhr/lhr

Connected.

LHR@dlhr>

LHR@dlhr> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects;

Table created.

LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);  

Index created.

LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);  

PL/SQL procedure successfully completed.

LHR@dlhr> set autot on

LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;

  COUNT(*)

----------

         1

Execution Plan

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

Plan hash value: 3612989399

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

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

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

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OBJECT_ID"=100)

Statistics

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

          1  recursive calls

          0  db block gets

       1249  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LHR@dlhr> set autot off

LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ;

SQL_ID

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

SQL_TEXT

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

7jt1btjkcczb8

select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100

7suktf0w95cry

EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L

HR_20160525_01 where object_id = 100

LHR@dlhr> DECLARE

  2      my_task_name VARCHAR2(30);

  3      my_sqltext       CLOB;

  4  BEGIN

  5      my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100';

  6      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

  7                              sql_text          => my_sqltext,

  8                              user_name       => 'LHR',

  9                              scope           => 'COMPREHENSIVE',

10                              time_limit    => 60,

11                              task_name       => 'sql_profile_test',

12                              description => 'Task to tune a query on a specified table');

13      DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');

14  END;

15  /

PL/SQL procedure successfully completed.

或者也可以使用sqlid來生成優化任務，如下：

LHR@dlhr> DECLARE

  2    a_tuning_task VARCHAR2(30);

  3  BEGIN

  4    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => '7jt1btjkcczb8',

  5                                                     task_name => 'sql_profile_test_SQLID');

  6    dbms_sqltune.execute_tuning_task(a_tuning_task);

  7  END;

  8  /

PL/SQL procedure successfully completed.

LHR@dlhr> set autot off

LHR@dlhr> set long 10000

LHR@dlhr> set longchunksize 1000

LHR@dlhr> set linesize 100

LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : sql_profile_test

Tuning Task Owner  : LHR

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 05/25/2016 16:58:31

Completed at       : 05/25/2016 16:58:32

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

Schema Name: LHR

SQL ID     : 9kzm8scz6t92z

SQL Text   : select /*+no_index(TB_LHR_20160525_01

             TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01

             where object_id = 100

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.83%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',

            task_owner => 'LHR', replace => TRUE);

  Validation results

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

  The SQL profile was tested by executing both its plan and the original plan

  and measuring their respective execution statistics. A plan may have been

  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved

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

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .006278            .00004      99.36 %

  CPU Time (s):                 .003397           .000021      99.38 %

  User I/O Time (s):                  0                 0

  Buffer Gets:                     1249                 2      99.83 %

  Physical Read Requests:             0                 0

  Physical Write Requests:            0                 0

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

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

  Physical Read Bytes:                0                 0

  Physical Write Bytes:               0                 0

  Rows Processed:                     1                 1

  Fetches:                            1                 1

  Executions:                         1                 1

  Notes

  -----

  1. Statistics for the original plan were averaged over 10 executions.

  2. Statistics for the SQL profile plan were averaged over 10 executions.

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

EXPLAIN PLANS SECTION

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

1- Original With Adjusted Cost

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

Plan hash value: 3612989399

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

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

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

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OBJECT_ID"=100)

2- Using SQL Profile

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

Plan hash value: 661515879

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

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

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

|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')

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

   2 - access("OBJECT_ID"=100)

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

這裡可以看到，在優化建議中給出了新的查詢計劃。現在，我們決定接受這個建議，並且不重寫語句。

LHR@dlhr> set autot on

LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;

  COUNT(*)

----------

         1

Execution Plan

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

Plan hash value: 3612989399

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

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

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

|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |

|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |

|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |

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

Predicate Information (identified by operation id):

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

   2 - filter("OBJECT_ID"=100)

Statistics

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

          0  recursive calls

          0  db block gets

       1249  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);

PL/SQL procedure successfully completed.

LHR@dlhr> set autot off

LHR@dlhr>  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints

  2     FROM dba_sql_profiles d,

  3           dba_advisor_tasks e,

  4          SYS.SQLOBJ$DATA A,

  5          SYS.SQLOBJ$ B,

  6          TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),

  7                                    '/outline_data/hint'))) h

  8    where a.signature = b.signature

  9      and a.category = b.category

10      and a.obj_type = b.obj_type

11      and a.plan_id = b.plan_id

12      and a.signature = d.signature

13      and d.task_id=e.task_id

14      and d.name = 'SYS_SQLPROF_0154e728ad3f0000'

15     ;

TASK_NAME                      NAME

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

SQL_TEXT

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

HINTS

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

sql_profile_test               SYS_SQLPROF_0154e728ad3f0000

select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01

where object_id = 100

OPTIMIZER_FEATURES_ENABLE(default)

sql_profile_test               SYS_SQLPROF_0154e728ad3f0000

select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01

where object_id = 100

IGNORE_OPTIM_EMBEDDED_HINTS

在這裡用了包DBMS_SQLTUNE的另一個函式：ACCEPT_SQL_PROFILE。其中，引數task_name即我們建立的優化建議任務的名稱，name是profile的名字，可以是任意合法名稱。此外這個函式還有其他一些函式，下面是這個函式的原型：

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

   task_name    IN  VARCHAR2,

   object_id    IN  NUMBER   := NULL,

   name         IN  VARCHAR2 := NULL,

   description  IN  VARCHAR2 := NULL,

   category     IN  VARCHAR2 := NULL;

   task_owner   IN VARCHAR2  := NULL,

   replace      IN BOOLEAN   := FALSE,

   force_match  IN BOOLEAN   := FALSE)

RETURN VARCHAR2;

Description是profile的描述資訊；task_owner是優化建議任務的所有者；replace為TRUE時，如果這個profile已經存在，就代替它；force_match為TURE時，表示與語句強制匹配，即強制使用繫結變數，和系統引數cursor_sharing設定為FORCE時類似，為FALSE時，與cursor_sharing設定為EXACT時類似，即完全匹配。

這裡要特別提到的是category這個引數，你可以通過設定這個引數，制定特定會話使用這個profile。在10g中，每個會話都有一個新引數SQLTUNE_CATEGORY，他的預設值是DEFAULT。而我們在呼叫這個函式時，如果沒有指定這個引數，那它的值也是DEFAULT，而如果我們給這個profile指定了一個其它的CATEGORY值，如FOR_TUNING，那麼只有會話參SQLTUNE_CATEGORY也為FOR_TUNING時，才會使用這個porfile。為什麼說這個引數很有用呢？試想一個這樣的環境：你在一個生產系統上利用STA調優一條語句，STA已經給出了優化建議，但是你又不敢貿然實施它給出的建議（畢竟它只是機器嘛，不能完全信任），你就可以建立一個有特殊CATEGORY的profile，然後在你自己的會話中制定SQLTUNE_CATEGORY為這個特殊的CATEGORY，那就既可以看優化建議的實際效果又不影響生產環境。

 此外可以通過檢視DBA_SQL_PROFILES來檢視已經建立的profile。

LHR@dlhr> set autot on

LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;

  COUNT(*)

----------

         1

Execution Plan

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

Plan hash value: 661515879

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

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

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

|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=100)

Note

-----

   - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement

Statistics

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

          1  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        526  bytes sent via SQL*Net to client

        520  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

從NOTE部分可以看到，語句採用了profile中的資料，建立了新的查詢計劃。並且在查詢計劃中還有一些附加資訊，表明這個語句是採用了’SYS_SQLPROF_0154e728ad3f0000’這個profile，而不是根據物件上面的統計資料來生成的查詢計劃。

但上述方法主要是依賴sql tuning advisor,如果它無法生成你想要的執行計劃.你還可以通過手動的方式,通過sql profile把hint加進去.複雜的SQL的hint可以採用指令碼coe_xfr_sql_profile.sql來產生原語句的outline data和加hint語句的outline data,然後替換對應的SYS.SQLPROF_ATTR,最後執行生成的sql就可以了.

使用PLSQL DEVELOPER 11檢視執行計劃，如下圖，新版本的好處：

2.3  SPM(SQL Plan Management)

2.3.1  SPM基礎知識

SQL 語句的SQL 執行計劃發生更改時，可能存在效能風險。

SQL 計劃發生更改的原因有很多，如優化程式版本、優化程式統計資訊、優化程式引數、方案定義、系統設計和SQL 概要檔案建立等。

在以前版本的Oracle DB 中引入了各種計劃控制技術（如儲存的大綱(storedoutline(9i))和SQL 概要檔案等(SQLprofile(10g))），用於解決計劃更改導致的效能迴歸。但是，這些技術都是需要手動干預的被動式程式。

SQL 計劃管理是一種隨Oracle Database 11g 引入的新功能，通過維護所謂的“SQL 計劃基線(SQL plan baseline(11g))”來使系統能夠自動控制SQL 計劃演變。啟用此功能後，只要證明新生成的SQL 計劃與SQL 計劃基線相整合不會導致效能迴歸，就可以進行此項整合。因此，在執行某個SQL 語句時，只能使用對應的SQL 計劃基線中包括的計劃。可以使用SQL 優化集自動載入或植入SQL 計劃基線。

SQL 計劃管理功能的主要優點是系統效能穩定，不會出現計劃迴歸。此外，該功能還可以節省DBA 的許多時間，這些時間通常花費在確定和分析SQL 效能迴歸以及尋找可用的解決方案上。Oracle11g中，Oracle提供dbms_spm包來管理SQL Plan，SPM是一個預防機制，它記錄並評估sql的執行計劃，將已知的高效的sql執行計劃建立為SQL Plan Baselines，SQL Plan Baseline的功能是保持SQL的效能而不必關注系統的改變。

在SQL Plan BaseLines捕獲階段，Oracle記錄SQL的執行計劃並檢測該執行計劃是否已經改變，如果SQL改變後的執行計劃是安全的，則SQL就使用新的執行計劃，因此，Oracle維護單個SQL執行計劃的歷史資訊，Oracle維護的SQL執行計劃的歷史僅僅針對重複執行的SQL，SQL Plan Baseline可以手工load，也可以設定為自動捕獲。

載入SQL 計劃基線的方式有兩種:

（1）  即時捕獲,自動捕獲(Automatic Plan Capture)：

使用自動計劃捕獲，方法是：將初始化引數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 設定為TRUE。預設情況下，該引數設定為FALSE。將該引數設定為TRUE 將開啟自動標識可重複SQL 語句，以及自動為此類語句建立計劃歷史記錄的功能。 如果要啟用自動的SQL Plan Capture，則需要設定OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES，該引數預設為False，如果設定為True，則表示自動捕獲SQL Plan，則系統會自動建立並維護SQL Plan History，SQL Plan History包括優化器關注的：比如an execution plan, SQL text, outline, bind variables, and compilation environment。

（2）  成批載入（Manual Plan Loading）：

使用DBMS_SPM 程式包；該程式包支援手動管理SQL 計劃基線。使用此程式包，可以將SQL 計劃從遊標快取記憶體或現有的SQL 優化集(STS) 直接載入到SQL計劃基線中。對於要從STS 載入到SQL 計劃基線的SQL 語句，需要將其SQL計劃儲存在STS中。使用DBMS_SPM 可以將基線計劃的狀態從已接受更改為未接受（以及從未接受更改為已接受），還可以從登臺表匯出基線計劃，然後使用匯出的基線計劃將SQL 計劃基線載入到其它資料庫中。

也可以手動裝載一個存在的SQL Plan作為SQL Plan Baseline，手動裝載的SQL Plan並不校驗它的效能：

--從SQL Tuning Set中裝載:

DECLARE

  my_plans pls_integer;

BEGIN

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'tset1');