在上一篇《SQL Profiles-Part I》,我向大家介绍了什么是SQL Profiles及其作用,如何使用SQL Tuning Advisor来生成SQL Profile,以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。那么在今天,将向大家介绍如何手工创建SQL Profiles(即不通过SQL Tuning Advisor)来达成2个目的:

  • 锁定或者说稳定SQL执行计划。
  • 在不能修改应用的SQL的情况下,来改变或者说是强制使SQL使用我们指定的执行计划,即使原始的SQL包含了Hints。

那么,这里最关键的一点是,如何来手工创建SQL Profiles?
答案是,正如上一篇中有朋友的留言,使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。

SQL> desc dbms_sqltune
...
PROCEDURE IMPORT_SQL_PROFILE
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 SQL_TEXT                       CLOB                    IN
 PROFILE                        SQLPROF_ATTR            IN
 NAME                           VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT
 CATEGORY                       VARCHAR2                IN     DEFAULT
 VALIDATE                       BOOLEAN                 IN     DEFAULT
 REPLACE                        BOOLEAN                 IN     DEFAULT
 FORCE_MATCH                    BOOLEAN                 IN     DEFAULT
...

这个过程其名字与实际功能有所差异,其实可以理解为CREATE OR REPLACE SQL_PROFILE。过程中的PROFILE参数为SYS.SQLPROF_ATTR,这种类型其实就是VARCHAR2的集合类型(COLLECTION):

SQL> select text from dba_source where name='SQLPROF_ATTR' and owner='SYS';

TYPE     sqlprof_attr
 AS VARRAY(2000) of VARCHAR2(500)

下面我们就用这个过程来创建SQL PROFILE:
为避免干扰,将上一篇测试中生成的SQL Profile删除掉,同时恢复T1表的统计信息中的表行数:

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>49953);

PL/SQL 过程已成功完成。

现在我们手工创建一个SQL Profile:

SQL> declare
  2    v_hints sys.sqlprof_attr;
  3  begin
  4    v_hints:=sys.sqlprof_attr('USE_NL(T1 T2)','INDEX(T2)');
  5    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
  6                v_hints,'SQLPROFILE_NAME1',force_match=>true);
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
  2  where a.signature = b.signature
  3  and a.name='SQLPROFILE_NAME1';

ATTR_VAL
----------------------------------------
USE_NL(T1 T2)
INDEX(T2)

下面执行SQL Profiles对应的SQL:

SQL> select  t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2498 | 99920 |   219   (4)| 00:00:03 |
|*  1 |  HASH JOIN         |      |  2498 | 99920 |   219   (4)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2498 | 72442 |    59   (6)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 49954 |   536K|   159   (2)| 00:00:02 |
---------------------------------------------------------------------------

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

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

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

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        933  consistent gets
        

可以看到,SQL使用了SQL Profile,不过没有达到我们预期的效果。

看起来是SQL Profile使用的Hints有问题。我们重新设置SQL Profile的Hints,在Hints中加上“Query Block Name"。这一次在执行IMPORT_SQL_PROFILE过程时,将REPLACE参数设置为TRUE,以替换现有的SQL Profile:

SQL> declare
  2    v_hints sys.sqlprof_attr;
  3  begin
  4    v_hints:=sys.sqlprof_attr('USE_NL(T1@SEL$1 T2@SEL$1)','INDEX(T2@SEL$1)');
  5    dbms_sqltune.import_sql_profile('select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id=t2.object_id',
  6                v_hints,'SQLPROFILE_NAME1',force_match=>true,replace=>true);
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b
  2  where a.signature = b.signature
  3  and a.name='SQLPROFILE_NAME1';

ATTR_VAL
----------------------------------------
USE_NL(T1@SEL$1 T2@SEL$1)
INDEX(T2@SEL$1)  

再次执行下面的SQL:

SQL> select  t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

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

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        294  consistent gets

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。
那么我们再一次手工修改T1表的统计信息,看看结果如何:

SQL> exec dbms_stats.set_table_stats('TEST1','T1',numrows=>5000000);

PL/SQL 过程已成功完成。

SQL> select  t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |
|*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        294  consistent gets

可以看到,Oracle优化器评估表T1经过Like条件过滤后返回的行数虽然很大,但是这里的执行计划仍然与未修改统计信息之前一样,使用range scan+ nested loop join。

通过以上的测试,我们明白了DBMS_SQLTUNE.IMPORT_SQL_PROFILE的使用,同时也验证了这种方式的有效性,SQL Profiles能够像Outlines一样,能够稳定SQL的执行计划。

接下来我们需要完成两个任务。
任务一:对现有的SQL稳定其执行计划。
这里的问题是:稳定一条SQL语句的Hints从哪里来?简单的sql,没问题,我们可以手工构造,但是复杂的SQL,手工构造相对比较复杂,同时手工构造的Hints不一定能够保证SQL的执行计划就会稳定。从10g开始,v$sql_plan中就包括了SQL语句OUTLINE数据,也就是稳定执行计划的Hints。从下面可以看到:

SQL> select  t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;


已选择29行。

SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  6m45w7r0xgdfj, child number 0
-------------------------------------
select  t1.*,t2.owner      from t1,t2      where t1.object_name like '%T1%'
   and t1.object_id=t2.object_id

Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |  5061 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |  2498 | 99920 |  5061   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL        | T1     |  2498 | 72442 |    59   (6)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      USE_NL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */

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

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

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

上面所显示的“Outline Data”即是我们稳定SQL执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的SQL中)。对需要稳定执行计划的SQL,我们所要做的就是如前面所示,将Hints与SQL文本一起创建一个SQL Profile。这里不得不提到一个SQL脚本,来自MOS。"SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]",在这篇文章中,可以下载到sqlt.zip,这个压缩文件内有一个文件:coe_xfr_sql_profile.sql。这个脚本可以用于从shared pool、awr中提取指定的SQL ID的Outline Data并创建SQL Profile。下面是示例:


SQL> select /*+ proftest1 */ t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

--在shared pool中查找刚刚执行的SQL,其ID为b4zvp712np1bp--

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

输入 1 的值:  b4zvp712np1bp


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     2959412835        .112

Parameter 2:
PLAN_HASH_VALUE (required)

输入 2 的值:  2959412835

Values passed:
~~~~~~~~~~~~~
SQL_ID         : "b4zvp712np1bp"
PLAN_HASH_VALUE: "2959412835"


Execute coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql
on TARGET system in order to create a custom SQL Profile
with plan 2959412835 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
  6058051510930011685


... manual custom SQL Profile has been created


COE_XFR_SQL_PROFILE_b4zvp712np1bp_2959412835 completed

SQL>select attr_val from sys.sqlprof$attr where signature=6058051510930011685;

ATTR_VAL
--------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T2"@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
USE_HASH(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA

coe_xfr_sql_profile.sql这个脚本首先要求输入sql id,然后从shared pool、awr中获取sql执行的各个执行计划的统计信息(执行计划不稳定的SQL通常会有多个不同的执行计划),然后输入你认为是正确的、需要稳定的执行计划的hash value,脚本就会生成另一个脚本,这里为coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql,然后运行这个脚本,就会创建出稳定执行计划所需要的SQL Profile,SQL Profile的名字为:coe+sql_id+plan_hash_value,这里为coe_b4zvp712np1bp_2959412835。注意,这里创建的SQL Profile,force match默认为FALSE,我们可以手工修改脚本将其改为TRUE,同时我们也可以按意愿来修改生成的脚本的其他内容。

除了上面提到的脚本,http://kerryosborne.oracle-guy.com这个BLOG里面也有许多与SQL Profiles相关的脚本。其中create_sql_profile.sql可完成类似的功能,只不过功能相对简单,只能从shared pool中生成SQL Profile,因此也更方便。

任务二:在不能修改SQL的情况下改变并固定SQL的执行计划,即使原始的SQL使用了Hints。
常常遇到这样的情况,SQL语句其执行计划有问题,或者是SQL使用了错误的Hints(比如 /*+ RULE */)导致SQL性能较差,但是应用又不能修改或者时间内不能修改,那么我们怎么来改变SQL的执行计划呢。有3种办法,一种是调整统计信息,这个不建议使用,因为比较复杂、不稳定可靠(统计信息可能会重新收集),影响面广(会影响其他访问此对象的SQL)。第二种是使用OUTLINE,这种方法比较复杂。第三种就是我们今天要介绍的使用SQL Profiles了。

使用SQL Profiles来改变SQL的执行计划,其本质上就是使用Hints来改变SQL的执行计划。对于简单的SQL,我们同样可以像前面一样手工构造Hints然后再使用DBMS_SQLTUNE.IMPORT_SQL_PROFILE来实现。但是这种方法还是略显烦琐。那么通常的方法就是"乾坤大挪移“了:

  • 取得原始SQL的文本(如有可能还包括sql id)
  • 构造一个与原始SQL在逻辑上、结构上完全相同的SQL。这里强制逻辑上和结构上相同,SQL解析的用户名、SQL中引用对象的用户名甚至是一些predicate条件都可以不同。当然能够与原始SQL完全一样就更省事。
  • 执行我们构造的SQL,并取得构造的SQL的Outline Data。
  • 使用原始SQL的文本和构造的SQL的Outline Data创建SQL Profile。

下面我们来演示一下整个过程。我们这里要修改执行计划的SQL是:

select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner   
     from t1,t2   
     where t1.object_name like '%T1%'   
     and t1.object_id=t2.object_id; 

我们首先需要执行这一条SQL,然后取得的SQL ID为gmvb9bp7f9kqd:

SQL> select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T1%'
  4       and t1.object_id=t2.object_id;

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   250K|  9765K|       |   998  (25)| 00:00:12 |
|*  1 |  HASH JOIN         |      |   250K|  9765K|  1128K|   998  (25)| 00:00:12 |
|   2 |   TABLE ACCESS FULL| T2   | 49954 |   536K|       |   159   (2)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| T1   |   250K|  7080K|       |   288  (81)| 00:00:04 |
----------------------------------------------------------------------------------- 

然后我们构造一条SQL,让这条SQL按我们希望的执行计划运行,构造的SQL其ID为cymak300cycmd:

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

已选择29行。

执行计划
----------------------------------------------------------
Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |
|*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

然后使用coe_xfr_sql_profile.sql脚本来提取我们构造的SQL的Outline Data,生成的结果为coe_xfr_sql_profile_cymak300cycmd_3787413387.sql,打开结果文件,可以看到有这么一段:

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.1')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
q'[INDEX(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))]',
q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "T2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

再针对gmvb9bp7f9kqd使用coe_xfr_sql_profile.sql,生成的结果文件为coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql。手工修改这个文件,将里面h := SYS.SQLPROF_ATTR...那一段替换成我们之前得到的那一段。这一次我们将这个文件中的force_match从FALSE改成TRUE。
最后我们运行coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql这个脚本文件:

SQL>@coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql

PL/SQL 过程已成功完成。

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

            SIGNATURE
---------------------
 15409905709853673912

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_gmvb9bp7f9kqd_2959412835 completed

这样就完成了我们所需要的SQL Profile的创建。下面再看看原来的SQL执行情况(这里我故意将like条件改了一下,以查看force match是否起作用):

SQL>select /*+ orig_sql full(t1) full(t2) use_hash(t1 t2) */ t1.*,t2.owner
  2       from t1,t2
  3       where t1.object_name like '%T2%'
  4       and t1.object_id=t2.object_id;

已选择77行。

执行计划
----------------------------------------------------------
Plan hash value: 3787413387

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   250K|  9765K|   500K  (1)| 01:40:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |   250K|  9765K|   500K  (1)| 01:40:12 |
|*  3 |    TABLE ACCESS FULL        | T1     |   250K|  7080K|   288  (81)| 00:00:04 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

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

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        363  consistent gets

可以看到SQL Profile起作用了。

最后一步,生成SQL Profile时稍显复杂,不过我们可以修改之前提到的create_sql_profile.sql文件来达到同样的目的,只不过前几个步骤仍然是不可省略的。将里面的代码:

select extractvalue(value(d), '/hint') as outline_hints bulk collect
  into ar_profile_hints
  from xmltable('/*/outline_data/hint' passing
                (select xmltype(other_xml) as xmlval
                   from v$sql_plan
                  where sql_id = '&&sql_id'
                    and child_number = &&child_no
                    and other_xml is not null)) d;

改为

select extractvalue(value(d), '/hint') as outline_hints bulk collect
  into ar_profile_hints
  from xmltable('/*/outline_data/hint' passing
                (select xmltype(other_xml) as xmlval
                   from v$sql_plan
                  where sql_id = '&&modi_sql_id'
                    and child_number = &&modi_child_no
                    and other_xml is not null)) d;

注意这里modi_sql_id和modi_child_no为我们构造的SQL执行后的id及child_number。同时这2个变量在文件前面需要定义,此处不再细述。

小结:本文承接上一篇,介绍了如何利用SQL Profile来稳定执行计划;如何利用SQL Profile来改变SQL的执行计划。对于SQL Profiles来说,不属于任何一个用户,比Outlines更具有操控性灵活性。对于SQL Profiles的category,这里不做介绍,有兴趣的朋友请参考文档。

--The end---

,
Trackback

19 comments untill now

  1. good

  2. 谢谢分享,很有用

  3. 甲骨虫虫 @ 2011-01-11 23:25

    回复的太长了,发不上去?

    老熊 Reply:

    在另一篇文章上面吧?这个?http://www.laoxiong.net/how-keeping-sql-plan-stability.html#comment-1095

  4. 甲骨虫虫 @ 2011-01-12 09:22

    有个简体版的脚本,我在网上看到的,改写了下,临危救命用
    母本:http://space.itpub.net/10972173/viewspace-674702

  5. 请问,什么是query block,我只是大概知道是10g中为HINT而提供的。但概念很模糊,能推荐相关文档么?
    谢谢。

  6. 老熊你好:
    手工创建SQL Profiles很棒很实用,谢谢分享;实践中碰到一种特例:
    当SQL超长(>10000bytes),须改写为折行的形式才能写入import函数作为参数使用,但发现生成的signature与原SQL不同,导致PROFILE无效。
    我也测试了简单的语句,如select 1 from dual; 这样的语句中增加换行符是没问题的,signature不变。不明白关键在哪里。
    谢谢。

    老熊 Reply:

    IMPORT_SQL_PROFILE过程中的SQL_TEXT为CLOB类型,足以支持超长的SQL。
    折行后生成的SQL的force_matching_signature与exact_matching_signature均不相同吗?

  7. 学习了,解决了今天的问题

  8. daniel1237 @ 2011-07-12 17:12

    老熊您好!
    我在生成hints的时候,长度超过了500,有什么好办法解决?还有oracle metalink上[ID 215187.1]的上sqlt.zip压缩文件无法解压,能否提供一个上传。谢谢!

    老熊 Reply:

    sqlt.zip我可以邮件一份给你,请提供一下你的email地址。
    生成hints长度超过了500,就不能生成了吗?不知道你是用的什么办法。我们可以邮件交流一下。

  9. 老熊您好!
    能否发一份sqlt.zip 给我(hdbhdbhdb3@sina.com)?
    谢谢!

  10. 为啥没有这个表呢sqlprof_attr

  11. 同之前一个网友的问题,hint超过500之后如何处理呢?因为今天处理一个生产问题就遇到了生成的sqlprof_attr超过了500,不知道熊哥有没有什么好的方法解决,谢谢。

  12. 如果一个库,不同schema,同样的sql就会影响到,那这个方法就不能用了吧,11g 的oracle baseline也有同样的状况。

  13. 熊哥,你好,我按照你的文章,在做实验的时候,SQL> select * from table(dbms_xplan.display_cursor(null,null,’outline’));

    PLAN_TABLE_OUTPUT
    ——————————————————————————

    Error: format ‘OUTLINE’ not valid for DBMS_XPAN.DISPLAY_CURSOR()
    老提示上面的错误,请问这是为什么? 我的数据库版本是: 10.1.0.2.0。
    还有熊哥,能麻烦你把sqlt.zip 发我吗?

  14. 手工创建sql profiles,没起作用
    NAME CATEGORY SIGNATURE SQL_TEXT CREATED LAST_MODI DESCRIPTIO TYPE STATUS FOR
    —————————— —————————— ———- —————————— ——— ——— ———- ——— ——– —
    SQLPROFILE_NAME1 DEFAULT 3.3487E+18 select /*+ index(t,i_t) */ * f 05-JUL-12 05-JUL-12 MANUAL ENABLED YES
    rom t where object_id select /*+ index(t,i_t) */ * from t where object_id<50000;

    48514 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 2616361825

    ————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————-
    | 0 | SELECT STATEMENT | | 48520 | 189K| 110 (1)| 00:00:02 |
    |* 1 | INDEX RANGE SCAN| I_T | 48520 | 189K| 110 (1)| 00:00:02 |
    ————————————————————————-

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

    1 – access(“OBJECT_ID”<50000)

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    3335 consistent gets
    0 physical reads
    0 redo size
    883071 bytes sent via SQL*Net to client
    36066 bytes received via SQL*Net from client
    3236 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    48514 rows processed

  15. dafufeng @ 2014-04-05 15:35

    前辈您好,在《DBA手记3》SQLPROFILE实践中执行select attr_val from dba_sql_profiles a, sys.sqlprof$attr b where a.signature = b.signature and a.name=’SQLPROFILE_NAME1′;
    报table or view does not exist!请指教!谢谢%

  16. 熊哥,你好。我有一个疑问,import_sql_profile 已经可以固定执行计划了,为什么还要用coe_xfr_sql_profile.sql去再次操作一遍,这不是多此一举吗?
    谢谢!

Add your comment now