Starting from Oracle 12cR2 it is now possible to load a SPM plan
baseline from AWR historical execution plans. Prior to that only the plans from
cursor cache can be loaded into baselines.
Let’s say I need to configure SPM for the sqlid 94dwfa8yd87kw with
plan hash value as 3321871023 with its plan available from AWR repository.
demo@ORA12C>
select count(*) from dba_sql_plan_baselines;
COUNT(*)
----------
0
demo@ORA12C>
select sql_text from dba_hist_sqltext where sql_id ='94dwfa8yd87kw';
SQL_TEXT
--------------------------------------------------------------------------------
select
count(*) from t2
demo@ORA12C>
select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id
='94dwfa8yd87kw';
SNAP_ID SQL_ID PLAN_HASH_VALUE
----------
------------- ---------------
4718 94dwfa8yd87kw 3321871023
With the above details all I need to invoke LOAD_PLANS_FROM_AWR
function (new in 12.2 database) inside the DBMS_SPM package.
demo@ORA12C>
variable x number
demo@ORA12C>
begin
2 :x := dbms_spm.load_plans_from_awr(
begin_snap=>4717,end_snap=>4718,
3
basic_filter=>q'# sql_id='94dwfa8yd87kw' and plan_hash_value='3321871023' #'
);
4 end;
5 /
PL/SQL
procedure successfully completed.
demo@ORA12C>
print x
X
----------
1
Post that we can verify the details and plans from SMB.
demo@ORA12C>
select count(*) from dba_sql_plan_baselines;
COUNT(*)
----------
1
demo@ORA12C>
select t2.*
2 from dba_sql_plan_baselines t1,
3 table(
dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name) ) t2 ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL
handle: SQL_2064bcd93885dd48
SQL
text: select count(*) from t2
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan
name:
SQL_PLAN_20t5wv4w8bra81c6cf506
Plan id: 476902662
Enabled:
YES Fixed: NO Accepted:
YES Origin: MANUAL-LOAD-FROM-AWR
Plan
rows: From dictionary
--------------------------------------------------------------------------------
Plan
hash value: 3321871023
-------------------------------------------------------------------
|
Id | Operation |
Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
|
0 | SELECT STATEMENT |
| | 154
(100)| |
|
1 | SORT AGGREGATE |
| 1 | 0
(0)| |
|
2 | TABLE ACCESS FULL| T2 | 43196 |
154 (12)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
25
rows selected.
Finally we could quickly do an Explain plan over the sql to confirm if
the plan baselines were used – as highlighted in the notes section below.
demo@ORA12C>
set autotrace traceonly explain
demo@ORA12C>
select count(*) from t2;
Execution
Plan
----------------------------------------------------------
Plan
hash value: 3321871023
-------------------------------------------------------------------
|
Id | Operation |
Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
|
0 | SELECT STATEMENT |
| 1 | 152 (11)| 00:00:01 |
|
1 | SORT AGGREGATE |
| 1
|
| |
|
2 | TABLE ACCESS FULL| T2 | 36339 |
152 (11)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_20t5wv4w8bra81c6cf506" used for this
statement
No comments:
Post a Comment