This discussion is archived
6 Replies Latest reply: Feb 18, 2013 7:08 AM by 933257 RSS

Different Plans for same query causing issue.

933257 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points