ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11g 改变SQL执行计划

11g 改变SQL执行计划

原创 Linux操作系统 作者:csbwolf10 时间:2012-02-08 15:19:49 0 删除 编辑

 

1.先查看是否启用了baseline

SQL> show parameter sql
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE


2.查看不同的执行计划:

坏的执行计划:
SQL>  SELECT/*+ full(t)*/* FROM TEST.TEST_HARDPARSE T WHERE T.A=200;

         A B
---------- ---------------
       200 28-OCT-11


Execution Plan
----------------------------------------------------------
Plan hash value: 2522350317

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HARDPARSE |     1 |    12 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

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

 

好的执行计划:
SQL>  SELECT * FROM TEST.TEST_HARDPARSE T WHERE T.A=200;

         A B
---------- ---------------
       200 28-OCT-11


Execution Plan
----------------------------------------------------------
Plan hash value: 3429616802

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HARDPARSE |     1 |    12 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TTT            |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

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


3.将需要改变的执行计划的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.


4.确认SQL已经被LOAD到spm
select  * from dba_sql_plan_baselines t order by t.created desc;
1.21102128764653E19  SYS_SQL_a8101e72d0134aba    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 来做替换。
var nu number;
exec :nu:=dbms_spm.load_plans_from_cursor_cache( sql_handle => 'SYS_SQL_a8101e72d0134aba' ,sql_id => '9f6rj4v8d98mx' ,plan_hash_value => '3429616802' );


6.验证是否使用了新的执行计划:
SQL>  SELECT/*+ full(t)*/* FROM TEST.TEST_HARDPARSE T WHERE T.A=200;

         A B
---------- ---------------
       200 28-OCT-11


Execution Plan
----------------------------------------------------------
Plan hash value: 3429616802

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HARDPARSE |     1 |    12 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TTT            |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

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

Note
-----
   - SQL plan baseline "SQL_PLAN_ah40yfb816kpu61947bb7" used for this statement

 


7.如果好的执行计划和坏的执行计划都在base line中。也可以通过上面的方法互换执行计划。 互换后要将坏的执行计划置为 disable或者删除
 var nu number;
 exec :nu:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_a8101e72d0134aba',plan_name => 'SQL_PLAN_ah40yfb816kpu70d8e22c',attribute_name => 'ENABLED',attribute_value => 'NO' );
 或者将其删除
 var nu number;
 exec :nu:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_a8101e72d0134aba',plan_name => 'SQL_PLAN_ah40yfb816kpu70d8e22c');
 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10113559/viewspace-715841/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORACLE 坏块处理
请登录后发表评论 登录
全部评论

注册时间:2009-10-14

  • 博文量
    73
  • 访问量
    170681