SQL PROFILE修改固定执行计划

wei-xh

发给团队的，有需要的可以参照下。下面是原文，懒的编辑了

SQL PROFILE的用法示例，下周周会给应用DBA演示下。一般只需要步骤三、四就可以完成执行计划的修改和固定，而outline和baseline则需要N多个步骤。
SQL PROFILE使用简单,不区分大小写，回车，空格，但是对DBA写HINT的能力要求比较高，因为SQL PROFILE要求HINT必须写明查询块名。
我的示例里教了大家偷懒的做法，但是有时间我们还是最好认真把query block的东西学下。
SQL PROFILE还有其他一些牛逼的特性，具体其他细节，周会上再说。

步骤一-------------------------创建测试表，根据DBA_OBJECTS创建，OBJECT_ID上有索引
Create table wxh_tbd as select * from dba_objects;
create index t_3 on wxh_tbd(object_id);

步骤二-------------------------查看SQL默认执行计划,走了索引
explain plan for select * from wxh_tbd where object_id= :a;
select * from table(dbms_xplan.display(null,null,'outline'));-------------------通过指定outline可以获取到系统为我们生成的hint
-----------------------------------------------
| Id  | Operation                   | Name    |
-----------------------------------------------
|   0 | SELECT STATEMENT            |         |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |
|*  2 |   INDEX RANGE SCAN          | T_3     |
-----------------------------------------------
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "WXH_TBD"@"SEL$1" ("WXH_TBD"."OBJECT_ID"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.1.0.7')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

步骤3-------------------------如果我们想让它走全表扫描，获取全表扫描HINT
explain plan for select /*+ full(wxh_tbd) */* from wxh_tbd where object_id= :a;-----------增加HINT
select * from table(dbms_xplan.display(null,null,'outline'));------------可以看到全表扫描的hint已经为我们生成了，我们选取必要的hint就OK了，其他的可以不要
-------------------------------------
| Id  | Operation         | Name    |
-------------------------------------
|   0 | SELECT STATEMENT  |         |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |
-------------------------------------
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
     FULL(@"SEL$1" "WXH_TBD"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.1.0.7')
      OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

步骤4--------------------------使用sql profile
declare
  v_hints sys.sqlprof_attr;
begin
  v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "WXH_TBD"@"SEL$1")'); ----------从上面Outline Data部分获取到的HINT
  dbms_sqltune.import_sql_profile('select * from wxh_tbd where object_id= :a', ----------SQL语句部分
                                  v_hints,
                                  'WXH_TBD', --------------------------------PROFILE 的名字
                                  force_match => true);
end;
/

步骤五-------------------------查看是否生效，已经生效了
explain plan for select * from wxh_tbd where object_id= :a;
select * from table(dbms_xplan.display);
--------------------------------------
| Id  | Operation         | Name    |
--------------------------------------
|   0 | SELECT STATEMENT  |         |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |
--------------------------------------
Note
-----
   - SQL profile "WXH_TBD" used for this statement

[ 本帖最后由 wei-xh 于 2011-6-9 16:48 编辑 ]

ZALBB

楼主最好说明你的操作环境，

wei-xh

原帖由 ZALBB 于 2011-6-9 17:09 发表
楼主最好说明你的操作环境，

呵呵。
ops$admin@CRMG>select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

草中宝

楼主，10G的版本可以用吗？

yyp2009

10g为什么不能用？

wei-xh

原帖由 yyp2009 于 2011-6-9 18:24 发表
10g为什么不能用？

10G可以用。

westzq1984

高，学习了

lfree

我使用toad来做，更加傻瓜，我现在连命令都没记住

wei-xh

原帖由 lfree 于 2011-6-10 08:44 发表
我使用toad来做，更加傻瓜，我现在连命令都没记住

我们产品环境PL/SQL,TOAD这些都不能用，完全用PUTTY之类的工具。。。。。

JumFlow

9i  可以这样做吗？
我才做了哈，怎么看到这个效果。
=====================================================

SQL> select * from v$version where rownum = 1;

BANNER
-------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Error: format 'outline' unknown