11g 改变SQL执行计划

最新推荐文章于 2021-04-12 23:59:59 发布

congju4884

最新推荐文章于 2021-04-12 23:59:59 发布

阅读量230

点赞数

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10113559/viewspace-715841/