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.
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 18.104.22.168 Exadata system.