Consider the following execution plan it is associated with a sql plan
baselines.
demo@ORA12C>
explain plan for
2
select count( distinct status )
3 from
t
4
where object_id = 42;
Explained.
demo@ORA12C>
@xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash
value: 2359337548
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 5 |
313 (15)| 00:00:01 |
| 1 |
SORT AGGREGATE | |
1 | 5 | | |
| 2 |
VIEW | VW_DAG_0
| 1 | 5 |
313 (15)| 00:00:01 |
| 3 |
HASH GROUP BY | |
1 | 12 | 313 (15)|
00:00:01 |
|* 4 |
TABLE ACCESS FULL| T | 1 |
12 | 312 (15)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 - filter("OBJECT_ID"=42)
Note
-----
- SQL plan baseline
"SQL_PLAN_01nrwm0nv64tsb2385278" used for this statement
The notes section shows that we are using the sql plan baselines, let’s
disable it.
demo@ORA12C>
begin
2
:n := dbms_spm.alter_sql_plan_baseline(
3 sql_handle
=>'SQL_00d2fc9829b31338',
4 plan_name
=>'SQL_PLAN_01nrwm0nv64tsb2385278',
5 attribute_name
=>'ENABLED',
6 attribute_value =>'NO');
7 end;
8 /
PL/SQL
procedure successfully completed.
demo@ORA12C>
print n
N
----------
1
demo@ORA12C>
select sql_handle,plan_name,enabled,accepted
2 from
dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------
------------------------------ --- ---
SQL_00d2fc9829b31338 SQL_PLAN_01nrwm0nv64tsb2385278
NO YES
The sql execution plan for this statement is no longer constrained, the
data in this table will change and at some point, we get new plans. For the
purpose of this example let’s say we got a unique index on object_id column,
then plan changes like this:
demo@ORA12C>
select * from table( dbms_xplan.display_cursor );
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3v77781b798nk, child number 0
-------------------------------------
select count(
distinct status ) from t where object_id = 42
Plan hash
value: 1391718225
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 2 (100)|
| 1 |
SORT AGGREGATE
| | 1 |
5 | |
| 2 |
VIEW |
VW_DAG_0 | 1 | 5 |
2 (0)|
| 3 |
SORT GROUP BY NOSORT | |
1 | 12 | 2
(0)|
| 4 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 12 |
2 (0)|
|* 5 |
INDEX UNIQUE SCAN | T_IDX |
1 | | 1
(0)|
--------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"=42)
Our sql plan baselines is disabled, so the optimizer is not constrained.
It is free to use its new plan. There is no “Notes” section either, so we can
be sure that we are not using the sql plan baselines. At this point everything
looks right: the sql plan baselines is disabled so the execution plan is not
constrained in any way.
If we have taken a look at the sql plan baseline the moment after the
new “index plan” was chosen by the optimizer, this is what you would have seen.
demo@ORA12C>
select sql_handle,plan_name,enabled,accepted
2 from
dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------
------------------------------ --- ---
SQL_00d2fc9829b31338 SQL_PLAN_01nrwm0nv64ts6ccbdc46 YES
NO
SQL_00d2fc9829b31338 SQL_PLAN_01nrwm0nv64tsb2385278
NO YES
What happened? We got two sql plan baselines for the same statement.
Bear in mind that when you disable a sql plan baseline we disable an
individual sql plan baseline and not the ability of a particular sql statement
to acquire new plan baselines. When the optimizer sees a new sql execution plan
for a sql statement, it will create a new sql plan baseline if a sql plan
baseline already exists for the statement. It does even if the auto capture is
disabled (optimizer_capture_sql_plan_baselines=false) and even if the existing
sql plan baseline associated with the statement are disabled.
The new execution plan is enabled, but it has not yet verified and
accepted by the sql plan management evolution, so we see ACCEPTED = NO. At this
point our sql statement will not use either of our sql plan baselines and there
is no “notes” section show in the plan.
demo@ORA12C>
explain plan for
2
select count( distinct status )
3 from
t
4
where object_id = 42;
Explained.
demo@ORA12C>
@xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash
value: 1391718225
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 5 | 2
(0)|
| 1 |
SORT AGGREGATE
| | 1 |
5 | |
| 2 |
VIEW |
VW_DAG_0 | 1 | 5 |
2 (0)|
| 3 |
SORT GROUP BY NOSORT | |
1 | 12 | 2
(0)|
| 4 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 12 |
2 (0)|
|* 5 |
INDEX UNIQUE SCAN |
T_IDX | 1 |
| 1 (0)|
--------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"=42)
17 rows
selected.
If the auto evolution task is enabled in your database (which is the
default in Oracle 12c) or if you run evolution manually, then the sql plan
baseline is subjected to verification and evolution because it is not disabled.
When a sql plan baseline is verified, evolution might accept it. This is what
we will see after the evolution.
demo@ORA12C>
select sql_handle,plan_name,enabled,accepted
2 from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------
------------------------------ --- ---
SQL_00d2fc9829b31338 SQL_PLAN_01nrwm0nv64ts6ccbdc46 YES
YES
SQL_00d2fc9829b31338 SQL_PLAN_01nrwm0nv64tsb2385278
NO YES
We now have a sql plan baseline that is enabled and accepted. The first
generated sql plan baseline remains disabled, but the optimizer will be able to
use the new sql plan baselines.
demo@ORA12C>
explain plan for
2
select count( distinct status )
3 from
t
4
where object_id = 42;
Explained.
demo@ORA12C>
@xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:
1391718225
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 5 | 2
(0)|
| 1 |
SORT AGGREGATE
| | 1 |
5 | |
| 2 |
VIEW |
VW_DAG_0 | 1 | 5 |
2 (0)|
| 3 |
SORT GROUP BY NOSORT | |
1 | 12 | 2
(0)|
| 4 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 12 |
2 (0)|
|* 5 |
INDEX UNIQUE SCAN |
T_IDX | 1 |
| 1 (0)|
--------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"=42)
Note
-----
- SQL plan baseline
"SQL_PLAN_01nrwm0nv64ts6ccbdc46" used for this statement
21 rows
selected.
In summary
·
When you disable a sql plan baseline, you
disable an individual sql plan baseline but not the mechanism of plan capture
and evolution at statement level.
·
If the goal is to disable the sql plan baseline
for a statement, then drop that plan baseline using
DBMS_SPM.DROP_SQL_PLAN_BASELINE.
·
To keep the “backup copy” then use
DBMS_SPM.PACK_STGTAB_BASELINE to pack the relevant baseline into a staging table
before dropping it, however we can always put it back using
DBMS_SPM.UNPACK_STGTAB_BASELINE.
No comments:
Post a Comment