I mentioned this same effect in an earlier post about SQL profiles: link
I get a different plan_hash_value values for a query each time I run an explain plan or run the query. I see this in queries whose plan includes a system generated temporary segment like this:
| 1 | TEMP TABLE TRANSFORMATION | | ... | 72 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D668C_764DD84C |
For some reason the system generated temporary table name gets included in the plan_hash_value calculation. This makes plan_hash_value a less than perfect way to compare two plans to see if they are the same.
Last week I was using my testselect package to test the effect of applying a patch to fix bug 20061582. I used testselect to grab 1160 select statements from production and got their plans with and without the patch applied on a development database. I didn’t expect many if any plans to change based on what the patch does. Surprisingly, 115 out of the 1160 select statements had a changed plan, but all the ones I looked at had the system generated temporary table names in their plan.
Now, I am going to take the queries that have different plans with and without the patch and execute them both ways. I have a feeling that the plan differences are mainly due to system generated temp table names and their execution times will be the same with and without the patch.
I’ve run across other limitations of plan hash value as I mentioned in an earlier post: link
I’m still using plan_hash_value to compare plans but I have a list of things in my head that reminds me of cases where plan_hash_value fails to accurately compare two plans.
– Bobby
P.S. After posting this I realized that I didn’t know how many of the 115 select statements with plans that differed with and without the patch had system generated temp tables. Now I know. 114 of the 115 have the string “TEMP TABLE TRANSFORMATION” in their plans. So, really, there is only one select statement for which the patch may have actually changed its plan.
P.P.S. I reapplied the patch and verified that the one sql_id didn’t really change plans with the patch. So, that means all the plan changes were due to the system generated name. Also, all the executions times were the same except for one query that took 50 seconds to parse without the patch and 0 with the patch. So, one of the queries with the system generated temp table name happened to benefit from the patch. Very cool!
P.P.P.S This was all done on an 11.2.0.4 Exadata system.
Fortunately SQL Plan Baselines use phv2 as the plan enforcement mechanism which takes into account these differing names for the internal temp table.
Dom,
Thanks for your participation in the blog. I did a quick search on phv2 and it looks interesting. We have been using plan_hash_value because it is so visible in Oracle’s tools such as the output from dbms_xplan and the dba_hist_sqlstat view. It’s easy to use but flawed. I wonder how the phv2 values deal with predicates and partitions – things that plan_hash_value doesn’t encode?
– Bobby
AFAIK, phv2 does not deal with difference in predicates etc.
Thanks Dom. I appreciate these comments because they are making me think about how I might improve my package that compares plans of select statements under varying conditions. Maybe I could do some sort of compare of the text of the plans instead of trying to use a value like phv. It might take a long time to compare the text but there really isn’t a hurry in this situation. It could handle the temp table names in some way that is similar to what the other person who commented on this post said. It’s a possible future enhancement. 🙂
Bobby,
I think this is fixed in 11.2.0.3 ie the plan_hash is calculated without the SYS_TEMP_… tables and I get the same plan_hash regargless of the SYS_TEMP_.. table name.
Previous to this I wrote the following to caculate “my_hash” which factored out the references to SYS_TEMP_.. tables which seemed to work for me.
, ( — Remove spaces, then hash the plan line which is then summed up
SELECT Sum(Ora_Hash(Replace(
— Replace any temp table references with SYS_TEMP_TABLE
Regexp_Replace(plan_table_output, ‘SYS_TEMP_(.*)\w’ ,’SYS_TEMP_TABLE’)
,’ ‘,”))
) hash_val
FROM Table(DBMS_XPLAN.Display_AWR(sql_id,plan_hash_value,null,’BASIC +PARTITION’))
— Only plan lines
WHERE plan_table_output Like ‘|%|%’
) AS my_hash
Thank you for your comment.
I neglected to indicate in my post that I ran my test on 11.2.0.4 Exadata. I’m pretty sure the behavior is the same in 11.2.0.3, but I don’t have an easy way to test it right now.
I’ve thought about writing my own encoding of a plan that includes the predicates and partitions but not replacing the temp table names. That is a good idea. As I mentioned in the comment the reason we use plan_hash_value is that it is so widely used in the tools Oracle provides. So, it is a trade off of making the best of the commonly available value versus making my own that is more suited for plan comparison.
Which is easier to explain to other people? Do I recommend that we use plan_hash_value which is in such common use but realize that it has limitations? It’s hard to explain the limitations of plan_hash_value to others but it isn’t hard to just point out how it shows up in various tools. I lean towards using plan_hash_value without explaining its flaws, but then discussing its weaknesses as they appear in people’s experience.
But, I’m still learning and there probably are better ways to go.
– Bobby