修正执行计划的利器，SQL PROFILE脚本

点击这里下载 sql profile 脚本 profile.sql

我们先来看下如何手工创建sql profile来修正执行计划。
创建两张表test1、test2,在表test2的列object_id 上创建索引，分析两张表。

create table test1 as select * from dba_objects;

create table test2 as select * from dba_objects;

create index test2_oi_ind on test2(object_id);

begin

  dbms_stats.gather_table_stats(ownname          =>'test',

                                tabname          => 'test1',

                                no_invalidate    => FALSE,

                                estimate_percent => 100,

                                force            => true,

                                degree         => 5,

                                method_opt       => 'for  all columns  size 1',

                                cascade          => true);

end;

/

begin

  dbms_stats.gather_table_stats(ownname          =>'test',

                                tabname          => 'test2',

                                no_invalidate    => FALSE,

                                estimate_percent => 100,

                                force            => true,

                                degree         => 5,

                                method_opt       => 'for  all columns  size 1',

                                cascade          => true);

end;

/

然后看看如下一个SQL：

select test2.object_name, test2.object_type
  from test1, test2
 where test1.object_type like '%RULE'
   and test1.object_id = test2.object_id;

OBJECT_NAME          OBJECT_TYPE

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

ALERT_QUE$1          RULE

1 row selected.

默认的执行计划为hash join：

Plan hash value: 497311279

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

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

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

|   0 | SELECT STATEMENT   |       |   774 | 33282 |   100   (2)| 00:00:02 |

|*  1 |  HASH JOIN         |       |   774 | 33282 |   100   (2)| 00:00:02 |

|*  2 |   TABLE ACCESS FULL| TEST1 |   774 |  9288 |    50   (2)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST2 | 15478 |   468K|    49   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")

   2 - filter("TEST1"."OBJECT_TYPE" LIKE '%RULE' AND

              "TEST1"."OBJECT_TYPE" IS NOT NULL)

如果我们想让执行计划走NEST LOOP JOIN，而不是优化器选用的HASH JOIN，该如何办？

步骤一：查找到你的查询块的名称

SELECT operation,options,object_name,object_alias

FROM v$sql_plan

WHERE sql_id='7uga1u1twnqw1'

AND child_number=0

/

OPERATION                 OPTIONS              OBJECT_NAME          OBJECT_ALIAS

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

SELECT STATEMENT

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS              FULL                 TEST1                TEST1@SEL$1

INDEX                     RANGE SCAN           TEST2_OI_IND         TEST2@SEL$1

TABLE ACCESS              BY INDEX ROWID       TEST2                TEST2@SEL$1

也可以通过select * from table(dbms_xplan.display_cursor(sql_id,null,'outline'))来查看Outline Data来获取查询块。我更喜欢这种方法。

步骤二：构建你的HINT

leading(@"SEL$1" TEST1@"SEL$1") use_nl(@"SEL$1" TEST2@"SEL$1")

步骤三：创建你的SQL PROFILE

declare

l_profile_name varchar2(30);

cl_sql_text clob;

begin

select

sql_fulltext

into

cl_sql_text

from

v$sqlarea

where

sql_id = '7uga1u1twnqw1';

dbms_sqltune.import_sql_profile(

sql_text => cl_sql_text,

profile => sqlprof_attr(q'[leading(@"SEL$1" TEST1@"SEL$1") use_nl(@"SEL$1" TEST2@"SEL$1")]'),

category => '',

name => 'dwrose_xxx',

force_match =>FALSE

);

end;

/

步骤四，确认你的执行计划已经按照要求被改变：

select test2.object_name,test2.object_type from test1,test2 where test1.object_type like '%RULE' and test1.object_id=test2.object_id;

OBJECT_NAME          OBJECT_TYPE

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

ALERT_QUE$1          RULE

sys@DLSP>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID  bhm28h5575bjy, child number 0

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

select test2.object_name,test2.object_type from test1,test2 where

test1.object_type like '%RULE' and test1.object_id=test2.object_id

Plan hash value: 800282841

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

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

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

|   0 | SELECT STATEMENT             |              |       |       |  1598 (100)|          |

|   1 |  NESTED LOOPS                |              |       |       |            |          |

|   2 |   NESTED LOOPS               |              |   774 | 33282 |  1598   (1)| 00:00:20 |

|*  3 |    TABLE ACCESS FULL         | TEST1        |   774 |  9288 |    50   (2)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | TEST2_OI_IND |     1 |       |     1   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| TEST2        |     1 |    31 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - filter(("TEST1"."OBJECT_TYPE" LIKE '%RULE' AND "TEST1"."OBJECT_TYPE" IS NOT

              NULL))

   4 - access("TEST1"."OBJECT_ID"="TEST2"."OBJECT_ID")

Note

-----

   - SQL profile dwrose_xxx used for this statement

通过执行计划输出的Note部分可以看到SQL PROFILE已经被使用。

OK，看我们通过如何通过sql profile脚本把执行计划修正为NEST LOOP JOIN，这个过程将变得更加简单：