Sometimes simple question turns out to be harder. “Can we
see if a particular SQL execution in AWR used a baseline plan? “
Initial thoughts would be
Does dbms_xplan.display_awr tell us?
Apprantly not. See below.
Does DBA_HIST_SQLSTAT tell us? – No there is SQL_PROFILE column,
but no sql_plan_baseline column.
Does DBA_HIST_SQL_PLAN.OTHER_XML tell us? – No, it get notes
about cardinality feedback and dynamic sampling, but not sqlplan baselines.
The usual setup.
rajesh@ORA11G> create table t as select * from all_objects;
Table created.
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> select count(*) from t where owner ='SYS';
COUNT(*)
----------
37285
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 9cjaxv5d7rg8m,
child number 0
-------------------------------------
select count(*) from t where owner ='SYS'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 631 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 41685 |
692K| 631 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("OWNER"='SYS')
Note
-----
- dynamic sampling used
for this statement (level=2)
23 rows selected.
Now, let us baseline this plan.
rajesh@ORA11G> exec dbms_output.put_line(
dbms_spm.load_plans_from_cursor_cache('9cjaxv5d7rg8m'));
PL/SQL procedure successfully completed.
rajesh@ORA11G> select count(*) from t where owner ='SYS';
COUNT(*)
----------
37285
1 row selected.
rajesh@ORA11G> select count(*) from t where owner ='SYS';
COUNT(*)
----------
37285
1 row selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 9cjaxv5d7rg8m,
child number 0
-------------------------------------
select count(*) from t where owner ='SYS'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 631 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| T | 41685 |
692K| 631 (2)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
filter("OWNER"='SYS')
Note
-----
- dynamic sampling used
for this statement (level=2)
- SQL plan
baseline SQL_PLAN_g2dvxc950ykh83fdbb376 used for this statement
24 rows selected.
rajesh@ORA11G> select
sql_id,to_char(exact_matching_signature) sig,
2 sql_plan_baseline,plan_hash_value
3 from v$sql
4 where sql_id ='9cjaxv5d7rg8m';
SQL_ID SIG SQL_PLAN_BASELINE PLAN_HASH_VALUE
------------- --------------------
------------------------------ ---------------
9cjaxv5d7rg8m 17381499147962501640
SQL_PLAN_g2dvxc950ykh83fdbb376
2966233522
1 row selected.
rajesh@ORA11G>
Now, let’s add this statement to AWR and flush the shared pool.
rajesh@ORA11G> exec
dbms_workload_repository.add_colored_sql(sql_id=>'9cjaxv5d7rg8m');
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
rajesh@ORA11G> alter system flush shared_pool;
System altered.
rajesh@ORA11G> select
sql_id,to_char(exact_matching_signature) sig,
2 sql_plan_baseline,plan_hash_value
3 from v$sql
4 where sql_id ='9cjaxv5d7rg8m';
no rows selected
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor(sql_id=>'9cjaxv5d7rg8m'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID: 9cjaxv5d7rg8m, child number: 0 cannot be found
2 rows selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_awr(sql_id=>'9cjaxv5d7rg8m'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 9cjaxv5d7rg8m
--------------------
select count(*) from t where owner ='SYS'
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
| | 631 (100)| |
| 1 | SORT AGGREGATE |
| 1 | 17 | | |
| 2 | TABLE ACCESS FULL| T | 41685 |
692K| 631 (2)| 00:00:04 |
---------------------------------------------------------------------------
Note
-----
- dynamic
sampling used for this statement (level=2)
18 rows selected.
rajesh@ORA11G>
"Can we see if a particular SQL execution in AWR used a baselined
plan?"
- Get the exact_matching_signature from the sql text
- Get the phv2 out of DBA_HIST_SQL_PLAN.OTHER_XML
- Match that phv2 with planid which is not exposed in DBA_SQL_PLAN_BASELINES but is in the underlying SYS.SQLOBJ$ tables
rajesh@ORA11G> with subq_mysql as
2 ( select sql_id,
3
to_char(dbms_sqltune.sqltext_to_signature(sql_text)) sig
4 from dba_hist_sqltext
5 where sql_id ='9cjaxv5d7rg8m' ),
6 subq_mybaselines as (
7 select b.signature,
8 b.plan_name,
9 b.accepted,
10 b.created,
11 o.plan_id
12 from subq_mysql s,
13 dba_sql_plan_baselines b,
14 sys.sqlobj$ o
15 where s.sig = b.signature
16 and b.signature = o.signature
17 and o.name = b.plan_name ) ,
18 subq_myawrs as
19 (
20 select sn.snap_id,
21 to_char(sn.end_interval_time,'DD-MON-YYYY
hh24:mi') dt,
22 st.sql_id,
st.plan_hash_value,t.phv2,ms.sig
23 from dba_hist_snapshot sn,
24 dba_hist_sql_plan sp,
25 dba_hist_sqlstat st,
26 subq_mysql ms,
27 xmltable( 'for $i in /other_xml/info
28 where $i/@type eq
"plan_hash_2"
29 return $i'
30 passing xmltype(sp.other_xml)
31 columns phv2 number path '/') t
32 where st.sql_id = ms.sql_id
33 and sn.snap_id = st.snap_id
34 and sn.instance_number =
st.instance_number
35 and sp.sql_id = st.sql_id
36 and sp.plan_hash_value =
st.plan_hash_value
37 and sp.other_xml is not null )
38 select awr.*,
39 decode( (select b2.accepted
40 from subq_mybaselines b2
41 where b2.signature = awr.sig
42 and b2.accepted ='YES'
43 and rownum = 1 ),NULL,'NO','YES') as
baselines_exists,
44 decode(b.plan_id,NULL,'N','Y') as
is_baselined_plan,
45 to_char(b.created,'DD-MON-YYYY hh24:mi')
as when_baseline_created
46 from subq_myawrs awr,
47 subq_mybaselines b
48 where awr.sig = b.signature(+)
49 and awr.phv2 = b.plan_id (+)
50 /
SNAP_ID DT SQL_ID PLAN_HASH_VALUE PHV2 SIG BAS I WHEN_BASELINE_CRE
---------- ----------------- ------------- ---------------
---------- ---------------------- --- - -----------------
1064 07-NOV-2015
15:36 9cjaxv5d7rg8m 2966233522
1071362934 17381499147962501640 YES Y
07-NOV-2015 15:34
1 row selected.
rajesh@ORA11G>
