11g 改變SQL執行計劃

csbwolf10發表於2012-02-08
SQL

 

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章