6 Replies Latest reply: Feb 18, 2013 9:08 AM by 933257 RSS

    Different Plans for same query causing issue.

    933257
      Hi All,
      i am using Release 10.2.0.4.0 version of oracle.
                i am having below query for which i get two different plans at different point of time. All the
                values passed to the query are constant values. When using plan1 it completes within ~2 seconds, but when using second plan
                it completes within ~20 minutes. Could you please help me to understand the reason?
                
                query:
                -------
                 SELECT ihc.po 
                 FROM  ihc,
                        a,
                        r
                WHERE     a.c1 = 16812147745
                       AND a.m1 = 'app_10'
                       AND a.a1 = r.attachpk
                       AND ihc.u1 = UPPER (r.invnum)
                       AND ihc.c1 = 577371
                       AND ihc.i1 != 'REJ'
                       AND ihc.is1 = 0
                       AND ROWNUM <= 1;
                                      
      Plan 1:
                
      NOTE: Index 'IDX_IHCV_UINVNUM' is on column (c1,indate,u1).
                
      Elapsed: 00:00:01.23
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2143159871
      
      --------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      --------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                    |                          |     1 |    97 |    11   (0)| 00:00:01 |       |       |
      |*  1 |  COUNT STOPKEY                      |                          |       |       |            |          |       |       |
      |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| ihc                      |     1 |    29 |     1   (0)| 00:00:01 | ROWID | ROWID |
      |   3 |    NESTED LOOPS                     |                          |     1 |    97 |    11   (0)| 00:00:01 |       |       |
      |   4 |     NESTED LOOPS                    |                          |     8 |   544 |     3   (0)| 00:00:01 |       |       |
      |*  5 |      TABLE ACCESS BY INDEX ROWID    | a                        |     1 |    49 |     2   (0)| 00:00:01 |       |       |
      |*  6 |       INDEX RANGE SCAN              | IDX_A                    |     1 |       |     2   (0)| 00:00:01 |       |       |
      |   7 |      TABLE ACCESS BY INDEX ROWID    | r                        |     8 |   152 |     1   (0)| 00:00:01 |       |       |
      |*  8 |       INDEX RANGE SCAN              | IDX_R                    |     8 |       |     1   (0)| 00:00:01 |       |       |
      |*  9 |     INDEX RANGE SCAN                | IDX_IHCV_UINVNUM         |     1 |       |     1   (0)| 00:00:01 |       |       |
      --------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(1>=ROWNUM)
         2 - filter("IHC"."is1"=0 AND "IHC"."i1"<>'REJ')
         5 - filter("A"."m1"='app_10')
         6 - access("A"."c1"=16812147745)
         8 - access("A"."a1"="R"."ATTACHPK")
         9 - access("IHC"."c1"=577371 AND "IHC"."u1"=UPPER("R"."INVNUM"))
             filter("IHC"."u1"=UPPER("R"."INVNUM"))
      
      
      Statistics
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
              521  consistent gets
              521  physical reads
                0  redo size
              226  bytes sent via SQL*Net to client
              239  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)     
                
      Plan 2:
      
      NOTE: Index 'IDX_IHCV_UINVNUM' is on column (c1,indate,buyercompanyname).
      
      Elapsed: 00:05:10.57
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1747092609
      
      ---------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ---------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                     |                          |     1 |    97 |     5   (0)| 00:00:01 |       |       |
      |*  1 |  COUNT STOPKEY                       |                          |       |       |            |          |       |       |
      |*  2 |   HASH JOIN                          |                          |     1 |    97 |     5   (0)| 00:00:01 |       |       |
      |   3 |    TABLE ACCESS BY INDEX ROWID       | r                        |     8 |   152 |     1   (0)| 00:00:01 |       |       |
      |   4 |     NESTED LOOPS                     |                          |     8 |   544 |     3   (0)| 00:00:01 |       |       |
      |*  5 |      TABLE ACCESS BY INDEX ROWID     | a                        |     1 |    49 |     2   (0)| 00:00:01 |       |       |
      |*  6 |       INDEX RANGE SCAN               | IDX_A                    |     1 |       |     2   (0)| 00:00:01 |       |       |
      |*  7 |      INDEX RANGE SCAN                | IDX_R                    |     8 |       |     1   (0)| 00:00:01 |       |       |
      |*  8 |    TABLE ACCESS BY GLOBAL INDEX ROWID| ihc                      |   227 |  6583 |     2   (0)| 00:00:01 | ROWID | ROWID |
      |*  9 |     INDEX RANGE SCAN                 | IDX_IHCV_BUYNAME         |   456 |       |     1   (0)| 00:00:01 |       |       |
      ---------------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(1>=ROWNUM)
         2 - access("IHC"."u1"=UPPER("R"."INVNUM"))
         5 - filter("A"."m1"='app_10')
         6 - access("A"."c1"=16812147745)
         7 - access("A"."a1"="R"."ATTACHPK")
         8 - filter("IHC"."is1"=0 AND "IHC"."i1"<>'REJ')
         9 - access("IHC"."c1"=577371)
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
            70418  consistent gets
            68428  physical reads
                0  redo size
              226  bytes sent via SQL*Net to client
              239  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
        • 1. Re: Different Plans for same query causing issue.
          Mark D Powell
          Are the two plans for the same user or different users? If different users, do the users use any different session settings. For instance if the sort_area_size or hash_area_size is set larger for one user then that would make a hash join more likely. There are at least a dozen session level setable database parameters that effect the CBO plan. Differences in any of these might be an issue.

          View v$ses_optimizer_env might be useful for looking at the sessions if you can find both present at once.


          HTH -- Mark D Powell --
          • 2. Re: Different Plans for same query causing issue.
            L-MachineGun
            930254 wrote:
            Hi All,
            i am using Release 10.2.0.4.0 version of oracle.
                      i am having below query for which i get two different plans at different point of time. All the
                      values passed to the query are constant values. When using plan1 it completes within ~2 seconds, but when using second plan
                      it completes within ~20 minutes. Could you please help me to understand the reason?
                      
                      query:
                      -------
                       SELECT ihc.po 
                       FROM  ihc,
                              a,
                              r
                      WHERE     a.c1 = 16812147745
                             AND a.m1 = 'app_10'
                             AND a.a1 = r.attachpk
                             AND ihc.u1 = UPPER (r.invnum)
                             AND ihc.c1 = 577371
                             AND ihc.i1 != 'REJ'
                             AND ihc.is1 = 0
                             AND ROWNUM <= 1;
                                            
            . . .  E t c  . . .
                      
            Elapsed: 00:00:01.23
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2143159871
            
            --------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                           | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            --------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                    |                          |     1 |    97 |    11   (0)| 00:00:01 |       |       |
            |*  1 |  COUNT STOPKEY                      |                          |       |       |            |          |       |       |
            |*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| ihc                      |     1 |    29 |     1   (0)| 00:00:01 | ROWID | ROWID |
            |   3 |    NESTED LOOPS                     |                          |     1 |    97 |    11   (0)| 00:00:01 |       |       |
            |   4 |     NESTED LOOPS                    |                          |     8 |   544 |     3   (0)| 00:00:01 |       |       |
            |*  5 |      TABLE ACCESS BY INDEX ROWID    | a                        |     1 |    49 |     2   (0)| 00:00:01 |       |       |
            |*  6 |       INDEX RANGE SCAN              | IDX_A                    |     1 |       |     2   (0)| 00:00:01 |       |       |
            |   7 |      TABLE ACCESS BY INDEX ROWID    | r                        |     8 |   152 |     1   (0)| 00:00:01 |       |       |
            |*  8 |       INDEX RANGE SCAN              | IDX_R                    |     8 |       |     1   (0)| 00:00:01 |       |       |
            |*  9 |     INDEX RANGE SCAN                | IDX_IHCV_UINVNUM         |     1 |       |     1   (0)| 00:00:01 |       |       |
            --------------------------------------------------------------------------------------------------------------------------------
            
            . . .  E t c  . . .
            
            Plan 2:
            
            NOTE: Index 'IDX_IHCV_UINVNUM' is on column (c1,indate,buyercompanyname).
            
            Elapsed: 00:05:10.57
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 1747092609
            
            ---------------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                            | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ---------------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                     |                          |     1 |    97 |     5   (0)| 00:00:01 |       |       |
            |*  1 |  COUNT STOPKEY                       |                          |       |       |            |          |       |       |
            |*  2 |   HASH JOIN                          |                          |     1 |    97 |     5   (0)| 00:00:01 |       |       |
            |   3 |    TABLE ACCESS BY INDEX ROWID       | r                        |     8 |   152 |     1   (0)| 00:00:01 |       |       |
            |   4 |     NESTED LOOPS                     |                          |     8 |   544 |     3   (0)| 00:00:01 |       |       |
            |*  5 |      TABLE ACCESS BY INDEX ROWID     | a                        |     1 |    49 |     2   (0)| 00:00:01 |       |       |
            |*  6 |       INDEX RANGE SCAN               | IDX_A                    |     1 |       |     2   (0)| 00:00:01 |       |       |
            |*  7 |      INDEX RANGE SCAN                | IDX_R                    |     8 |       |     1   (0)| 00:00:01 |       |       |
            |*  8 |    TABLE ACCESS BY GLOBAL INDEX ROWID| ihc                      |   227 |  6583 |     2   (0)| 00:00:01 | ROWID | ROWID |
            |*  9 |     INDEX RANGE SCAN                 | IDX_IHCV_BUYNAME         |   456 |       |     1   (0)| 00:00:01 |       |       |
            ---------------------------------------------------------------------------------------------------------------------------------
            . . .  E t c  . . .
            1) It depends on the "Histograms", therefore the optimizer chooses different indexes:
            |* 9 | INDEX RANGE SCAN | IDX_IHCV_UINVNUM | 1 | | 1 (0)| 00:00:01 | | |
            Vs.
            |* 9 | INDEX RANGE SCAN | IDX_IHCV_BUYNAME | 456 | | 1 (0)| 00:00:01 | | |
            2) Check out the plan statistics :
            Plan 1 Statistics        Plan 2 Statistics   
            ----------------------   --------------------
            ...                      ...
                0  db block gets         0  db block gets
              521  consistent gets   70418  consistent gets
              521  physical reads    68428  physical reads
                0  redo size             0  redo size
            3) You can try using hint(s) or a profile to force a specific plan.
            :p
            • 3. Re: Different Plans for same query causing issue.
              moreajays
              Hi,

              Index selection by optimizer is totally based on table/index/column stats & instance based optimizer parameters
              To keep the Good plan to be picked up every time you execute this query, you may use Index hint or Run sql tuning advisory and accept the sql_profile

              Thanks,
              Ajay More
              http://www.moreajays.com
              • 4. Re: Different Plans for same query causing issue.
                933257
                Hi ,
                     below are the index details and i am expecting 'IDX_IHCV_UINVNUM' to be used in all situation.
                     
                     
                INDEX_NAME                                                   DISTINCT_KEYS        NUM_ROWS
                IDX_IHCV_BUYNAME(c1,idate,bcomp)                            1787182             74625370
                IDX_IHCV_UINVNUM(c1,idate,u1)                              70570080              70795370
                
                 also i am able to see the constant phenomena now.
                'Plan 1' which i have mentioned above is constantly getting used, when the indexes(IDX_IHCV_UINVNUM,IDX_IHCV_BUYNAME) are global index.
                     and 'plan 2' is getting used, when the indexes are locally partitioned indexes. What is the reason behind this?
                     
                So i tried executing below part of the query , with both the indexes locally partitioned. got below plan.
                -- it gives cardinality of 470, 
                
                SELECT /*+INDEX(ihc IDX_IHCV_UINVNUM)*/ihc.po 
                           FROM  ihc,
                                   r
                          WHERE ihc.u1 = UPPER (r.invnum)
                                 AND ihc.c1 = 577371
                                 AND ihc.i1 != 'REJ'
                                 AND ihc.is1 = 0
                                 AND ROWNUM <= 1;
                ----------------------------------------------------------------------------------------------------------------------------------------
                | Id  | Operation                             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                ----------------------------------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT                      |                                |     1 |    39 |    65   (2)| 00:00:01 |       |       |
                |*  1 |  COUNT STOPKEY                        |                                |       |       |         |             |       |       |
                |   2 |   NESTED LOOPS                        |                                |     2 |    78 |    65   (2)| 00:00:01 |       |       |
                |   3 |    PARTITION RANGE ALL                |                                |   224 |  6272 |    64   (2)| 00:00:01 |     1 |    61 |
                |   4 |     PARTITION HASH ALL                |                                |   224 |  6272 |    64   (2)| 00:00:01 |     1 |     8 |
                |*  5 |      TABLE ACCESS BY LOCAL INDEX ROWID| ihc                            |   224 |  6272 |    64   (2)| 00:00:01 |     1 |   488 |
                |*  6 |       INDEX RANGE SCAN                | IDX_IHCV_UINVNUM               |   470 |       |    63   (2)| 00:00:01 |     1 |   488 |
                |*  7 |    INDEX RANGE SCAN                   | IDX_REMDT                      |     2 |    22 |     1   (0)| 00:00:01 |       |       |
                ----------------------------------------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   1 - filter(1>=ROWNUM)
                   5 - filter("IHC"."ISTEST"=0 AND "IHC"."INVOICESTATUSCD"<>'REJ')
                   6 - access("IHC"."COMPANYPK"=577371)
                   7 - access("IHC"."UPPERINVOICENUM"=UPPER("INVNUM"))
                                 
                
                So why optimizer is not going for below access path(step 9) as its there in PLAN 1 rather (6 and 7) above?:
                
                9 - access("IHC"."c1"=577371 AND "IHC"."u1"=UPPER("R"."INVNUM"))
                       filter("IHC"."u1"=UPPER("R"."INVNUM"))
                     
                
                Is this issue really driven by Local/Global indexes?
                     
                Edited by: 930254 on Feb 18, 2013 5:05 AM
                • 5. Re: Different Plans for same query causing issue.
                  riedelme
                  Are you sure identical sql being exected? Step 9 in the predicates are different suggesting different where clauses

                  Had you been using bind variables I would have wondered about histograms too but the values you posted where hard-coded and assumed to be the same for both runs. What is your cursor sharing initialization/session parameter value?
                  • 6. Re: Different Plans for same query causing issue.
                    933257
                    My mistake, just edited the column names before posting it. but yes the the sqls are exactly identical.
                    c1 for - companypk and u1 for - UPPERINVOICENUM.

                    no use of bind variables here.