SQL Plan Management

Before 10G we can capture a SQL statement’s execution plan and save it into a stored outline.

Thus we can change the execution plan without any SQL modification.

In 10G it is called SQL Profiles .

In 11G it is called SQL Plan Management.

They take same fuctions although the ways of implementation are different.

See the example like below:

SQL> create table t (id number,name varchar2(100));

表已创建。

SQL> insert into t select rownum,object_name from dba_objects;

已创建72993行。

SQL> commit;

提交完成。

SQL> set autotrace traceonly exp stat;
SQL>

SQL> select * from t where id=3;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------

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

1 - filter("ID"=3)

Note
-----
- rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
2 recursive calls
1 db block gets
382 consistent gets
0 physical reads
176 redo size
480 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select sql_text,signature,sql_handle,plan_name,origin,enabled,accepted, autopurge
from dba_sql_plan_baselines where sql_text like '%select * from t where id=3%'
SQL> /

未选定行

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

会话已更改。

SQL> select * from t where id=3;

ID
----------
NAME
--------------------------------------------------------------------------------
3
CON$

SQL> select sql_text,signature,sql_handle,plan_name,origin,enabled,accepted, autopurge
2 from dba_sql_plan_baselines where sql_text like '%select * from t where id=3%';

SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE SQL_HANDLE PLAN_NAME
---------- ------------------------------ ------------------------------
ORIGIN ENA ACC AUT
-------------- --- --- ---
select * from t where id=3
9.9679E+18 SYS_SQL_8a54f32d904f9eda SQL_PLAN_8np7m5q84z7qu94ecae5c
AUTO-CAPTURE YES YES YES

Add the index.
SQL> create index idx_t on t(id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true)

PL/SQL 过程已成功完成。

SQL> select * from t where id=3;

ID
----------
NAME
--------------------------------------------------------------------------------
3
CON$

SQL> select sql_text,signature,sql_handle,plan_name,origin,enabled,accepted, autopurge
2 from dba_sql_plan_baselines where sql_text like '%select * from t where id=3%';

SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE SQL_HANDLE PLAN_NAME
---------- ------------------------------ ------------------------------
ORIGIN ENA ACC AUT
-------------- --- --- ---
select sql_text,signature,sql_handle,plan_name,origin,enabled,accepted, autopurg
8.0612E+18 SYS_SQL_6fdf0504e8efd004 SQL_PLAN_6zrs50mnfzn04391601ca
AUTO-CAPTURE YES YES YES

select * from t where id=3
9.9679E+18 SYS_SQL_8a54f32d904f9eda SQL_PLAN_8np7m5q84z7qu880b6daf
AUTO-CAPTURE YES NO YES

SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE SQL_HANDLE PLAN_NAME
---------- ------------------------------ ------------------------------
ORIGIN ENA ACC AUT
-------------- --- --- ---

select * from t where id=3
9.9679E+18 SYS_SQL_8a54f32d904f9eda SQL_PLAN_8np7m5q84z7qu94ecae5c
AUTO-CAPTURE YES YES YES
the changed plan(using index) was captured automatically but can not be used by itself becaue the first one is default.
SQL> select * from t where id=3;

ID
----------
NAME
--------------------------------------------------------------------------------
3
CON$

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 96 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 1 | 29 | 96 (2)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("ID"=3)

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

SQL Plan Management

See the accept column is NO for the second SQL PLAN.

Let us disable the first one .Then the second is not accepted either.

SQL Plan Management

SQL> /

ID
----------
NAME
--------------------------------------------------------------------------------
3
CON$

执行计划
----------------------------------------------------------
Plan hash value: 1594971208

--------------------------------------------------------------------------------
-----

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-----

| 0 | SELECT STATEMENT | | 1 | 29 | 1 (0)| 00:00
:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 29 | 1 (0)| 00:00
:01 |

|* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00
:01 |

--------------------------------------------------------------------------------
-----

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

2 - access("ID"=3)

Let us enable the second one.

Make it accepted.

SQL Plan Management

相关推荐