Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Post Same query Same database Different Explain Plans

    We have this interesting query which runs very fast or very slow when you change the search value passed in where clause. Explain plan cost is jumping up for the second value !!
    Same database and everything is same except the value passed to the query. In fact I am running both queries in same session.

    Any idea what would cause such difference ?

    Code:
    Query 1:
    
    Select ID, PERSON_ID
    From CONTACT
    Where PERSON_ID = 'XXX'
    
    Query 2: Same as above just the search value for PERSON_ID changes.
    
    Select ID, PERSON_ID
    From CONTACT
    Where PERSON_ID = 'YYY'
    TABLE Details. It just has 3 columns.
    Code:
      ID (PK)
      PERSON_ID (FK to another table) - There is a non-unique index for this column
      CONTACT_TYPE
    Explain plans are below (Optimizer mode : FIRST_ROWS)

    Code:
    Explain plan for Query 1:
    
    SELECT STATEMENT           TOTAL COST = 8595    
      TABLE ACCESS             CONTACT                BY INDEX ROW ID  COST=8595                          
        INDEX                  INDEX_ON_PERSON_ID     RANGE SCAN       COST=95
          Access Predicates
            PERSON_ID = 'XXX'
    
    Explain plan for Query 2:
    
    SELECT STATEMENT           TOTAL COST = 36979    
      VIEW                            
        Filter Predicates
          PERSON_ID = 'YYY'
        HASH JOIN
          Access Predicates
            ROWID = ROWID
          INDEX                INDEX_ON_PERSON_ID     RANGE SCAN       COST=1075
            Access Predicates
              PERSON_ID = 'YYY'
          INDEX                PK_OF_THIS_TABLE (ID)  FAST FULL SCAN   COST=28794
    As you can see, I wonder just for changing parameter value from 'XXX' to 'YYY' why Oracle comes up with a totally different plan that is way costlier than the other one . I checked the query run time and it drastically differs accordingly.



    Anybody had a similar experience or suggestions ? Thanks in advance.
    Last edited by ramesh1600; 03-02-11 at 15:50.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,512
    When were the statistics last updated, and what is the comparative cardinality of XXX and YYY?

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    Cardinality is 8287 (XXX) Vs 95682 (YYY). But the statistics are very old (3 months old). Thanks for the hint. Will see what happens after analyze.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •