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

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> /

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> /

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)

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