【DB笔试面试608】在Oracle中，如何使用STA来生成SQL Profile？

本文主要是介绍【DB笔试面试608】在Oracle中，如何使用STA来生成SQL Profile？，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

640?wx_fmt=gif

♣题目部分

在Oracle中，如何使用STA来生成SQL Profile？

♣答案部分

利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。可以按照STA给出的建议重写语句。但是，有些情况下，你可能无法重写语句（比如在生产环境中，SQL语句又在一个包中）。这个时候就可以利用Sql Profile，将优化策略存储在Profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用Profile的策略，生成新的查询计划。

第一步：给用户赋权限

 1[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba  2  3SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016  4  5Copyright (c) 1982, 2013, Oracle.  All rights reserved.  6  7  8Connected to:  9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 10With the Partitioning, Real Application Clusters, OLAP, Data Mining 11and Real Application Testing options 12 13SYS@dlhr>  14SYS@dlhr>  15SYS@dlhr>  16SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR; 17 18Grant succeeded. 19 20SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR; 21 22Grant succeeded. 23 24SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR; 25 26Grant succeeded. 27 28SYS@dlhr> conn lhr/lhr 29Connected. 30LHR@dlhr>  31LHR@dlhr> select * from v$version; 32 33BANNER 34-------------------------------------------------------------------------------- 35Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 36PL/SQL Release 11.2.0.4.0 - Production 37CORE    11.2.0.4.0      Production 38TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production 39NLSRTL Version 11.2.0.4.0 - Production 40 41LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects;  42 43Table created. 44 45LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);    46 47Index created. 48 49LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);    50 51PL/SQL procedure successfully completed. 52 53LHR@dlhr> set autot on 54LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 55 56  COUNT(*) 57---------- 58         1 59 60 61Execution Plan 62---------------------------------------------------------- 63Plan hash value: 3612989399 64 65----------------------------------------------------------------------------------------- 66| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     | 67----------------------------------------------------------------------------------------- 68|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 | 69|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          | 70|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 | 71----------------------------------------------------------------------------------------- 72 73Predicate Information (identified by operation id): 74--------------------------------------------------- 75 76   2 - filter("OBJECT_ID"=100) 77 78 79Statistics 80---------------------------------------------------------- 81          1  recursive calls 82          0  db block gets 83       1249  consistent gets 84          0  physical reads 85          0  redo size 86        526  bytes sent via SQL*Net to client 87        520  bytes received via SQL*Net from client 88          2  SQL*Net roundtrips to/from client 89          0  sorts (memory) 90          0  sorts (disk) 91          1  rows processed 92LHR@dlhr> set autot off 93LHR@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%' ; 94 95SQL_ID 96------------- 97SQL_TEXT 98------------------------------------------------------------------------------------------------------------------------------------ 997jt1btjkcczb8100select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 1001011027suktf0w95cry103EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L104HR_20160525_01 where object_id = 100

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

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

  8Connected to:
  9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 10With the Partitioning, Real Application Clusters, OLAP, Data Mining
 11and Real Application Testing options

 13SYS@dlhr> 
 14SYS@dlhr> 
 15SYS@dlhr> 
 16SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;

 18Grant succeeded.

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

 22Grant succeeded.

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

 26Grant succeeded.

 28SYS@dlhr> conn lhr/lhr
 29Connected.
 30LHR@dlhr> 
 31LHR@dlhr> select * from v$version;

 33BANNER
 34--------------------------------------------------------------------------------
 35Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 36PL/SQL Release 11.2.0.4.0 - Production
 37CORE    11.2.0.4.0      Production
 38TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
 39NLSRTL Version 11.2.0.4.0 - Production

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

 43Table created.

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

 47Index created.

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

 51PL/SQL procedure successfully completed.

 53LHR@dlhr> set autot on
 54LHR@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(*)
 57----------


 60
 61Execution Plan
 62----------------------------------------------------------
 63Plan hash value: 3612989399

 65-----------------------------------------------------------------------------------------
 66| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
 67-----------------------------------------------------------------------------------------
 68|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |
 69|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |
 70|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |
 71-----------------------------------------------------------------------------------------

 73Predicate Information (identified by operation id):
 74---------------------------------------------------
  2 - filter("OBJECT_ID"=100)

 79Statistics
 80----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed
 92LHR@dlhr> set autot off
 93LHR@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%' ;

 95SQL_ID
 96-------------
 97SQL_TEXT
 98------------------------------------------------------------------------------------------------------------------------------------
 997jt1btjkcczb8
100select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100

1027suktf0w95cry
103EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L
104HR_20160525_01 where object_id = 100

第二步：创建、执行优化任务

1LHR@dlhr> DECLARE 2  2      my_task_name VARCHAR2(30); 3  3      my_sqltext       CLOB; 4  4  BEGIN 5  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  6      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7  7                              sql_text          => my_sqltext, 8  8                              user_name       => 'LHR', 9  9                              scope           => 'COMPREHENSIVE',10 10                              time_limit    => 60,11 11                              task_name       => 'sql_profile_test',12 12                              description => 'Task to tune a query on a specified table');13 13      DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');14 14  END;15 15  /1617PL/SQL procedure successfully completed.DECLARE
     my_task_name VARCHAR2(30);
     my_sqltext       CLOB;
 BEGIN
     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';
     my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                             sql_text          => my_sqltext,
                             user_name       => 'LHR',
                             scope           => 'COMPREHENSIVE',
                             time_limit    => 60,
                             task_name       => 'sql_profile_test',
                             description => 'Task to tune a query on a specified table');
     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');
 END;
 /

17PL/SQL procedure successfully completed.

或者也可以使用sqlid来生成优化任务，如下：

1LHR@dlhr> DECLARE 2  2    a_tuning_task VARCHAR2(30); 3  3  BEGIN 4  4    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => '7jt1btjkcczb8', 5  5                                                     task_name => 'sql_profile_test_SQLID'); 6  6    dbms_sqltune.execute_tuning_task(a_tuning_task); 7  7  END; 8  8  / 910PL/SQL procedure successfully completed.DECLARE
   a_tuning_task VARCHAR2(30);
 BEGIN
   a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => '7jt1btjkcczb8',
                                                    task_name => 'sql_profile_test_SQLID');
   dbms_sqltune.execute_tuning_task(a_tuning_task);
 END;
 /

10PL/SQL procedure successfully completed.

第三步：查看优化建议

 1LHR@dlhr> set autot off  2LHR@dlhr> set long 10000  3LHR@dlhr> set longchunksize 1000  4LHR@dlhr> set linesize 100  5LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;  6  7DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')  8----------------------------------------------------------------------------------------------------  9GENERAL INFORMATION SECTION 10------------------------------------------------------------------------------- 11Tuning Task Name   : sql_profile_test 12Tuning Task Owner  : LHR 13Workload Type      : Single SQL Statement 14Scope              : COMPREHENSIVE 15Time Limit(seconds): 60 16Completion Status  : COMPLETED 17Started at         : 05/25/2016 16:58:31 18Completed at       : 05/25/2016 16:58:32 19 20------------------------------------------------------------------------------- 21Schema Name: LHR 22SQL ID     : 9kzm8scz6t92z 23SQL Text   : select /*+no_index(TB_LHR_20160525_01 24             TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 25             where object_id = 100 26 27------------------------------------------------------------------------------- 28FINDINGS SECTION (1 finding) 29------------------------------------------------------------------------------- 30 311- SQL Profile Finding (see explain plans section below) 32-------------------------------------------------------- 33  A potentially better execution plan was found for this statement. 34 35  Recommendation (estimated benefit: 99.83%) 36  ------------------------------------------ 37  - Consider accepting the recommended SQL profile. 38    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test', 39            task_owner => 'LHR', replace => TRUE); 40 41  Validation results 42  ------------------ 43  The SQL profile was tested by executing both its plan and the original plan 44  and measuring their respective execution statistics. A plan may have been 45  only partially executed if the other could be run to completion in less time. 46 47                           Original Plan  With SQL Profile  % Improved 48                           -------------  ----------------  ---------- 49  Completion Status:            COMPLETE          COMPLETE 50  Elapsed Time (s):             .006278            .00004      99.36 % 51  CPU Time (s):                 .003397           .000021      99.38 % 52  User I/O Time (s):                  0                 0 53  Buffer Gets:                     1249                 2      99.83 % 54  Physical Read Requests:             0                 0 55  Physical Write Requests:            0                 0 56 57DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST') 58---------------------------------------------------------------------------------------------------- 59  Physical Read Bytes:                0                 0 60  Physical Write Bytes:               0                 0 61  Rows Processed:                     1                 1 62  Fetches:                            1                 1 63  Executions:                         1                 1 64 65  Notes 66  ----- 67  1. Statistics for the original plan were averaged over 10 executions. 68  2. Statistics for the SQL profile plan were averaged over 10 executions. 69 70------------------------------------------------------------------------------- 71EXPLAIN PLANS SECTION 72------------------------------------------------------------------------------- 73 741- Original With Adjusted Cost 75------------------------------ 76Plan hash value: 3612989399 77 78----------------------------------------------------------------------------------------- 79| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     | 80----------------------------------------------------------------------------------------- 81|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 | 82|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          | 83|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 | 84----------------------------------------------------------------------------------------- 85 86Predicate Information (identified by operation id): 87--------------------------------------------------- 88 89   2 - filter("OBJECT_ID"=100) 90 912- Using SQL Profile 92-------------------- 93Plan hash value: 661515879 94 95-------------------------------------------------------------------------------------------- 96| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | 97-------------------------------------------------------------------------------------------- 98|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 | 99|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |100|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |101--------------------------------------------------------------------------------------------102103Predicate Information (identified by operation id):104---------------------------------------------------105106107DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')108----------------------------------------------------------------------------------------------------109   2 - access("OBJECT_ID"=100)110111-------------------------------------------------------------------------------set autot off
  2LHR@dlhr> set long 10000
  3LHR@dlhr> set longchunksize 1000
  4LHR@dlhr> set linesize 100
  5LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;

  7DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
  8----------------------------------------------------------------------------------------------------
  9GENERAL INFORMATION SECTION
 10-------------------------------------------------------------------------------
 11Tuning Task Name   : sql_profile_test
 12Tuning Task Owner  : LHR
 13Workload Type      : Single SQL Statement
 14Scope              : COMPREHENSIVE
 15Time Limit(seconds): 60
 16Completion Status  : COMPLETED
 17Started at         : 05/25/2016 16:58:31
 18Completed at       : 05/25/2016 16:58:32

 20-------------------------------------------------------------------------------
 21Schema Name: LHR
 22SQL ID     : 9kzm8scz6t92z
 23SQL Text   : select /*+no_index(TB_LHR_20160525_01
            TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
            where object_id = 100

 27-------------------------------------------------------------------------------
 28FINDINGS SECTION (1 finding)
 29-------------------------------------------------------------------------------

 311- SQL Profile Finding (see explain plans section below)
 32--------------------------------------------------------
 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

 57DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
 58----------------------------------------------------------------------------------------------------
 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.

 70-------------------------------------------------------------------------------
 71EXPLAIN PLANS SECTION
 72-------------------------------------------------------------------------------

 741- Original With Adjusted Cost
 75------------------------------
 76Plan hash value: 3612989399

 78-----------------------------------------------------------------------------------------
 79| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
 80-----------------------------------------------------------------------------------------
 81|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |
 82|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |
 83|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |
 84-----------------------------------------------------------------------------------------

 86Predicate Information (identified by operation id):
 87---------------------------------------------------
  2 - filter("OBJECT_ID"=100)

 912- Using SQL Profile
 92--------------------
 93Plan hash value: 661515879

 95--------------------------------------------------------------------------------------------
 96| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 97--------------------------------------------------------------------------------------------
 98|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |
 99|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |
100|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |
101--------------------------------------------------------------------------------------------

103Predicate Information (identified by operation id):
104---------------------------------------------------

107DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
108----------------------------------------------------------------------------------------------------
- access("OBJECT_ID"=100)

111-------------------------------------------------------------------------------

这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。

第四步：接受profile

1LHR@dlhr> set autot on 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 3 4  COUNT(*) 5---------- 6         1 7 8 9Execution Plan10----------------------------------------------------------1112Plan hash value: 36129893991314-----------------------------------------------------------------------------------------15| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |16-----------------------------------------------------------------------------------------17|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |18|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |19|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |20-----------------------------------------------------------------------------------------2122Predicate Information (identified by operation id):23---------------------------------------------------2425   2 - filter("OBJECT_ID"=100)262728Statistics29----------------------------------------------------------30          0  recursive calls31          0  db block gets32       1249  consistent gets33          0  physical reads34          0  redo size35        526  bytes sent via SQL*Net to client36        520  bytes received via SQL*Net from client37          2  SQL*Net roundtrips to/from client38          0  sorts (memory)39          0  sorts (disk)40          1  rows processed41LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);4243PL/SQL procedure successfully completed.4445LHR@dlhr> set autot off46LHR@dlhr>  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints47  2     FROM dba_sql_profiles d,48  3           dba_advisor_tasks e,49  4          SYS.SQLOBJ$DATA A,50  5          SYS.SQLOBJ$ B,51  6          TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),52  7                                    '/outline_data/hint'))) h53  8    where a.signature = b.signature54  9      and a.category = b.category55 10      and a.obj_type = b.obj_type56 11      and a.plan_id = b.plan_id57 12      and a.signature = d.signature 58 13      and d.task_id=e.task_id59 14      and d.name = 'SYS_SQLPROF_0154e728ad3f0000'60 15     ;6162TASK_NAME                      NAME63------------------------------ ------------------------------64SQL_TEXT65----------------------------------------------------------------------------------------------------66HINTS67----------------------------------------------------------------------------------------------------68sql_profile_test               SYS_SQLPROF_0154e728ad3f000069select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_0170where object_id = 10071OPTIMIZER_FEATURES_ENABLE(default)7273sql_profile_test               SYS_SQLPROF_0154e728ad3f000074select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_0175where object_id = 10076IGNORE_OPTIM_EMBEDDED_HINTSset autot on
 2LHR@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(*)
 5----------


 8
 9Execution Plan
10----------------------------------------------------------

12Plan hash value: 3612989399

14-----------------------------------------------------------------------------------------
15| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
16-----------------------------------------------------------------------------------------
17|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |
18|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |
19|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |
20-----------------------------------------------------------------------------------------

22Predicate Information (identified by operation id):
23---------------------------------------------------
  2 - filter("OBJECT_ID"=100)

28Statistics
29----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed
41LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);

43PL/SQL procedure successfully completed.

45LHR@dlhr> set autot off
46LHR@dlhr>  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints
    FROM dba_sql_profiles d,
          dba_advisor_tasks e,
         SYS.SQLOBJ$DATA A,
         SYS.SQLOBJ$ B,
         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
                                   '/outline_data/hint'))) h
   where a.signature = b.signature
     and a.category = b.category
     and a.obj_type = b.obj_type
     and a.plan_id = b.plan_id
     and a.signature = d.signature 
     and d.task_id=e.task_id
     and d.name = 'SYS_SQLPROF_0154e728ad3f0000'
    ;

62TASK_NAME                      NAME
63------------------------------ ------------------------------
64SQL_TEXT
65----------------------------------------------------------------------------------------------------
66HINTS
67----------------------------------------------------------------------------------------------------
68sql_profile_test               SYS_SQLPROF_0154e728ad3f0000
69select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
70where object_id = 100
71OPTIMIZER_FEATURES_ENABLE(default)

73sql_profile_test               SYS_SQLPROF_0154e728ad3f0000
74select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
75where object_id = 100
76IGNORE_OPTIM_EMBEDDED_HINTS

在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名称。此外这个函数还有其他一些函数，下面是这个函数的原型：

1DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 2   task_name    IN  VARCHAR2, 3   object_id    IN  NUMBER   := NULL, 4   name         IN  VARCHAR2 := NULL, 5   description  IN  VARCHAR2 := NULL, 6   category     IN  VARCHAR2 := NULL; 7   task_owner   IN VARCHAR2  := NULL, 8   replace      IN BOOLEAN   := FALSE, 9   force_match  IN BOOLEAN   := FALSE)10 RETURN VARCHAR2;
  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。

第五步：查看profile的效果

1LHR@dlhr> set autot on 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ; 3 4  COUNT(*) 5---------- 6         1 7 8 9Execution Plan10----------------------------------------------------------11Plan hash value: 6615158791213--------------------------------------------------------------------------------------------14| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |15--------------------------------------------------------------------------------------------16|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |17|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |18|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |19--------------------------------------------------------------------------------------------2021Predicate Information (identified by operation id):22---------------------------------------------------2324   2 - access("OBJECT_ID"=100)2526Note27-----28   - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement293031Statistics32----------------------------------------------------------33          1  recursive calls34          0  db block gets35          2  consistent gets36          0  physical reads37          0  redo size38        526  bytes sent via SQL*Net to client39        520  bytes received via SQL*Net from client40          2  SQL*Net roundtrips to/from client41          0  sorts (memory)42          0  sorts (disk)43          1  rows processedset autot on
 2LHR@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(*)
 5----------


 8
 9Execution Plan
10----------------------------------------------------------
11Plan hash value: 661515879

13--------------------------------------------------------------------------------------------
14| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
15--------------------------------------------------------------------------------------------
16|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |
17|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |
18|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |
19--------------------------------------------------------------------------------------------

21Predicate Information (identified by operation id):
22---------------------------------------------------
  2 - access("OBJECT_ID"=100)

26Note
27-----
  - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement

31Statistics
32----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 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查看执行计划，如下图，新版本的好处：

640?wx_fmt=png