11g 改变SQL执行计划

1.先查看是否启用了baseline

SQL> show parameter sql
ufbP:ob@/J k0 ITPUB个人空间5L$W0m&E)Z!Y
NAME                                 TYPE        VALUEITPUB个人空间 t+b R
`t0g
------------------------------------ ----------- ------------------------------
$f;M1E&BCW.HVO0optimizer_capture_sql_plan_baselines boolean     FALSEITPUB个人空间+|h:T5Q@ s\'OS3L
optimizer_use_sql_plan_baselines     boolean     TRUE

j$D'yW4v [ [8\02.查看不同的执行计划：

坏的执行计划：ITPUB个人空间$dTPMZIa
SQL>  SELECT/*+ full(t)*/* FROM TEST.TEST_HARDPARSE T WHERE T.A=200;

         A B
[ Kz#Y(yI)F4st!R9\0---------- ---------------ITPUB个人空间^!AcZA-^
       200 28-OCT-11

ITPUB个人空间2UYR!W\/l5x2h(wYq
Execution PlanITPUB个人空间"f7ub9cVl
----------------------------------------------------------
#N Rm {p1bd3J0Plan hash value: 2522350317

------------------------------------------------------------------------------------
2vaz Xfir
W/o0| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间1G"h3gv$Y%X.`
------------------------------------------------------------------------------------ITPUB个人空间!md%X2[&WkU
|   0 | SELECT STATEMENT  |                |     1 |    12 |     3   (0)| 00:00:01 |
P7k8Xb7Gi(r)]7ub0|*  1 |  TABLE ACCESS FULL| TEST_HARDPARSE |     1 |    12 |     3   (0)| 00:00:01 |ITPUB个人空间5Xt~5q#n UGs
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
!Kl#t7M["lK2z*o%d0---------------------------------------------------

   1 - filter("T"."A"=200)

好的执行计划：
`n!E%}Vy8l^0SQL>  SELECT * FROM TEST.TEST_HARDPARSE T WHERE T.A=200;

         A BITPUB个人空间ink;L9O4[3j3yvQ
---------- ---------------ITPUB个人空间"f~(xTOwJ+^o@
       200 28-OCT-11

ITPUB个人空间%r+Ig?` j,y-n
y-I/_E
Execution Plan
#Cp:`#ul0----------------------------------------------------------ITPUB个人空间"i1G{&z-w
Plan hash value: 3429616802

----------------------------------------------------------------------------------------------
YCD'u'|)y7N1gG0| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间z7if;C)~XX#h|
----------------------------------------------------------------------------------------------ITPUB个人空间8zs8n*[h+B0es
|   0 | SELECT STATEMENT            |                |     1 |    12 |     2   (0)| 00:00:01 |
7Da`7G%@%UX0|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HARDPARSE |     1 |    12 |     2   (0)| 00:00:01 |ITPUB个人空间.K YfN#d_^B0z0G
|*  2 |   INDEX RANGE SCAN          | TTT            |     1 |       |     1   (0)| 00:00:01 |ITPUB个人空间"v2Gk4QNE-b9J1k]
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
qm"x&IZ0---------------------------------------------------

   2 - access("T"."A"=200)

tdL$wI4G5W03.将需要改变的执行计划的SQL，放入sql baseline，有两种方法：

  a)var  nv number;

    exec :nv:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'bp8twr81cz4sy');

  b)alter session set optimizer_capture_sql_plan_baselines = true;  执行2次坏的SQL.

EtF3cz
U8M)`&rx04.确认SQL已经被LOAD到spmITPUB个人空间.h?0g#C[u+B z
select  * from dba_sql_plan_baselines t order by t.created desc;ITPUB个人空间6brZN)iq r
1.21102128764653E19  SYS_SQL_a8101e72d0134aba  <CLOB>  SQL_PLAN_ah40yfb816kpu70d8e22c  SYS  MANUAL-LOAD  SYS    11.2.0.1.0  08-2月 -12 09.15.05.000000 上午  08-2月 -12 09.51.18.000000 上午  08-2月 -12 09.16.43.000000 上午    NO  YES  NO  YES  3 sqlplus@localhost.localdomain(TNS V1-V3)  2 7676 4999 16 0 0 2 4 2

5.使用坏的执行计划的sql_handle ，好的执行计划的SQL_ID 和好的 plan_hash_value 来做替换。ITPUB个人空间F3g.l7kk
var nu number;ITPUB个人空间)Hqa,r Cc
exec :nu:=dbms_spm.load_plans_from_cursor_cache( sql_handle => 'SYS_SQL_a8101e72d0134aba' ,sql_id => '9f6rj4v8d98mx' ,plan_hash_value => '3429616802' );

ITPUB个人空间Z%]1h%|%uv
6.验证是否使用了新的执行计划：
)V(MTX
H dr!q:K0SQL>  SELECT/*+ full(t)*/* FROM TEST.TEST_HARDPARSE T WHERE T.A=200;

         A B
$L(gv Yd$k"h(IAk0---------- ---------------
7ef/BW+nNR0       200 28-OCT-11

A4[A]2n,q1q/Pb kx
k0Execution PlanITPUB个人空间T$of/{1K8E9LUY
----------------------------------------------------------
)e;P(D|b,S0?uK:}0Plan hash value: 3429616802

----------------------------------------------------------------------------------------------ITPUB个人空间(n {kE/J*q
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
'CC Vt&O#f0----------------------------------------------------------------------------------------------
g0Qx;v|o0|   0 | SELECT STATEMENT            |                |     1 |    12 |     2   (0)| 00:00:01 |ITPUB个人空间8^9zn ySC
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HARDPARSE |     1 |    12 |     2   (0)| 00:00:01 |
m!Js&|:i0|*  2 |   INDEX RANGE SCAN          | TTT            |     1 |       |     1   (0)| 00:00:01 |
[0jvJ3w NF{6Q0----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
,N_ {7]DX8L"{~0---------------------------------------------------

   2 - access("T"."A"=200)

Note
s u'l/W#ty`-tvM^0-----
P[3@m]^v'U.m0   - SQL plan baseline "SQL_PLAN_ah40yfb816kpu61947bb7" used for this statement

ITPUB个人空间\\e0xnKW
7.如果好的执行计划和坏的执行计划都在base line中。也可以通过上面的方法互换执行计划。 互换后要将坏的执行计划置为 disable或者删除
FvQ,A tM0 var nu number;ITPUB个人空间agt}~4vX%n
 exec :nu:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_a8101e72d0134aba',plan_name => 'SQL_PLAN_ah40yfb816kpu70d8e22c',attribute_name => 'ENABLED',attribute_value => 'NO' );
:I9RACJm0 或者将其删除ITPUB个人空间WPS[ S&W
 var nu number;ITPUB个人空间n],L/G;l-k&]
 exec :nu:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_a8101e72d0134aba',plan_name => 'SQL_PLAN_ah40yfb816kpu70d8e22c');
,R-Q$Hd/l%eQ&V&f0 

z4W:}g5CD(l0