AWR: Was a baselined plan used?
February 5, 2014 8 Comments
Sometimes a simple question turns out to be harder than expected.
“Can we see if a particular SQL execution in AWR used a baselined plan?”
Initial thoughts might be:
Q: Does DBMS_XPLAN.DISPLAY_AWR tell us this?
A: Apparently not. See below. This question could also be rephrased as two other possible questions:
Q:Isn’t there a column on DBA_HIST_SQLSTAT which tell us this?
A: No. You’d think there should be. There is a SQL_PROFILE column. There isn’t a SQL_PLAN_BASELINE column.
There also isn’t an EXACT_MATCHING_SIGNATURE although there is a FORCE_MATCHING_SIGNATURE.
Q: Is it in DBA_HIST_SQL_PLAN.OTHER_XML?
A. No although this is where DBMS_XPLAN.DISPLAY_AWR gets it’s notes about cardinality feedback and dynamic sampling from.
First of all, Let’s see that it’s not shown in DBMS_XPLAN.DISPLAY_AWR.
Let’s get a sql statement baselined, in AWR and not in memory.
The usual setup
SQL> create table t1 2 (col1 number 3 ,col2 varchar2(50) 4 ,flag varchar2(2)); Table created. SQL> insert into t1 2 select rownum 3 , lpad('X',50,'X') 4 , case when rownum = 1 5 then 'Y1' 6 when rownum = 2 7 then 'Y2' 8 when mod(rownum,2) = 0 9 then 'N1' 10 else 'N2' 11 end 12 from dual 13* connect by rownum <= 100000 SQL> / 100000 rows created. SQL> commit; Commit complete.
Get a plan and put it in a baseline:
SQL> var n varchar2(2); SQL> exec :n := 'N1'; PL/SQL procedure successfully completed. SQL> select count(*), max(col2) from t1 where flag = :n; select * from table(dbms_xplan.display_cursor); COUNT(*) MAX(COL2) ---------- -------------------------------------------------- 49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 731b98a8u0knf, child number 0 ------------------------------------- select count(*), max(col2) from t1 where flag = :n Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 221 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | TABLE ACCESS FULL| T1 | 50254 | 1472K| 221 (1)| 00:00:03 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("FLAG"=:N) Note ----- - dynamic sampling used for this statement (level=2) SQL> declare 2 l_op pls_integer; 3 begin 4 l_op := 5 dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf'); 6* end; SQL> / PL/SQL procedure successfully completed.
Verify that DBMS_XPLAN.DISPLAY_CURSOR reports baseline usage:
SQL> select count(*), max(col2) from t1 where flag = :n; COUNT(*) MAX(COL2) ---------- -------------------------------------------------- 49999 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 731b98a8u0knf, child number 0 ------------------------------------- select count(*), max(col2) from t1 where flag = :n Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 221 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | |* 2 | TABLE ACCESS FULL| T1 | 50254 | 1472K| 221 (1)| 00:00:03 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("FLAG"=:N) Note ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
See that dynamic_sampling note most likely comes from OTHER_XML and Baseline note doesn’t, it comes from V$SQL:
SQL>select sql_id 2 , to_char(exact_matching_signature) sig 3 , plan_hash_value 4 , sql_plan_baseline 5 from v$sql 6* where sql_id = '731b98a8u0knf' SQL> / SQL_ID SIG PLAN_HASH_VALUE SQL_PLAN_BASELINE ------------- ---------------------------------------- --------------- ------------------------------ 731b98a8u0knf 1292784087274697613 3724264953 SQL_PLAN_13w748wknkcwd616acf47 SQL> set long 10000 SQL> select other_xml 2 from v$sql_plan 3 where sql_id = '731b98a8u0knf' 4* and other_xml is not null SQL> / OTHER_XML -------------------------------------------------------------------------------- <other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![C DATA["E668983_DBA"]]></info><info type="dynamic_sampling">2</info><info type="pl an_hash">3724264953</info><info type="plan_hash_2">1634389831</info><peeked_bind s><bind nam=":N" pos="1" dty="1" csi="178" frm="1" mxl="32">4e31</bind></peeked_ binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><! [CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDATA[DB_VERSION( '11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('query_rewrite_enabled' 'false')]] ></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]] ></hint><hint><![CDATA[FULL(@"SEL$1" "T1"@"SEL$1")]]></hint></outline_data></oth er_xml>
Now, let’s add statement to AWR, do an AWR snap and flush the SP:
SQL> exec dbms_workload_repository.add_colored_sql('731b98a8u0knf'); PL/SQL procedure successfully completed. SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. SQL> alter system flush shared_pool; System altered.
Now if we look back at the plan in memory, there’s nothing there.
SQL> select * from v$sql where sql_id = '731b98a8u0knf'; no rows selected SQL> select * from table(dbms_xplan.display_cursor('731b98a8u0knf')); PLAN_TABLE_OUTPUT ------------------------------------------------------------ SQL_ID: 731b98a8u0knf, child number: 0 cannot be found
But the SQL is in AWR and let’s see that DBMS_XPLAN.DISPLAY_AWR does not tells us that a baseline is used:
SQL> select * from table(dbms_xplan.display_awr('731b98a8u0knf')); PLAN_TABLE_OUTPUT --------------------------------------------------------------- SQL_ID 731b98a8u0knf -------------------- select count(*), max(col2) from t1 where flag = :n Plan hash value: 3724264953 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 221 (100)| | | 1 | SORT AGGREGATE | | 1 | 30 | | | | 2 | TABLE ACCESS FULL| T1 | 50238 | 1471K| 221 (1)| 00:00:03 | --------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)
But it will tell us if a sql profile is used because it can via DBA_HIST_SQLSTAT.SQL_PROFILE – I leave that exercise to the reader.
So, back to the original question:
“Can we see if a particular SQL execution in AWR used a baselined plan?”
I’ve never needed to do this but as an exercise we can infer this connection by the presence of a baselined plan with the same phv2 but it’s convoluted.
It may be that there are simpler ways but it seems to me that we need to:
- 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
SQL> with subq_mysql as 2 (select sql_id 3 , (select dbms_sqltune.sqltext_to_signature(ht.sql_text) 4 from dual) sig 5 from dba_hist_sqltext ht 6 where sql_id = '731b98a8u0knf') 7 , subq_baselines as 8 (select b.signature 9 , b.plan_name 10 , b.accepted 11 , b.created 12 , o.plan_id 13 from subq_mysql ms 14 , dba_sql_plan_baselines b 15 , sys.sqlobj$ o 16 where b.signature = ms.sig 17 and o.signature = b.signature 18 and o.name = b.plan_name) 19 , subq_awr_plans as 20 (select sn.snap_id 21 , to_char(sn.end_interval_time,'DD-MON-YYYY HH24:MI') dt 22 , hs.sql_id 23 , hs.plan_hash_value 24 , t.phv2 25 , ms.sig 26 from subq_mysql ms 27 , dba_hist_sqlstat hs 28 , dba_hist_snapshot sn 29 , dba_hist_sql_plan hp 30 , xmltable('for $i in /other_xml/info 31 where $i/@type eq "plan_hash_2" 32 return $i' 33 passing xmltype(hp.other_xml) 34 columns phv2 number path '/') t 35 where hs.sql_id = ms.sql_id 36 and sn.snap_id = hs.snap_id 37 and sn.instance_number = hs.instance_number 38 and hp.sql_id = hs.sql_id 39 and hp.plan_hash_value = hs.plan_hash_value 40 and hp.other_xml is not null) 41 select awr.* 42 , nvl((select max('Y') 43 from subq_baselines b 44 where b.signature = awr.sig 45 and b.accepted = 'YES'),'N') does_baseline_exist 46 , nvl2(b.plan_id,'Y','N') is_baselined_plan 47 , to_char(b.created,'DD-MON-YYYY HH24:MI') when_baseline_created 48 from subq_awr_plans awr 49 , subq_baselines b 50 where b.signature (+) = awr.sig 51 and b.plan_id (+) = awr.phv2 52* order by awr.snap_id SQL> / SNAP_ID DT SQL_ID PLAN_HASH_VALUE PHV2 SIG D I WHEN_BASELINE_CRE ---------- ----------------- ------------- --------------- ---------- ---------- - - ----------------- 8703 05-FEB-2014 15:18 731b98a8u0knf 3724264953 1634389831 1.2928E+18 Y Y 05-FEB-2014 15:08 SQL>
Surprising.
Unless, of course, I’ve overlooked something bleeding obvious.
Hi Dom,
I don’t clearly see what’s surprising here. For me it seems that plan_id and phv2 represent the same thing.
We can get the plan_id directly from the baselined plan
And then we can get the phv2 to see if it equals that plan_id (1634389831) or not
Which obviously is the same as the plan_id exposed via dbms_xplan.display_sql_plan_baseline above. Moreover this is what I have observed in the 10053 trace file for the same query
I am also investigating which optimizer parameter are used during the execution of a baselined sql query and here below is snapshot of a corresponding 10053 trace file:
Where you can see that PHV2 equals also the planId
So, can you please let me know what is surprising in your post?
By the way, the question that triggered your post is excellent. I have already been thinking about it but you have done it and thanks for that.
I don’t understand.
You say the question is good.
You say that I’ve answered the question.
Yet you say that you don’t get what is surprising.
What’s surprising?
1. It’s very easy to see whether a SQL profile was used via AWR – see DBA_HIST_SQLSTAT.SQL_PROFILE. But it’s completely different for a baseline.
2. Because of this DBMS_XPLAN can say whether a SQL Profile was used but not a baseline
2. Yes plan id and phv2 are the same – that is one of my points for the required mapping.
I see now what you meant by surprising even thought that I am not surprised to know that dbms_xplan.display_awr lacks to show that baseline Note because it already lacks to show the predicate part which is in my opinion more important than the baseline note.
Best regards
Mohamed Houri
Sure – a lack of predicates is more significant but that’s been known for years and years – problem goes back to 9i at least.
Think it’s fixed in 12c though. Might have to check later.
Unfortunately it seems that it is still not fixed
Mohamed Houri
Super, thanks.
Pingback: SPM baseline and historical execution plans | Mohamed Houri’s Oracle Notes
thanks a lot, this is exactly what I need to find what sql_id is linked to what sql plan baseline.