Today I saw two different plans with the same plan hash value but different numbers of partitions. So, the partition numbers must not factor into the plan hash value. So, you can’t count on the plan hash value to fully indicate whether two plans are the same.
I’ve taken the real plans and just modified the table and index names so I won’t expose our real names. But, otherwise this is the real thing:
10/30/2013 plan Plan hash value: 11498413 ---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | | | 4440 (100)| | | | | | | | 1 | MERGE | TABLE_ONE | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 21446 | 45M| 4440 (1)| 00:01:03 | | | Q1,00 | P->S | QC (RAND) | | 4 | VIEW | | | | | | | | Q1,00 | PCWP | | | 5 | NESTED LOOPS OUTER | | 21446 | 45M| 4440 (1)| 00:01:03 | | | Q1,00 | PCWP | | | 6 | PX PARTITION RANGE ALL | | 21446 | 22M| 1459 (1)| 00:00:21 | 1 | 156 | Q1,00 | PCWC | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE_TWO | 21446 | 22M| 1459 (1)| 00:00:21 | 1 | 156 | Q1,00 | PCWP | | | 8 | INDEX RANGE SCAN | INDEX_TABLE_TWO | 21593 | | 26 (0)| 00:00:01 | 1 | 156 | Q1,00 | PCWP | | | 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_ONE | 1 | 1145 | 0 (0)| | ROWID | ROWID | Q1,00 | PCWP | | | 10 | INDEX UNIQUE SCAN | PK_TABLE_ONE | 1 | | 0 (0)| | | | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------------------------------------------------------------- 11/11/2013 plan Plan hash value: 11498413 ---------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | | | 4845 (100)| | | | | | | | 1 | MERGE | TABLE_ONE | | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 21966 | 46M| 4845 (1)| 00:01:08 | | | Q1,00 | P->S | QC (RAND) | | 4 | VIEW | | | | | | | | Q1,00 | PCWP | | | 5 | NESTED LOOPS OUTER | | 21966 | 46M| 4845 (1)| 00:01:08 | | | Q1,00 | PCWP | | | 6 | PX PARTITION RANGE ALL | | 21966 | 22M| 1469 (1)| 00:00:21 | 1 | 208 | Q1,00 | PCWC | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE_TWO | 21966 | 22M| 1469 (1)| 00:00:21 | 1 | 208 | Q1,00 | PCWP | | | 8 | INDEX RANGE SCAN | INDEX_TABLE_TWO | 22638 | | 33 (0)| 00:00:01 | 1 | 208 | Q1,00 | PCWP | | | 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_ONE | 1 | 1151 | 0 (0)| | ROWID | ROWID | Q1,00 | PCWP | | | 10 | INDEX UNIQUE SCAN | PK_TABLE_ONE | 1 | | 0 (0)| | | | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------------------------------------------------
One of these queries was run on 10/30/2013 (the one with fewer partitions). The other was run yesterday and is still running. Looks like the range scan has a bunch more partitions worth of data today and that is why the query, despite the same plan hash value, is running longer.
The Pstop column has the high partition number so it looks like we had 156 partitions on 10/30 and 208 yesterday unless I’m missing something.
– Bobby
Access and filter predicates have no effect on plan_hash_value generation, so partition_start and partition_stop haven’t also.
Sayan,
Thanks for your comment. I think I need to get a better grasp on exactly what is and isn’t factored into the plan hash value because we are using it to compare query executions. I guess if you see the same query with the same plan hash value but drastically different resource usage that would give you a clue to look further at the plan itself.
– Bobby
Bobby,
simple example about predicates:
SQL> select sql_text from v$sql s where s.plan_hash_value=272002086;
SQL_TEXT
———————————————————————
select 1 from dual where dummy=’z’
select * from dual where dummy=2
select * from dual where dummy=’x’
SELECT * FROM dual
Predicates are very important and if we need to compare plans, we have to compare they not only with same sql_id(same query), but also we must take into account different bind values.
Sayan,
Thanks again for your input. I’ve had a lot of success just using the plan hash value to look for plan changes since it is a column in DBA_HIST_SQLSTAT. You can easily tell if the plan has changed for a given sql_id using the plan_hash_value column on that view. I’ve seen many cases where a plan changes in ways that change the plan_hash_value so I don’t think we should throw that method out. But, it is helpful to know the kinds of things that can cause the plan to be different even though the plan_hash_value is the same for a given sql_id.
– Bobby
p.s. I edited out the tags from your comment since they didn’t work. I’m not sure which tags the comments work with on WordPress blogs.
Pingback: Different plan_hash_value same plan | Bobby Durrett's DBA Blog