Same plan hash value different partition range

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

5 Responses to Same plan hash value different partition range

  1. Access and filter predicates have no effect on plan_hash_value generation, so partition_start and partition_stop haven’t also.

    • Bobby says:

      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.

        • Bobby says:

          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.

  2. Pingback: Different plan_hash_value same plan | Bobby Durrett's DBA Blog

Leave a Reply to Bobby Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.