Has my Plan changed or not?
We have learned from Kerry Osborne’s recent post on SQL Gone Bad – But Plan Not Changed?, and a prior post from Randolf Geist on PLAN_HASH_VALUE – How equal (and stable?) are your execution plans – part 1, that having the same Plan Hash Value (PHV) it is not guarantee that we actually have the same Execution Plan if you were to consider Access and Filter Predicates for Plan Operations. So, what does it mean? It means that if you have inconsistent performance out of the same Execution Plan (same PHV) it is not enough to just check if you have or not the same PHV. You need to be aware of the Predicates as well. Of course there are other possible explanations for having inconsistent performance out of the same PHV, like skewed data combined with the use of binds, but that is another story.
Same Plan but different PHV
The opposite to “Same PHV but different Plan” is also possible. So, after we understood we can have the same PHV but not quite the same Plan if we include in the compare the Predicates, the question became: Can we also have cases having the same Plan (including Predicates) but get a different PHV? And the answer is YES.
Discard the keyword STORAGE, which we can consider not strong enough to determine a Plan Operation is different. Of course this is questionable, but as of today the PHV is blind to the STORAGE keyword, so a Plan with or without this keyword would render the same PHV.
What about system-generated names like ‘SYS_TEMP%’, ‘index$_join$_%’ and ‘VW_ST%’? In all these 3 cases the PHV will be different but the Plan is actually the same, with the exception of the system-generated names. So, if you just look at the PHV and see that is different then it is also possible that actually you have the same Plan.
What if in the case of having the same index name, the set of columns or their order is different? In these cases you may look at the PHV and see the same value, and indeed it is the same Plan, but if the columns on a referenced index have changed, is it really the same Plan? In my opinion it is not! You could be spinning on an issue where you have same Plan, different Performance, but an Index changed its columns.
SQLT to the rescue
Since we have the two cases: “Same PHV but different Plan” and “Same Plan but different PHV”, reviewing and ruling out these two possible cases on a complex Execution Plan can be cumbersome and time consuming. That is WHY SQLT incorporated the concept of SQLT Plan Hash Value (SQLT_PHV) since version 11.4.0.5 (from May 20, 2010). First came SQLT_PHV1, then SQLT_PHV2 (on SQLT 11.4.2.4 on February 18, 2011). So we have now PHV, SQLT_PHV1 and SQLT_PHV2, as you can see below.
As you can see in Table foot note: SQLT_PHV1 considers id, parent_id, operation, options, index_columns and object_name. SQLT PHV2 includes also access and filter predicates. So when comparing Plans the values of PHV, SQLT_PHV1 and SQLT_PHV2 can give you a better idea if your Plan is actually the same or not. SQLT COMPARE also uses the 3 values to determine if your Plan is the same or not, and in cases like the PHV is the same but a Predicate on a Plan Operation is different, it highlights in red the specific Plan Operation that has a different Predicate. Pretty cool right? I guess I will have to blog about SQLT COMPARE some time soon then…
Oh, be also aware that AWR does not store Plan Predicates, so if your Plan only exists on AWR you may be blind to these Predicates, but if the EXPLAIN PLAN FOR renders the same Plan as seen in lines 4 and 5 above, then you can see the predicates for 657302870 out the “XPL” Plan instead of “AWR”. A little trick that becomes handy…
Conclusion
When it comes to Execution Plans and their Plan Hash Value, it is possible that two Plans with same PHV are actually different if you consider the Predicates, and also possible you get a different PHV even if the Plan is the same if your Plan has system-generated names. So, during your analysis just looking at the PHV to determine if two Plans are the same or not is not enough. If you are already using SQLT, pay attention to the “Execution Plans” section, and to the SQLT PHV1 and PHV2 columns.
Carlos Sierra's Tools and Tips 
For the differences in system-generated temp table names, I know these are accounted for when using the v$sql_plan.other_xml.plan_hash_2 attribute as mentioned here:
http://orastory.wordpress.com/2012/07/12/plan_hash_value-and-internal-temporary-table-names/
As plan_hash_2 is what baselines use, this means that baselines work happy with materialized CTEs.
I’ve not checked the PHV2 is in the other examples you mention.
Dom Brooks
June 9, 2013 at 11:38 am
Good to know. Thanks. Please post what you find. The point of this blog is that observing the PHV is not enough in corner cases like the ones I mentioned. Cheers!
Carlos Sierra
June 9, 2013 at 3:22 pm
Hi Dom,
I just did a quick test and unfortunately the value stored in plan_hash_2 does not appear to include predicates in it’s calculation. Baselines don’t do anything to control how predicates are ordered either in 11g. 12c has the potential to improve that situation, but I’m not supposed to talk about that yet. 🙂 It’s cool that Baselines know about system generated names by the way. I hadn’t run across that before.
pieboy13
June 9, 2013 at 8:51 pm
Dang! I was going to do that. You should definitely do a post on SQL Compare!
Kerry Osborne
June 9, 2013 at 8:43 pm
will do! 🙂
Carlos Sierra
June 10, 2013 at 6:02 am
Hi Carlos,
Yesterday I produced the following situation:
select sql_id ,child_number ,force_matching_signature ,rows_processed ,plan_hash_value from v$sql where sql_text like '%MAX(e2.hiredate)%' and sql_text not like '%v$sql%'; SQL_ID CHILD_NUMBER FORCE_MATCHING_SIGNATURE ROWS_PROCESSED PLAN_HASH_VALUE ------------- ------------ ------------------------ -------------- --------------- ba39fv7txcsbk 0 6256287308517838235 4 2339135578 b2kggnvz02ctk 0 1563627505656661161 3 2339135578That is, two different sql_id, two different force_matching_signature, same plan_hash_value producing two different numbers of rows processed.
As I started investigating SQLT, I thought it is worth to see what execution plan SQLT will show.
The first interesting point is that sqltxtract for both sql_id show, as expected, the same plan hash value (2339135578), the same plan_hash_value1 (80535) and two different plan_hash_value2. This is very cool and extremely correct as far as the difference is located within the predicate part.
However, the difference in the predicate part is not shown into the main report. The two executions plans seems to be identical from the execution plan point of view.
I am going to blog about that. If you want I can send you the sqltxtract for both sql_id
Best regards
Mohamed Houri
hourim
June 17, 2013 at 4:49 am
Mohamed,
If you execute SQLT COMPARE you should see the difference in predicates (if any). You can also look at the SQLT PHV1 and 2 out of the Execution Plans section on MAIN. Please send me both SQLT files.
Thanks — Carlos
Carlos Sierra
June 17, 2013 at 5:29 am
[…] this context of plan hash value inspection, Carlos sierra from the Oracle support published a blog article showing that his sqltxplain tool has the ability to consider execution plan using not only the plan […]
Different sql id, different force matching signature, different rows processed with the same plan hash value | Mohamed Houri’s Oracle Notes
June 19, 2013 at 5:07 am
[…] Has my Plan changed or not? […]
Three Great Things You Didn't Know About SQLT - %%SQLTXPLAIN%%
June 29, 2013 at 10:14 am
Hi Carlos,
First of all, congrants for made the DBA day’s more cool with this tool…. 🙂
I don’t know if here is the rigth place but I have doubt about the information in the “Observations ” section.
In TYPE = BINDS , NAME = _OPTIM_PEEK_USER_BINDS , Observation = Child 4 on Plan 1697547450 “is missing Peeked Binds”…
What the “is missing Peeked Binds” really means here ? A cursor that was parsed and executed without any bind ? Is it possible ?
Thank you very much.
Dab
June 1, 2016 at 5:07 pm
Can you send me a screenshot? carlos.sierra.usa@gmail.com
Carlos Sierra
June 1, 2016 at 6:05 pm
Hi Carlos,
I just sent to you… 🙂
I’m very thankful for your answer here…
Thank you very much!!!!!
Dab
June 1, 2016 at 6:23 pm