oracle固定执行计划1-sql profile

最新推荐文章于 2024-07-12 16:19:57 发布

P10ZHUO

最新推荐文章于 2024-07-12 16:19:57 发布

阅读量1.2k

点赞数 3

分类专栏： sql优化文章标签： oracle

本文链接：https://blog.csdn.net/fanzhuozhuo/article/details/114266331

版权

sql优化专栏收录该内容

4 篇文章 1 订阅

订阅专栏

oracle固定执行计划1-sql profile

为什么要固定执行计划
SQL Profile
- automatic类型的sql profile
总结：

为什么要固定执行计划

我的数据库怎么了？

• 系统刚上线，一切很美好
• 一两年后，负荷逐渐增加
– 数据量的积累
– 用户数增多
– 功能点使用增多
• 频繁出现性能问题

优化SQL是最好的解决上述问题的方法，但是：
1、使用封装好的商业软件
1)没发修改SQL
2、外包开发
1)验收周期已经结束
2)开发商已经离场
3、自行开发
1)开发人员不愿意修改代码
2)没人承担变更风险
担子全部抛给运维组或者dba组。

不修改SQL，如何优化？

1）硬件优化
• 硬件层面
– CPU
RAC
– 内存
增加cache
– 存储
zData方案

2）数据库
– 参数调整：
内存参数，优化器参数 …
– 表结构调整：
索引, 并行度,分区
– SQL执行计划调整：
SQL Profile, SPM
– 其它：
Cache 表
统计信息
查询重写
数据归档

• 数据库内存参数
– SGA
• Buffer Cache
• Shared pool
– PGA
• 谨慎使用自动SGA调整
– 在parse call 很高的环境下，shared pool 抖
动可能发生灾难性性能
– Latch: library cache
– cursor: pin S

• 优化器参数
– Optimizer_mode
CBO/RBO
统计信息为CBO服务
– Optimizer_features_enabled
新版本优化特性总开关，升级中常用
设置该参数必须经过严格测试
– 其它小的优化参数：
运行 10046 trace
Alter session set events ‘10046 trace name context forever, level 1’;
查看trace，找到“PARAMETERS USED BY THE OPTIMIZER”

• 索引
– 适当的索引对提高系统性能有非常大帮助
– 适当建立函数索引
使用SQL Profiles无非是两个目的：
锁定或者说是稳定执行计划。
在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。
在这里插入图片描述
我们都希望对于所有在oracle数据库中执行的SQL，CBO都能产生出正确的执行计划，但实际情况却并非如此，由于各种各样的原因，比如目标SQL所涉及的对象的统计信息的不准确，或者CBO内部一些成本计算公式先天的缺陷等。导致有时CBO会产生效率不高、甚至是错误的执行计划。特别是CBO对目标SQL所产生的初始执行计划是正确的，后来由于某种原因（比如统计信息的变更等）而导致CBO重新对其产生了错误的执行计划，这种执行计划的改变往往会导致目标SQL执行时间呈数量级的递增，而且常常会让我们很困惑：这个SQL原先跑的好好的，为什么突然就慢的让人无法接收？其实这种SQL执行效率突然的衰减往往是因为目标SQL执行计划的改变。
我们当然希望这样的改变永远都不要发生，即在oracle数据库中跑的所有SQL都能有正确的、稳定的执行计划，但实际上在oracle 11g SPM出现之前，这一点是很难做到的。那么现在退而求其次，如果已经出现了执行计划的变更，即CBO已经长生了错误的执行计划，我们应该怎样纠正那？
这种情况下，我们通常会重新收集一下统计信息或者修改目标SQL（比如在目标SQL中加入Hint等）以纠正错误的执行计划。但是有时候重新收集统计信息并不能解决问题，更糟糕的是，很多情况下是没有办法修改目标SQL的SQL文本的（比如系统是第三方开发的，修改不了源码，或者目标SQL是前台框架动态生成的等等），那么这种情况下我们该怎么办哪?
在oracle 10g/11g及其以后的版本中，我们可以使用SQL profile或SPM来解决上述执行计划变更的问题，用他们来调整、稳定目标SQL的执行计划。
还有一些情况，SQL写的并没有问题，但由于种种原因，数据库经常走错执行计划；这时候，通过改写SQL, 增加Hint是一种常见解决方式。在无法修改SQL的情况下，也可以通过一些手段对SQL执行计划进行固定。
本文先着重介绍通过一些手段对SQL执行计划进行固定
这些手段主要包括有：
SQL Profile(Oracle 10g以后)
SQL Plan Baseline Management(Oracle 11g以后)
SQL Patch
Outline

SQL profile在Oracle 10g引入
通过为特定的SQL文本指定优化器的一些信息，从而引导优化器生成更为合理的SQL执行计划。达到不修改SQL文本就可以改变并指定执行计划的目的
SQL-Profile 主要通过dbms_sqltune包进行控制；
SQL Plan Management在Oracle 11g引入，通过为特定的SQL指定已知SQL执行计划，强制优化器选择已经指定的SQL执行计划，从而达到不修改SQL文本即可修改执行计划的目的，可以指定多个可用执行计划供优化器选择，可以和SQL Tuning Advisor一起用，可以自动收集运行库中SQL 作为已知执行计划，也可以手工设置，SQL Plan Management主要通过DBMS_SPM包进行控制；
sys.dbms_sqldiag_internal.i_create_patch
SQL Patch是一种强行给SQL加Hint的方法，主要通过sys.dbms_sqldiag_internal.i_create_patch 进行。
原因：
1）sql突然执行就慢了
2）经常走错执行计划
3) 想要优化sql，但无法修改sql文本

SQL Profile

1、SQL profile在Oracle 10g引入
2、通过为特定的SQL文本指定优化器的一些信息，从而引导优化器生成更为合理的SQL执行计划。达到不修改SQL文本就可以改变并指定执行计划的目的。
3、通常和SQL Tuning Advisor一起用
4、也可以手工设置
使用SQL Profiles无非是两个目的：
1、锁定或者说是稳定执行计划。
2、在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。
那么SQL Profile到底是什么？在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划。下面进行验证。

automatic类型的sql profile

构造测试环境：

session 1：

conn scott/tiger;
create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
create table t2 as select * from dba_objects;
create index t2_idx on t2(object_id);
begin
  dbms_stats.gather_table_stats(ownname=>'SCOTT',
                                tabname=>'T1',
                                no_invalidate=>false,
                                degree=>8,
                                cascade=>true);
end;
/

begin
  dbms_stats.gather_table_stats(ownname=>'SCOTT',
                                tabname=>'T2',
                                no_invalidate=>false,
                                degree=>8,
                                cascade=>true);
end;
/

分析执行计划
session 1：

SQL> set autot trace
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   391   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   391   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    70   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 80073 |   860K|   321   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1396  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

这里省略了SELECT出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。
首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like '%T1%'这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like '%T1%'返回的结果行数为2500行，即T1表总行数的5%(这是Oracle在类似于like ‘%xxx’这样的谓词的默认选择率)，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本：

SQL> select /*+ use_nl(t1 t2) index(t2)*/ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id; 

36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|   1 |  NESTED LOOPS                |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5071   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        306  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

从执行计划可以看到Oracle优化器评估的成本为5071，远远高于原来的391。
但是实际的逻辑读是多少呢？加了HINT之后实际的逻辑读只有306，低于原始SQL的1396。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。

此时没有真正的执行，只是看了执行计划，虽然是真实的执行计划，但是并没有执行，可以拿v$sql来验证。

session 2：
SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t1%' and sql_text not like '%v$sql%';

no rows selected

执行一次：

session 1：
SQL> set autot off
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

 OBJECT_ID
----------
。。。。

再次查询

session 2：
SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t1%' and sql_text not like '%v$sql%';


SQL_ID        SQL_TEXT                                                     PARSE_CALLS      LOADS EXECUTIONS
------------- ------------------------------------------------------------ ----------- ---------- ----------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%           1          2          1
              T1%' and t1.object_id=t2.object_id

原始SQL现在已经解析，执行完成了1次。
开始auto绑定：

查询sql_id

session 2：
SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t1%' and sql_text not like '%v$sql%';


SQL_ID        SQL_TEXT                                                     PARSE_CALLS      LOADS EXECUTIONS
------------- ------------------------------------------------------------ ----------- ---------- ----------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%           1          2          1
              T1%' and t1.object_id=t2.object_id

执行autotune优化任务

var tuning_task varchar2(100);  
DECLARE  
  l_sql_id v$session.prev_sql_id%TYPE;  
  l_tuning_task VARCHAR2(30);  
BEGIN  
  l_sql_id:='4zbqykx89yc8v';  
  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  :tuning_task:=l_tuning_task;  
  dbms_sqltune.execute_tuning_task(l_tuning_task);  
  dbms_output.put_line(l_tuning_task);  
END;  
/  
set long 10000 --SQL*PLUS中不要忘记执行，否则看不全优化结果（切记）
set long 10000
set longchunksize 1000
set linesize 100
set heading off
print tuning_task;  
SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual;

以下为具体过程：
session 3:

SQL> var tuning_task varchar2(100);  
SQL> DECLARE  
  2    l_sql_id v$session.prev_sql_id%TYPE;  
  3    l_tuning_task VARCHAR2(30);  
  4  BEGIN  
  5    l_sql_id:='4zbqykx89yc8v';  
  6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  7    :tuning_task:=l_tuning_task;  
  8    dbms_sqltune.execute_tuning_task(l_tuning_task);  
  9    dbms_output.put_line(l_tuning_task);  
 10  END;  
 11  /  

PL/SQL procedure successfully completed.

查询其他一些额外信息：

session 2:
SQL>  select rownum, a.*
  2     from (select exact_matching_signature,
  3                  force_matching_signature,
  4                  plan_hash_value,
  5                  module,
  6                  parsing_schema_name,
  7                  sql_text
  8             from v$sql
  9            where sql_text like '%from t1%'
 10              and sql_text not like '%v$sql%'
 11            order by 1) a;


ROWNUM EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE      PLAN_HASH_VALUE MODULE               PARSING_SC SQL_TEXT
------ ------------------------ ------------------------ -------------------- -------------------- ---------- ------------------------------------------------------------
     1      8975541025552400288      3960696072677096522           1838229974 SQL*Plus             SCOTT      select t1.*,t2.owner from t1,t2 where t1.object_name like '%
                                                                                                              T1%' and t1.object_id=t2.object_id

     2      8975541025552400288      3960696072677096522           1022743391 sqlplus@oracle11g (T SCOTT      /* SQL Analyze(38,0) */ select t1.*,t2.owner from t1,t2 wher
                                                                              NS V1-V3)                       e t1.object_name like '%T1%' and t1.object_id=t2.object_id

     3      8975541025552400288      3960696072677096522           1838229974 sqlplus@oracle11g (T SCOTT      /* SQL Analyze(38,0) */ select t1.*,t2.owner from t1,t2 wher
                                                                              NS V1-V3)                       e t1.object_name like '%T1%' and t1.object_id=t2.object_id


SQL> SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t1%' and sql_text not like '%v$sql%';

SQL_ID        SQL_TEXT                                                              PARSE_CALLS                LOADS           EXECUTIONS
------------- ------------------------------------------------------------ -------------------- -------------------- --------------------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%                    1                    1                    1
              T1%' and t1.object_id=t2.object_id

amxwktmuy3h0f /* SQL Analyze(38,0) */ select t1.*,t2.owner from t1,t2 wher                    1                   33                   10
              e t1.object_name like '%T1%' and t1.object_id=t2.object_id

amxwktmuy3h0f /* SQL Analyze(38,0) */ select t1.*,t2.owner from t1,t2 wher                    1                    1                   10
              e t1.object_name like '%T1%' and t1.object_id=t2.object_id

/* SQL Analyze(38,0) */这种开头的，这是数据库SQL优化的包，自己调用的，是标记这种优化包调用的。
一是我们这种手动调用的dbms_sqltune过程，二是自动维护任务，sql tuning advisor。其本质都是调用dbms_sqltune过程。它们最明显的特征就是前面以/* SQL Analyze(38,0) */ 开头。后面em操作，详见后面。

查询自动优化过执行计划的信息，可以进一步确认这条sql是不是真的被自动调优过。

SQL> select distinct TASK_NAME,SQL_ID,EXECUTION_NAME,PLAN_HASH_VALUE,TIMESTAMP from dba_advisor_sqlplans where sql_id in('4zbqykx89yc8v') order by sql_id;

TASK_NAME                      SQL_ID        EXECUTION_NAME                      PLAN_HASH_VALUE TIMESTAMP
------------------------------ ------------- ------------------------------ -------------------- -------------------
TASK_12                        4zbqykx89yc8v EXEC_2                                   1838229974 2021-02-25 16:16:15
TASK_12                        4zbqykx89yc8v EXEC_2                                   1022743391 2021-02-25 16:16:15

查询自动调优后的结果

session 3:
SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> print tuning_task;  

TASK_12


SQL> SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual; 

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_12
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 02/26/2021 11:48:34
Completed at       : 02/26/2021 11:48:35

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 4zbqykx89yc8v
SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
             and t1.object_id=t2.object_id

-------------------------------------------------------------------------------
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: 78.57%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_12',
            task_owner => 'SYS', 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):             .024284           .019931      17.92 %
  CPU Time (s):                 .024087           .019786      17.85 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1393               297      78.67 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                    36                36
  Fetches:                           36                36
  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: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    36 |  1476 |   391   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |    36 |  1476 |   391   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |    36 |  1080 |    70   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 80073 |   860K|   321   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

2- Using SQL Profile
--------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为36，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。

接受这个SQL Profile

当你运行SQL Tuning Advisor后，建议你接受一个SQL Profile，如果你想在接受SQL Profile前知道它到底为你提供了些什么，可以运行以下查询获得：
select
--b.ATTR1        -- 10g 列
b.ATTR5        -- 11g 列
from wri$_adv_tasks a,wri$_adv_rationale b 
where a.name = 'TASK_12' 
and b.task_id = a.id 
order by b.rec_id, b.id;      

ATTR5
--------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.5')
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.5')

6 rows selected.

SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);

PL/SQL procedure successfully completed.

–注意，参数force_match
默认值是false，表示只有在SQL文本完全匹配的情况下才会应用SQL Profile，这种情况下只要目标SQL的SQL文本发生一点变动，原有的SQL profile就将失去作用。
但是没测出效果。待验证。后面有详细过程。

如果一个SQL使用了SQL Profile，那么这个SQL的v$sql的sql_profile字段会显示使用到的SQL Profile的名字。下面的查询显示了系统中存在的SQL Profile和当前共享池中正在使用的SQL Profile的SQL。

SQL> select name, category, status, substr(sql_text,1,25) sql_text, force_matching
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text','%')
  4  and name like nvl('&name',name)
  5  order by last_modified;
Enter value for sql_text: 
old   3: where sql_text like nvl('&sql_text','%')
new   3: where sql_text like nvl('','%')
Enter value for name: 
old   4: and name like nvl('&name',name)
new   4: and name like nvl('',name)

NAME                           CATEGORY                       STATUS   SQL_TEXT                                                     FOR
------------------------------ ------------------------------ -------- ------------------------------------------------------------ ---
SYS_SQLPROF_0178117397260000   DEFAULT                        ENABLED  select t1.*,t2.owner from                                    YES

select sql_id,
       child_number cn,
       plan_hash_value plan_hash,
       sql_profile,
       executions execs,
       buffer_gets / decode(nvl(executions, 0), 0, 1, executions) avg_lio
  from v$sql s
 where upper(sql_text) like upper(nvl('&sql_text', sql_text))
   and sql_text not like '%from v$sql where sql_text like nvl(%'
   and sql_id like nvl('&sql_id', sql_id)
   and sql_profile is not null
 order by 1, 2, 3;
 
Enter value for sql_text: 
old   8:  where upper(sql_text) like upper(nvl('&sql_text', sql_text))
new   8:  where upper(sql_text) like upper(nvl('', sql_text))
Enter value for sql_id: 4zbqykx89yc8v
old  10:    and sql_id like nvl('&sql_id', sql_id)
new  10:    and sql_id like nvl('4zbqykx89yc8v', sql_id)

SQL_ID                CN  PLAN_HASH SQL_PROFILE                                                           EXECS    AVG_LIO
------------- ---------- ---------- ---------------------------------------------------------------- ---------- ----------
4zbqykx89yc8v          0 3787413387 SYS_SQLPROF_0177dc7534bb0000                                              3 307.666667

受SQL Tuning Advisor提供的SQL Profile后，执行计划Note部分：- SQL profile “SYS_SQLPROF_0177dc7534bb0000” used for this statement，显示已经使用到了SQL Profile，注意由SQL Tuning Advisor产生的SQL Profile名称都是SYS_SQLPROF作为前缀

验证效果

 session 1：
SQL> /

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_0177dc7534bb0000" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        306  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

执行计划已经发生改变。
从执行计划的"Note“一节也可以看到，SQL Profile起作用了。SQL Profile的名字为"SYS_SQLPROF_0177dc7534bb0000"。

那么我们再执行其他的类似SQL看看：
因为force_match=>true的作用。

session 1：
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;

57 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_0177dc7534bb0000" used for this statement


Statistics
----------------------------------------------------------
        905  recursive calls
          0  db block gets
       1040  consistent gets
          1  physical reads
          0  redo size
       2910  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         70  sorts (memory)
          0  sorts (disk)
         57  rows processed

这一次，尽管我们更改了LIKE 后面的值，但是执行计划与SQL Tuning Advisor产生的执行计划完全一样。从执行计划的"Note“一节也可以看到，SQL Profile起作用了。两个sql用的都是sqlprofile ：SYS_SQLPROF_0177dc7534bb0000
查看profile的信息：

SQL>  select name,category,signature,type,status,force_matching from dba_sql_profiles; 

NAME                           CATEGORY                                  SIGNATURE TYPE    STATUS   FOR
------------------------------ ------------------------------ -------------------- ------- -------- ---
SYS_SQLPROF_0177dc7534bb0000   DEFAULT                         3960696072677096522 MANUAL  ENABLED  YES

SQL> select * from all_sql_profiles;
select * from all_sql_profiles
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from user_sql_profiles;
select * from user_sql_profiles
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user;
USER is "SYS"
SQL>

一旦接受SQL Profile，就可以通过视图dba_sql_profiles视图来查看SQL Profile的相关信息。因为SQL Profile并不属于某个用户，因此all_sql_profiles和user_sql_profiles视图都不可用。

sql profile的本质

我们来看看，SQL Profiles实际上是些什么：

select * from SQLOBJ$;
select * from SQLOBJ$AUXDATA;
 col OBJ_TYPE for 999
 col SIGNATURE for 999
 col COMP_DATA for a100
 set num 49
 set long 100000
 col CATEGORY for a10
 col PLAN_ID for 9
 col SIGNATURE for 99999999999999999999
 col SPARE1 for a10
 col SPARE2 for a10
select * from SQLOBJ$DATA;

            SIGNATURE CATEGORY   OBJ_TYPE PLAN_ID COMP_DATA                                                                                                SPARE1 SPARE2
--------------------- ---------- -------- ------- ---------------------------------------------------------------------------------------------------- ---------- ----------
  3960696072677096522 DEFAULT           1       0 <outline_data><hint><![CDATA[OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)]]></hint
                                                  ><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint></outline_data>

上述中，其实最重要的就是 COMP_DATA字段里面的 [CDATA[OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)]
可以看到，SQL Profiles实际上就是一些Hints，这跟Outlines没有本质上的区别。只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数.2500*0.0144=36,这里就是告诉Oracle优化器，T1表经过谓语过滤后返回行数应该为评估的0.0144倍。从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器。看下面的测试：

session 1:
SQL> exec dbms_stats.set_table_stats('SCOTT','T1',numrows=>5000000);

PL/SQL procedure successfully completed.
session 2:
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3600 |   144K|   419   (7)| 00:00:06 |
|*  1 |  HASH JOIN         |      |  3600 |   144K|   419   (7)| 00:00:06 |
|*  2 |   TABLE ACCESS FULL| T1   |  3600 |   105K|    99  (30)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   | 80073 |   860K|   321   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

Note
-----
   - SQL profile "SYS_SQLPROF_0177dc7534bb0000" used for this statement


Statistics
----------------------------------------------------------
        242  recursive calls
          0  db block gets
       1567  consistent gets
          1  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         29  sorts (memory)
          0  sorts (disk)
         36  rows processed

将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回5000000*5%*0.0144=3600行。这里执行计划又变回为full scan+hash join。可以看到，
虽然SQL Profile起作用了，但是并没有锁定执行计划。
注意：
即T1表总行数的5%?这个5% 是怎么来的呢?这是Oracle在类似于like ‘%xxx’这样的谓词的默认选择率。

看看通过SQL Tuning Advisor创建的SQL Profile使用到的hint。

col HINT for a100
SELECT extractValue(value(h), '.') AS hint
  FROM sys.sqlobj$data od,
       sys.sqlobj$ so,
       table(xmlsequence(extract(xmltype(od.comp_data),
                                 '/outline_data/hint'))) h
 WHERE so.name = 'SYS_SQLPROF_0177dc7534bb0000'
   AND so.signature = od.signature
   AND so.category = od.category
   AND so.obj_type = od.obj_type
   AND so.plan_id = od.plan_id;
   HINT
----------------------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.1.0.5')

这些hint都不是我们日常所用的hint，大部分是以OPT_ESTIMATE打头的，例如OPT_ESTIMATE(@“SEL$1”, TABLE, “T1”@“SEL$1”, SCALE_ROWS=0.0144)代表的是把表t2经过谓词过滤后返回的基数修正为原始评估的基数乘以0.0144，也就是缩小了x倍：基数从x缩小为x。按照OPT_ESTIMATE提示缩小后的基数非常的准确，由于OPT_ESTIMATE告诉了优化器非常准确的基数信息，因此优化器再次评估执行计划的时候选择了索引扫描。

Note：SQL Profile里可能会包含哪些hint？这里对SQL Profile里一些常出现的hint做出解释。
1) OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)
返回10倍于预估的表的基数
2) OPT_ESTIMATE(@SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1)
返回十分之一的预估的索引的基数
3) OPT_ESTIMATE(@SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1),SCALE_ROWS=4.2)
当test1,test2做join时，返回4.2倍与预估的基数
4) TABLE_STATS(“HR”, “EMPLOYEES”, scale, blocks=10, rows=107)
为表提供统计信息：如行数、块数
5) COLUMN_STATS(“HR”, “EMPLOYEES”, “EMPLOYEE_ID”, scale,length=3 DISTINCT=107 nulls=0 min=100 max=207)
为表上的列提供统计信息：如空值、最大值、最小值等
6) INDEX_STATS(“HR”, “EMPLOYEES”, “EMP_IDX”, scale, blocks=5, rows=107)
为索引提供统计信息：如索引块数、索引条目数
7) ALL_ROWS
设置优化器的模式为ALL_ROWS
8) IGNORE_OPTIM_EMBEDDED_hintS
忽略嵌入在SQL里的hint

验证force_match=>true

修改正确，使profile生效，重新收集统计信息即可：

session2：
begin
  dbms_stats.gather_table_stats(ownname=>'SCOTT',
                                tabname=>'T1',
                                no_invalidate=>false,
                                degree=>8,
                                cascade=>true);
end;
/

session 1：
SQL> /

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_0177dc7534bb0000" used for this statement


Statistics
----------------------------------------------------------
        372  recursive calls
          0  db block gets
        666  consistent gets
          1  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         41  sorts (memory)
          0  sorts (disk)
         36  rows processed

session 2:
SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>false);

PL/SQL procedure successfully completed.

session 1:
SQL> /

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_0177dc7ca26c0001" used for this statement


Statistics
----------------------------------------------------------
         56  recursive calls
          0  db block gets
        362  consistent gets
          1  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         36  rows processed

SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' and t1.object_id=t2.object_id;

57 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_0177dc7534bb0000" used for this statement


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        318  consistent gets
          1  physical reads
          0  redo size
       2910  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         57  rows processed

发现并没有生效，还是对不同SQL文本生效，但是可以发现，2条sql用到的sql profile不一样。
此时会有2个profile：

SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles; 

NAME                           CATEGORY               SIGNATURE TYPE    STATUS   FOR
------------------------------ ---------- --------------------- ------- -------- ---
SYS_SQLPROF_0177dc7534bb0000   DEFAULT      3960696072677096522 MANUAL  ENABLED  YES
SYS_SQLPROF_0177dc7ca26c0001   DEFAULT      8975541025552400288 MANUAL  ENABLED  NO
SQL> select * from SQLOBJ$DATA;

            SIGNATURE CATEGORY   OBJ_TYPE PLAN_ID COMP_DATA                                                                                                SPARE1 SPARE2
--------------------- ---------- -------- ------- ---------------------------------------------------------------------------------------------------- ---------- ----------
  3960696072677096522 DEFAULT           1       0 <outline_data><hint><![CDATA[OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)]]></hint
                                                  ><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint></outline_data>


  8975541025552400288 DEFAULT           1       0 <outline_data><hint><![CDATA[OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)]]></hint
                                                  ><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint></outline_data>

看到状态字段：for一个为yes，一个为no。不同的SQL文本，它可以自动切换。所以上面验证force_match说法不正确。

维护

禁用：

session 3：
begin
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_0177d85381f70000', 
attribute_name => 'status', 
value   => 'disabled'); 
end; 
/

PL/SQL procedure successfully completed.

SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;

NAME                           CATEGORY                        SIGNATURE TYPE    STATUS   FOR
------------------------------ ------------------------------ ---------- ------- -------- ---
SYS_SQLPROF_0177d85381f70000   DEFAULT                        3.9607E+18 MANUAL  DISABLED YES

–status为disable

session 1:
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   391   (1)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   391   (1)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    70   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 80073 |   860K|   321   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)


Statistics
----------------------------------------------------------
         49  recursive calls
          0  db block gets
       1474  consistent gets
          0  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
         36  rows processed

profile已经不起作用了。

启用：

begin
dbms_sqltune.alter_sql_profile( 
name   => 'SYS_SQLPROF_0177d85381f70000', 
attribute_name => 'status', 
value   => 'enabled'); 
end; 
/
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles; 

NAME                           CATEGORY                        SIGNATURE TYPE    STATUS   FOR
------------------------------ ------------------------------ ---------- ------- -------- ---
SYS_SQLPROF_0177d85381f70000   DEFAULT                        3.9607E+18 MANUAL  ENABLED  YES
session 2：

SQL> /

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |    36 |  1476 |   142   (0)| 00:00:02 |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   142   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    70   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_0177d85381f70000" used for this statement


Statistics
----------------------------------------------------------
        394  recursive calls
          0  db block gets
        700  consistent gets
          1  physical reads
          0  redo size
       2132  bytes sent via SQL*Net to client
        541  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         36  rows processed

不用设置，自动生效。

删除：

session 3：
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0177d85381f70000'); 

SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles; 

no rows selected

事后查询：

SQL> SELECT * FROM  dba_ADVISOR_TASKS ;
TASK_ID	TASK_NAME	DESCRIPTION	ADVISOR_NAME	CREATED	LAST_MODIFIED	PARENT_TASK_ID	PARENT_REC_ID	LAST_EXECUTION	EXECUTION_TYPE	EXECUTION_START	EXECUTION_END	STATUS	STATUS_MESSAGE	PCT_COMPLETION_TIME	PROGRESS_METRIC	METRIC_UNITS	ACTIVITY_COUNTER	RECOMMENDATION_COUNT	ERROR_MESSAGE	SOURCE	HOW_CREATED	READ_ONLY	SYSTEM_TASK	ADVISOR_ID	STATUS#
1	SYS_AUTO_SQL_TUNING_TASK	Automatic SQL Tuning Task	SQL Tuning Advisor	2019/11/21 11:47	2019/11/21 11:47	0	0					INITIAL		0	0		0	0			AUTO	FALSE	TRUE	4	1
12	TASK_12		SQL Tuning Advisor	2021/2/25 16:16	2021/2/25 16:16	0	0	EXEC_2	TUNE SQL	2021/2/25 16:16	2021/2/25 16:16	COMPLETED		0	0		0	0			CMD	FALSE	FALSE	4	3

–从此处也可以看出，sql自动调优又2个，一个是自动的，一个是我们手动调用的

SQL Tuning Advisor

SQL Tuning Advisor Analyze individual SQL statements, and recommend SQL profiles, statistics, indexes, and restructured SQL to SQL performance.
Automatic SQL Tuning Results View the results of automated execution of SQL Tuning Advisor on observed high-load SQL.

限制
1、sql没有倍aget out出去，才可以绑定
2、automatic类型的sql profile并不能完全起到稳定目标SQL的执行计划的作用，虽然它确实可以用来调整执行计划。

1、在实际执行过程中，如果sql_id已经不在v$sql中会报错，此时这种方法已经无用武之地了
SQL> var tuning_task varchar2(100);  
SQL> DECLARE  
  2    l_sql_id v$session.prev_sql_id%TYPE;  
  3    l_tuning_task VARCHAR2(30);  
  4  BEGIN  
  5    l_sql_id:='4zbqykx89yc8v';  
  6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  7    :tuning_task:=l_tuning_task;  
  8    dbms_sqltune.execute_tuning_task(l_tuning_task);  
  9    dbms_output.put_line(l_tuning_task);  
 10  END;  
 11  /  
DECLARE
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655
ORA-06512: at line 6

总结：

在实际生产环境中，常常由于：1）sql突然执行就慢了；2）经常走错执行计划；3) 想要优化sql，但无法修改sql文本的需求，就需要固定sql的执行计划，常用手段主要包括有：
SQL Profile(Oracle 10g以后)
SQL Plan Baseline Management(Oracle 11g以后)
SQL Patch
Outline
一定要注意使用场景，因为所有的技术都离不开自己适合的场景。
sql profile可以用来固定执行计划，它有两种方式，一是automatic，利用oracle自动调优的功能，使oracle产生最优的执行计划；而是manual方式，利用人类自己的经验，加hint，产生最优的执行计划，进行绑定。
automatic绑定的步骤如下：

1. 查询sql_id

SQL> select SQL_ID,sql_text,PARSE_CALLS,LOADS,EXECUTIONS from v$sql where SQL_TEXT like '%from t1%' and sql_text not like '%v$sql%';

SQL_ID        SQL_TEXT                                                     PARSE_CALLS      LOADS EXECUTIONS
------------- ------------------------------------------------------------ ----------- ---------- ----------
4zbqykx89yc8v select t1.*,t2.owner from t1,t2 where t1.object_name like '%           1          2          1
              T1%' and t1.object_id=t2.object_id
 2. 执行autotune优化任务

var tuning_task varchar2(100);  
DECLARE  
  l_sql_id v$session.prev_sql_id%TYPE;  
  l_tuning_task VARCHAR2(30);  
BEGIN  
  l_sql_id:='4zbqykx89yc8v';  
  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  :tuning_task:=l_tuning_task;  
  dbms_sqltune.execute_tuning_task(l_tuning_task);  
  dbms_output.put_line(l_tuning_task);  
END;  
/  
set long 10000 --SQL*PLUS中不要忘记执行，否则看不全优化结果（切记）
set long 10000
set longchunksize 1000
set linesize 100
set heading off
print tuning_task;  
SELECT dbms_sqltune.report_tuning_task(:tuning_task) FROM dual; 
 3. 接受这个SQL Profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task,replace => TRUE,force_match=>true);
 3.1. 当你运行SQL Tuning Advisor后，建议你接受一个SQL Profile，如果你想在接受SQL Profile前知道它到底为你提供了些什么，可以运行以下查询获得：
select
--b.ATTR1        -- 10g 列
b.ATTR5        -- 11g 列
from wri$_adv_tasks a,wri$_adv_rationale b 
where a.name = '$task_name' 
and b.task_id = a.id 
order by b.rec_id, b.id;      
 4. 查看通过SQL Tuning Advisor创建的SQL Profile使用到的hint。

col HINT for a100
SELECT extractValue(value(h), '.') AS hint
  FROM sys.sqlobj$data od,
       sys.sqlobj$ so,
       table(xmlsequence(extract(xmltype(od.comp_data),
                                 '/outline_data/hint'))) h
 WHERE so.name = '&profile_name'
   AND so.signature = od.signature
   AND so.category = od.category
   AND so.obj_type = od.obj_type
   AND so.plan_id = od.plan_id;

automatic类型的sql profile并不会像stored outline那样锁定目标sql的执行计划，因为automatic类型的sql profile的本质就是针对目标sql的一些额外的调整信息，这些额外的调整信息需要与原目标sql的相关统计信息等内容一起作用才能得到新的执行计划，即原始sql的统计信息等内容一旦发生变化，即使原有automatic类型的sql profile并没有改变，该sql的执行计划也可能会发生变化，从这个意义讲，automatic类型的sql profile并不能完全起到稳定目标sql的执行计划的作用，虽然它确实可以用来调整执行计划。
如：[CDATA[OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)] 记录的是原表的行数倍数，要以原表的统计信息为基准，所以一旦原表发生变化，这个就会出错。
参数force_match默认值是false，表示只有在SQL文本完全匹配的情况下才会应用SQL Profile，这种情况下只要目标SQL的SQL文本发生一点变动，原有的SQL profile就将失去作用。但是没测出效果。待验证。但是可以发现，2条sql用到的sql profile不一样。
/* SQL Analyze(38,0) */这种开头的，这是数据库SQL优化的包，自己调用的，是标记这种优化包调用的。一是我们这种手动调用的dbms_sqltune过程，二是自动维护任务，sql tuning advisor。其本质都是调用dbms_sqltune过程。它们最明显的特征就是前面以/* SQL Analyze(38,0) */ 开头。
限制：

1、sql没有倍aget out出去，才可以绑定
2、automatic类型的sql profile并不能完全起到稳定目标SQL的执行计划的作用，虽然它确实可以用来调整执行计划。

查询sql profile适用的hint

10G可以通过如下查询来获取SQL PROFILE使用的HINT
SELECT attr_val
FROM sys.sqlprof$ p, sys.sqlprof$attr a
WHERE p.sp_name = 'opt_estimate'
AND p.signature = a.signature
AND p.category = a.category;


11G后这个查询不再有效，实际上涉及到的底层表也已经改变。可以通过如下查询获取：
SELECT extractValue(value(h),'.') AS hint
FROM sys.sqlobj$data od, sys.sqlobj$ so,
table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
WHERE so.name = 'opt_estimate'
AND so.signature = od.signature
AND so.category = od.category
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;