9 Replies Latest reply: Feb 18, 2014 7:01 AM by 887829 RSS

    Query working response time differ for different instance

    887829

      Hi,

       

      We are facing problem in query response takes more in Prod than Test instance.

      The parameters are set similar in higher side in Production as infra resource availability.

      Data volume is more or less same in test & production, even though the same query when run in test gives results in 3 seconda and for the same it takes 15 minutes in prod.

      temp size usage is more in prod.

      Cost is more in production than SIT  for the same query.

      Please guide which areas to look into this for the same.

       

      in test following is the explain plan:

       

      SELECT STATEMENT, GOAL = ALL_ROWS   50 1 1609

      COUNT STOPKEY    

        VIEW ORCL_QA  50 1 1609

         SORT ORDER BY STOPKEY   50 1 1622

          VIEW ORCL_QA  49 1 1622

           WINDOW SORT PUSHED RANK   49 1 1294

            VIEW ORCL_QA  48 1 1294

             WINDOW SORT   48 1 1307

              VIEW ORCL_QA  47 1 1307

               WINDOW BUFFER   47 1 265

                SORT GROUP BY   47 1 265

                 NESTED LOOPS    

                  NESTED LOOPS   46 1 265

                   NESTED LOOPS   44 1 253

                    HASH JOIN   43 1 203

                     NESTED LOOPS    

                      NESTED LOOPS   41 29 5046

                       NESTED LOOPS   15 1 140

                        NESTED LOOPS   6 1 59

                         TABLE ACCESS BY INDEX ROWID ORCL_QA W_HIERARCHY_D 2 1 42

                          BITMAP CONVERSION TO ROWIDS    

                           BITMAP AND    

                            BITMAP INDEX SINGLE VALUE ORCL_QA IDX_HIER3_CODE  

                            BITMAP INDEX SINGLE VALUE ORCL_QA IDX_HIER1_CODE_FNCTN  

                         TABLE ACCESS BY INDEX ROWID ORCL_QA W_GL_SEGMENT_D 6 1 17

                          BITMAP CONVERSION TO ROWIDS    

                           BITMAP INDEX SINGLE VALUE ORCL_QA W_GL_SEGMENT_D_M2  

                        TABLE ACCESS BY INDEX ROWID ORCL_QA W_GL_ACCOUNT_D 15 4 324

                         BITMAP CONVERSION TO ROWIDS    

                          BITMAP INDEX SINGLE VALUE ORCL_QA IDX_ACCOUNT_SEG2_CODE  

                       BITMAP CONVERSION TO ROWIDS    

                        BITMAP INDEX SINGLE VALUE ORCL_QA W_GL_BALANCE_F_F5  

                      TABLE ACCESS BY INDEX ROWID ORCL_QA W_GL_BALANCE_F 41 37 1258

                     TABLE ACCESS BY INDEX ROWID ORCL_QA W_LEDGER_D 2 1 29

                      BITMAP CONVERSION TO ROWIDS    

                       BITMAP INDEX SINGLE VALUE ORCL_QA W_LEDGER_D_M1  

                    TABLE ACCESS BY INDEX ROWID ORCL_QA W_MCAL_DAY_D 1 1 50

                     INDEX UNIQUE SCAN ORCL_QA W_MCAL_DAY_D_P1 0 1

       

      for PRoduction following is the explain plan:

      SELECT STATEMENT, GOAL = ALL_ROWS   177 1 1609

      COUNT STOPKEY    

        VIEW ORCL_PROD  177 1 1609

         SORT ORDER BY STOPKEY   177 1 1622

          VIEW ORCL_PROD  176 1 1622

           WINDOW SORT PUSHED RANK   176 1 1294

            VIEW ORCL_PROD  174 1 1294

             WINDOW SORT   174 1 1307

              VIEW ORCL_PROD  173 1 1307

               WINDOW BUFFER   173 1 264

                SORT GROUP BY   173 1 264

                 NESTED LOOPS    

                  NESTED LOOPS   173 1 264

                   NESTED LOOPS   76 2 460

                    MERGE JOIN CARTESIAN   74 1 218

                     NESTED LOOPS    

                      NESTED LOOPS   71 1 189

                       NESTED LOOPS   34 1 108

                        MERGE JOIN CARTESIAN   16 1 91

                         TABLE ACCESS BY INDEX ROWID ORCL_PROD W_MCAL_DAY_D 2 1 50

                          BITMAP CONVERSION TO ROWIDS    

                           BITMAP AND    

                            BITMAP INDEX SINGLE VALUE ORCL_PROD W_MCAL_DAY_D_M4  

                            BITMAP INDEX SINGLE VALUE ORCL_PROD W_MCAL_DAY_D_F46  

                         BUFFER SORT   14 19 779

                          TABLE ACCESS BY INDEX ROWID ORCL_PROD W_HIERARCHY_D 16 19 779

                           BITMAP CONVERSION TO ROWIDS    

                            BITMAP AND    

                             BITMAP INDEX SINGLE VALUE ORCL_PROD IDX_HIER1_CODE_FNCTN  

                             BITMAP INDEX SINGLE VALUE ORCL_PROD W_HIERARCHY_D_M2  

                             BITMAP INDEX SINGLE VALUE ORCL_PROD IDX_HIER3_CODE  

                        TABLE ACCESS BY INDEX ROWID ORCL_PROD W_GL_SEGMENT_D 34 1 17

                         BITMAP CONVERSION TO ROWIDS    

                          BITMAP INDEX SINGLE VALUE ORCL_PROD W_GL_SEGMENT_D_M2  

                       BITMAP CONVERSION TO ROWIDS    

                        BITMAP INDEX SINGLE VALUE ORCL_PROD IDX_ACCOUNT_SEG2_CODE  

                      TABLE ACCESS BY INDEX ROWID ORCL_PROD W_GL_ACCOUNT_D 71 4 324

                     BUFFER SORT   3 1 29

                      TABLE ACCESS FULL ORCL_PROD W_LEDGER_D 2 1 29

                    TABLE ACCESS BY INDEX ROWID ORCL_PROD W_SASCIA_LEDGER_D 2 2 24

                     INDEX RANGE SCAN ORCL_PROD IDX_USERNAME 1 30

                   BITMAP CONVERSION TO ROWIDS    

                    BITMAP INDEX SINGLE VALUE ORCL_PROD W_GL_BALANCE_F_F5  

                  TABLE ACCESS BY INDEX ROWID ORCL_PROD W_GL_BALANCE_F 173 1 34

                   INDEX RANGE SCAN ORCL_QA IDX_USERNAME 1 30

                  TABLE ACCESS BY INDEX ROWID ORCL_QA W_SASCIA_LEDGER_D 2 2 24

      Thanks

        • 1. Re: Query working response time differ for different instance
          sb92075

          887829 wrote:

           

          Hi,

           

          We are facing problem in query response takes more in Prod than Test instance.

          The parameters are set similar in higher side in Production as infra resource availability.

          Data volume is more or less same in test & production, even though the same query when run in test gives results in 3 seconda and for the same it takes 15 minutes in prod.

          temp size usage is more in prod.

          Cost is more in production than SIT  for the same query.

          Please guide which areas to look into this for the same.

           

          Regards,

          Dhanashree

           

          post SQL & EXPLAIN PLAN from both systems.

           

           

           

          HOW To Make TUNING request

          https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360003

          • 2. Re: Query working response time differ for different instance
            Martin Preiss

            without the query and the filter and access predicates it's a lot of guessing but it seems that in prod the optimizer starts with the access on W_MCAL_DAY_D (combining index information from W_MCAL_DAY_D_M4 and W_MCAL_DAY_D_F46) and assumes that this operation will return a very small resultset. As a consequence the optimizer joins this data with W_HIERARCHY_D using a cartesian join.

             

            I am quite sure that the cardinality estimate for the W_MCAL_DAY_D step is wrong and would take a look at the number of rows that you get for W_MCAL_DAY_D filtered with the given conditions. Another option would be the creation of a plan with rowsource statistics (gather_plan_statistics hint) to find out at which point the optimizer fails to predict the correct sizes of intermediate results. The next step would be to try to give the optimizer better information in prod (maybe by adding histograms).

            • 3. Re: Query working response time differ for different instance
              887829

              Hi,

               

              Thanks for the info. It's very big query to paste here.. so thought of giving explain plan. How to force the optmizer to predict correct size of intermediate result.

              Not sure how test instance is ableto derive the correct effective plan for execution.

              Is there any parameter or setting need to be done?

               

              Regards

              • 4. Re: Query working response time differ for different instance
                Martin Preiss

                the optimizer is a quite complex piece of software and small changes in estimated cardinalities may lead to a completely different plan - so there is no simple do-it-right parameter or setting. I would suggest that you create a plan with rowsource statistics:

                1. Execute your query with the hint /*+ gather_plan_statistics */
                2. after the execution get the plan using dbms_xplan: select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

                This should give you a plan that contains the cardinalities estimated by the optimizer (E-rows) and the actual cardinalities in the execution (A-rows). Big differences between both values are the most common reason for ineffective plans.

                • 5. Re: Query working response time differ for different instance
                  887829


                  Hi,

                   

                  Thanks for your inputs. We'll check the same.

                   

                  Is there any way to enforce to choose the best plan while execution.As there are multiple queries which are giving this problem and works fast in test instance.

                   

                  Regards

                  • 6. Re: Query working response time differ for different instance
                    JustinCave

                    The optimizer is, of course, attempting to choose the best plan.  If it was obvious what the best plan was, the optimizer's task would be much easier and much less time would be devoted to understanding and improving the optimizer.

                     

                    In the large majority of cases, when the optimizer chooses a poor plan, that is an indication that statistics on one or more objects involved in the query are incorrect or missing (which gets into a number of questions about how and when you run DBMS_STATS).  Plans can be poor because you've set initialization parameters incorrectly at the database or at the session level or because you happen to have a data distribution that the optimizer's calculations do not handle well but those are much less common than simple missing/ out-of-date statistics.

                     

                    Justin

                    • 7. Re: Query working response time differ for different instance
                      887829

                      Thanks for your response.

                       

                      For instance where the response time is slow , statistics are rebuilt recently  than test instance.For test the statistics are 2 days old.

                       

                      No session level parameters are set for both test & prod instance. Database level parameters are exactly matching except for memory related parameters which are higher side than test instance. Only the difference is

                       

                      in PROD

                      memory_max_target                    big integer 40G

                      memory_target                        big integer 40G

                      pga_aggregate_target                 big integer 5G

                      sga_target                           big integer 20G

                      sort_area_size                integer 1G

                       

                      hash_area_size           integer 1G

                       

                      in TEST

                      pga_aggregate_target                 big integer 5G

                      sga_target                           big integer 12G

                      sort_area_size                integer 65536

                       

                      hash_area_size           integer 131072

                       

                       

                       

                       

                      Regards,

                      • 8. Re: Query working response time differ for different instance
                        JustinCave

                        If the data volumes are close between environments, I'll put money on the fact that the statistics in the two environments are different.  Perhaps you have histograms in one environment and not another.  Perhaps you have some stale histograms in one environment.  Perhaps you gathered statistics differently across environments.

                         

                        Justin

                        • 9. Re: Query working response time differ for different instance
                          887829


                          Hi,

                           

                          Please let me know how to check histograms in environment.statistcs are rebuilt recently in the envirnment where the response is slow.

                          wheras for test it's 2 days old statistics.

                           

                          Regards