1 2 Previous Next 16 Replies Latest reply on Nov 5, 2012 12:47 PM by User627471-Oracle

    Plan hash Value

    User627471-Oracle
      Hi,

      We have a job which runs series of sql queries and these sql queries are running with one plan on one day and other on the next day. For example on 1,3,5 dates it runs with one plan hash value and on 2,4,6 with other plan hash value.

      There is a performance degradation for one of these plans, please guide me on making these queries to use single plan.

      What factors contribute to change the PHV?

      Thanks
        • 1. Re: Plan hash Value
          sb92075
          user627471 wrote:
          Hi,

          We have a job which runs series of sql queries and these sql queries are running with one plan on one day and other on the next day. For example on 1,3,5 dates it runs with one plan hash value and on 2,4,6 with other plan hash value.

          There is a performance degradation for one of these plans, please guide me on making these queries to use single plan.

          What factors contribute to change the PHV?
          post both EXPLAIN PLAN
          Are Bind Variables used by SQL?
          • 2. Re: Plan hash Value
            rp0428
            >
            please guide me on making these queries to use single plan.
            >
            Only run them on the days when you get the good plan?
            >
            There is a performance degradation for one of these plans
            >
            I don't see anything in the plans you posted that looks different. Are you sure you posted the correct plans?
            • 3. Re: Plan hash Value
              User627471-Oracle
              There are 12-15 different queries in this script and each query has two PHV's, on every alternate day one particular plan is used.

              no bind variables are used.

              Thanks

              Edited by: user627471 on Jul 8, 2012 3:44 PM
              • 4. Re: Plan hash Value
                rp0428
                >
                There are 12-15 different queries in this script and each query has two PHV's, on every alternate day one particular plan is used.
                >
                Sorry - I still don't see anything in the plans you posted that looks different. Please be sure you have posted the correct plans.
                • 5. Re: Plan hash Value
                  User627471-Oracle
                  Hi,

                  Plan for sql running on 1,3,4,5,7

                  Plan hash value: 1788044516
                  
                  -------------------------------------------------------------------------------------------------
                  | Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------
                  -------------------------------------------------------------------------------------------------
                  |   0 | INSERT STATEMENT         |                      |       |       |  5327 (100)|          |
                  |   1 |  LOAD TABLE CONVENTIONAL |                      |       |       |            |          |
                  |   2 |   HASH JOIN              |                      |  3314 |   368K|  5327   (1)| 00:01:04 |
                  |   3 |    HASH JOIN             |                      |  3305 |   154K|  4130   (1)| 00:00:50 |
                  |   4 |     TABLE ACCESS FULL    | TEMP_TEST_TAB        |  3242 | 19452 |     3   (0)| 00:00:01 |
                  |   5 |     TABLE ACCESS FULL    | DLV_TAB              |   229K|  9409K|  4126   (1)| 00:00:50 |
                  |   6 |    TABLE ACCESS FULL     | DOCUMENT_TAB_REF     |   219K|    13M|  1196   (1)| 00:00:15 |
                  -------------------------------------------------------------------------------------------------
                  Same query on day 2, 4, 6,8 produces the following plan
                  Plan hash value: 2455904561
                  
                  ------------------------------------------------------------------------------------------------------
                  | Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------
                  ------------------------------------------------------------------------------------------------------
                  |   0 | INSERT STATEMENT              |                      |       |       |   345 (100)|          |
                  |   1 |  LOAD TABLE CONVENTIONAL      |                      |       |       |            |          |
                  |   2 |   NESTED LOOPS                |                      |       |       |            |          |
                  |   3 |    NESTED LOOPS               |                      |     1 |   120 |   345   (1)| 00:00:05 |
                  |   4 |     MERGE JOIN CARTESIAN      |                      |     1 |    79 |   344   (1)| 00:00:05 |
                  |   5 |      TABLE ACCESS FULL        | TEMP_TEST_TAB        |     1 |    13 |     2   (0)| 00:00:01 |
                  |   6 |      BUFFER SORT              |                      | 62091 |  4001K|   342   (1)| 00:00:05 |
                  |   7 |       TABLE ACCESS FULL       | DOCUMENT_TAB_REF     | 62091 |  4001K|   342   (1)| 00:00:05 |
                  |   8 |     INDEX UNIQUE SCAN         | DM_PK                |     1 |       |     0   (0)|          |
                  |   9 |    TABLE ACCESS BY INDEX ROWID| DLV_TAB              |     1 |    41 |     1   (0)| 00:00:01 |
                  
                  PLAN_TABLE_OUTPUT
                  ------------------------------------------------------------------------------------------------------------------------
                  ------------------------------------------------------------------------------------------------------
                  Thanks
                  clin.

                  Edited by: user627471 on Jul 21, 2012 6:43 AM

                  Edited by: user627471 on Jul 21, 2012 6:49 AM
                  • 6. Re: Plan hash Value
                    rp0428
                    Those plans don't look like they are even being run on the same server.

                    You didn't say but I'm assuming that the second plan is the good one?

                    There are at least two major differences in the plans.

                    First the estimated number of rows is MUCH lower for the second plan for all three tables. For TEMP_TEST_TAB the first plan expects 3242 rows while the second plan only expects 1 row. How many rows does this table have on each day? Does the number of rows change from day to day? Are stats being collected on this table? From the name it sounds like this could be a temporary table that gets loaded by the batch is that correct?

                    For THE DLV_TAB table the same comments and questions apply. First plan shows 229K rows while second plan only shows 1 row. Answer the same questions for this table that I ask above for the other table.

                    Those two row count differences are HUGE and could easily cause a different plan to be used.

                    For the DOCUMENT_TAB_REF the first plan show 219K rows but the second only 62091. Again, answer the same questions for this table.,

                    The other major difference is that the second plan is using the DM_PK index. Is this a primary key for one of the tables? Is this a permanent index or is it created by the batch?

                    Are stats being collected prior to the batch runs? Are the batch runs doing any TRUNCATE AND LOAD operations? Those operations can cause plans to change because if stats are not gathered after the load any existing stats may not be accurate.

                    Provide the DDL for the tables and indexes involved and the query that was used for the plans that you posted.
                    • 7. Re: Plan hash Value
                      Jonathan Lewis
                      user627471 wrote:

                      Plan for sql running on 1,3,4,5,7
                      |   4 |     TABLE ACCESS FULL    | TEMP_TEST_TAB        |  3242 | 19452 |     3   (0)| 00:00:01 |
                      Same query on day 2, 4, 6,8 produces the following plan
                      |   4 |     MERGE JOIN CARTESIAN      |                      |     1 |    79 |   344   (1)| 00:00:05 |
                      |   5 |      TABLE ACCESS FULL        | TEMP_TEST_TAB        |     1 |    13 |     2   (0)| 00:00:01 |
                      This looks like a stats problem - perhaps relating to when the stats are collected, particularly on TEMP_TEST_TAB, perhaps relating to query values moving out of the range of the known low and high values in the table. For example, given the "alternating" appearance of the problem it could be that you empty and reload the TEMP_TEST_TAB table every day, but only collect stats every other day - on the days you collect stats they reflect the actual content of the table, on the days you do't collect stats they give Oracle the impression that they query you're after isn't going to find any data.

                      I would look for code that loads data into TEMP_TEST_TAB and see what it does, when it does it, and what it does about stats collection.

                      Regards
                      Jonathan Lewis
                      • 8. Re: Plan hash Value
                        Girish Sharma
                        Sir,

                        What if I request to Oracle to please provide the current health of statistics with execution plan something like this (along with existing columns:
                        --------------------------------------------------------------------------------------------------------------
                        | Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |  Statistics
                        --------------------------------------------------------------------------------------------------------------
                        |   0 | INSERT STATEMENT         |                      |       |       |  5327 (100)|          |
                        |   1 |  LOAD TABLE CONVENTIONAL |                      |       |       |            |          |
                        |   2 |   HASH JOIN              |                      |  3314 |   368K|  5327   (1)| 00:01:04 |
                        |   3 |    HASH JOIN             |                      |  3305 |   154K|  4130   (1)| 00:00:50 |
                        |   4 |     TABLE ACCESS FULL    | TEMP_TEST_TAB        |  3242 | 19452 |     3   (0)| 00:00:01 |     50%
                        |   5 |     TABLE ACCESS FULL    | DLV_TAB              |   229K|  9409K|  4126   (1)| 00:00:50 |     75%
                        |   6 |    TABLE ACCESS FULL     | DOCUMENT_TAB_REF     |   219K|    13M|  1196   (1)| 00:00:15 |    100% 
                        --------------------------------------------------------------------------------------------------------------
                        or even in the footer of execution plan something like this :

                        Statistics Health N%.

                        then am I requesting to Oracle for something good or not ?

                        Regards
                        Girish Sharma
                        • 9. Re: Plan hash Value
                          User627471-Oracle
                          Thank you very much for your reply, the data in the temp table is truncated and loaded everyday from two other tables temp_test_tab and dm_tab. Auto stats collection is enabled and collected through dbms_scheduler much prior to the start of the job.
                          insert statement for temp_test_tab is

                          insert into temp_test_tab(col a) from test_tab tt, dm_tab dt where tt.cola=dt.cola;

                          The first plan provides good run and second plan runs for very long almost 6 hours compared to 2 mins on good run.

                          Thanks
                          Clin
                          • 10. Re: Plan hash Value
                            Jonathan Lewis
                            user627471 wrote:
                            Thank you very much for your reply, the data in the temp table is truncated and loaded everyday from two other tables temp_test_tab and dm_tab. Auto stats collection is enabled and collected through dbms_scheduler much prior to the start of the job.
                            It sounds from this descriptions as if the stats are created for the data for the previous day just before a complete new set of data is created - that's never a good idea, but it isn't necessarily the direct cause of your problem. However (a) given the nature of the data load, the program that does it should end with an explicit stats collection for that table; (b) if the problem is more subtle than bad stats it would help if you showed us the predicate section of the execution plan.

                            I'm not surprised that the performance varies from 2 minutes to 6 hours (this tends to suggest that it would be a cheap piece of insurance to do the right thing with stats anyway), the cardinality of 1 that results in the merge join cartesian is probably a long way off the reality of the result set.


                            Regards
                            Jonathan Lewis
                            • 11. Re: Plan hash Value
                              User627471-Oracle
                              Interestingly every two weeks the plans are getting changed, for example on 1,3,5,7,8,11,13,15 of a month we will have same set of plans for these queries and on 2,4,6,8,10,12,14 of a month they will have another set of plans.
                              Again after 15, the first set of plans will appear on 16,18,20,22,24,26,28,30 and second set of plans on 17,19,21,23,25,27,29.

                              I am little confused as to where should I start looking for a cause, please help and advice.

                              is it auto tuning/gather stats through dbms_scheduler is the culprit..? should I start look from there ..?

                              The database version is 11gR2.

                              Thanks
                              Clin.
                              • 12. Re: Plan hash Value
                                rp0428
                                >
                                I am little confused as to where should I start looking for a cause, please help and advice.
                                >
                                Jonathan Lewis, Oracle ACE Director and noted author, already gave you advice that you either didn't read or ignored.

                                Reread his last reply, do what he suggested and provide the information that was asked for. If you won't heed the advice, follow the suggestions or even comment on what an expert tells you it is hard to believe you would listen to anything else.
                                >
                                It sounds from this descriptions as if the stats are created for the data for the previous day just before a complete new set of data is created - that's never a good idea, but it isn't necessarily the direct cause of your problem. However (a) given the nature of the data load, the program that does it should end with an explicit stats collection for that table; (b) if the problem is more subtle than bad stats it would help if you showed us the predicate section of the execution plan.
                                >
                                Provide the information ask for about when and how the stats are collected. Run queries each day both before and after the jobs are run that show that stats for the tables involved. This history will show when the stats are actually changing.

                                Do some testing - for example on a day when you expect a poor plan will be used have the stats manually collected and see if that makes a difference.
                                • 13. Re: Plan hash Value
                                  User627471-Oracle
                                  Thank you very much for your reply, stats are collected on the tables which are involved in this job just after it is completed and again two hours before the nightly run of this job.

                                  I am posting the bad plan predicate which might help in analyzing my issue.


                                  INSERT INTO TEMP_DOC (COL1,COL2) SELECT
                                  DT.COL1,DTR.COL2 FROM DOCUMENT_TAB_REF DTR,
                                  DLV_TAB DT , TEMP_TEST_TAB TT WHERE DTR.COL1 =
                                  DT.COL1 AND DT.COL2 IN (1,2) AND DT.COL3 = TT.COL3
                                  
                                  
                                  Plan hash value: 2455904561
                                  
                                  ------------------------------------------------------------------------------------------------------
                                  | Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                                  ------------------------------------------------------------------------------------------------------
                                  |   0 | INSERT STATEMENT              |                      |       |       |  1336 (100)|          |
                                  |   1 |  LOAD TABLE CONVENTIONAL      |                      |       |       |            |          |
                                  |   2 |   NESTED LOOPS                |                      |       |       |            |          |
                                  |   3 |    NESTED LOOPS               |                      |     1 |   121 |  1336   (1)| 00:00:17 |
                                  |   4 |     MERGE JOIN CARTESIAN      |                      |     1 |    79 |  1334   (1)| 00:00:17 |
                                  |   5 |      TABLE ACCESS FULL        | TEMP_TEST_TAB          |     1 |    13 |     2   (0)| 00:00:01 |
                                  |   6 |      BUFFER SORT              |                      |   119K|  7692K|  1332   (1)| 00:00:16 |
                                  |   7 |       TABLE ACCESS FULL       | DOCUMENT_TAB_REF     |   119K|  7692K|  1332   (1)| 00:00:16 |
                                  |*  8 |     INDEX UNIQUE SCAN         | DM_PK                |     1 |       |     1   (0)| 00:00:01 |
                                  |*  9 |    TABLE ACCESS BY INDEX ROWID| DLV_TAB              |     1 |    42 |     2   (0)| 00:00:01 |
                                  ------------------------------------------------------------------------------------------------------
                                  
                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------
                                  
                                     8 - access("DTR"."COL1"="DT"."COL1")
                                     9 - filter((INTERNAL_FUNCTION("DT"."COL2") AND "DT"."COL3"="TT"."COL3"))
                                  Thanks
                                  Clin.
                                  • 14. Re: Plan hash Value
                                    User627471-Oracle
                                    Predicate from good plan :
                                    Plan hash value: 1788044516
                                    
                                    -------------------------------------------------------------------------------------------------
                                    | Id  | Operation                | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
                                    -------------------------------------------------------------------------------------------------
                                    |   0 | INSERT STATEMENT         |                      |       |       |  5747 (100)|          |
                                    |   1 |  LOAD TABLE CONVENTIONAL |                      |       |       |            |          |
                                    |*  2 |   HASH JOIN              |                      | 25587 |  2848K|  5747   (1)| 00:01:09 |
                                    |*  3 |    HASH JOIN             |                      | 25587 |  1199K|  4414   (1)| 00:00:53 |
                                    |   4 |     TABLE ACCESS FULL    | TEMP_TEST_TAB     | 25318 |   148K|    13   (0)| 00:00:01 |
                                    |*  5 |     TABLE ACCESS FULL    | DLV_TAB             |   115K|  4752K|  4400   (1)| 00:00:53 |
                                    |   6 |    TABLE ACCESS FULL     | DOCUMENT_TAB_REF     |   116K|  7507K|  1332   (1)| 00:00:16 |
                                    -------------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       2 - access("DTR"."COL1"="DT"."COL1")
                                       3 - access("DT"."COL3"="TT"."COL3")
                                       5 - filter(("DT"."COL2"=1 OR "DT"."COL2"=2))
                                    Please help.

                                    Thanks
                                    Clin.

                                    Edited by: user627471 on Aug 23, 2012 10:37 AM
                                    1 2 Previous Next