index is not used after a table reorg
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: index is not used after a table reorg

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    128

    index is not used after a table reorg

    Hi,

    Application - SAP
    Database - oracle 10.2.0.4.0
    OS - Sun sparc 64 bit

    The below sql statement was using the index jcds~0 but after a table reorg(using Quest's live reorg) and collecting stats using brconnect, it has started performing full table scans.

    UNIQUE INDEX SAPSR3."JCDS~0" ON SAPSR3.JCDS
    (MANDT, OBJNR, STAT, CHGNR)

    SELECT "MANDT" , "OBJNR" , "STAT" , "CHGNR" , "USNAM" , "UDATE" , "UTIME" , "TCODE" , "CDTCODE" , "INACT" , "CHIND"
    FROM "JCDS" WHERE "MANDT" = 100 AND "OBJNR" BETWEEN 'VB*' AND 'VB99999999999999999999' AND "INACT" = ' ';

    When I explain that statement on oracle sqlplus level, it seems to be using the index. However, when run from SAP, it does a full table scan. Does that make sense?

    I was able to reorg just the table/index on a staging env by retaining the existing stats. The select was still using the index, so that confirmed that the reorg is not causing the issue but the stats is telling the optimizer to change the execution plan.

    So, I copied the stats that work into the problem database hoping it should use the index now but it was still doing full table scans when run from the application level.

    Can someone help me with what else I can look at to resolve this mystery. I'm running out of options. Appreciate your help.

    Regards
    Jay

  2. #2
    Join Date
    Feb 2001
    Posts
    128
    The acutal select statement uses bind variables.
    SELECT "MANDT" , "OBJNR" , "STAT" , "CHGNR" , "USNAM" , "UDATE" , "UTIME" , "TCODE" , "CDTCODE" , "INACT" , "CHIND"
    FROM "JCDS" WHERE "MANDT" = :A0 AND "OBJNR" BETWEEN :A1 AND :A2 AND "INACT" = :A3;

    The values for the bind variables are
    MANDT-100
    OBJNR = VB* and VB99999999999999999999
    INACT = ' '

    Regards
    Jay

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Can you post the explain plan with formatting for the statement here

    Code:
    SELECT MANDT , OBJNR , STAT , 
    CHGNR , USNAM , UDATE , 
    UTIME , 
    TCODE , CDTCODE , 
    INACT , CHIND
    FROM JCDS 
    WHERE MANDT = :A0 
    AND OBJNR BETWEEN :A1 AND :A2 
    AND INACT = :A3

  4. #4
    Join Date
    Mar 2001
    Location
    India
    Posts
    58
    Hi,

    What are distinct values in same index? Did you analyze both?

    Thanks and regards,
    Gitesh Trivedi
    http://www.dbametrix.com

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by giteshtrivedi View Post
    What are distinct values in same index? Did you analyze both?
    ... meaning?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Feb 2001
    Posts
    128
    Attached are the explain plans.

    the Mandt column does not have disticnt values. OBJNR has alot of disticnt values.

    Regards
    Jay
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    I am not sure what i am seeing different in your attached file is the cause of different explain plans

    In attached file SAP screen shot has query as
    select * from JCDS where .........

    In query which you are trying it has

    SELECT "MANDT" , "OBJNR" , "STAT" , "CHGNR" , "USNAM" , "UDATE" , "UTIME" , "TCODE" , "CDTCODE" , "INACT" , "CHIND"
    FROM "JCDS" WHERE .........

    Can you try explain plan in sql with "select * from JCDS where ...."
    Rgds
    Parag

  8. #8
    Join Date
    Feb 2001
    Posts
    128
    Hi,

    Below is the explain for select *

    SQL> explain plan set statement_id='OCT30' for
    2 SELECT * FROM "JCDS" WHERE "MANDT" = 100 AND "OBJNR" BETWEEN 'VB*' AND 'VB99999999999999999999' AND "INACT" = ' ';

    Explained.

    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | SELECT STATEMENT | | 2K| 136K| 121 | | |
    | TABLE ACCESS BY INDEX ROW|JCDS | 2K| 136K| 120 | | |
    | INDEX SKIP SCAN |JCDS~0 | 316 | | 82 | | |
    --------------------------------------------------------------------------------

    6 rows selected.

    Regards
    Jay

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would stop looking at explain plans and start looking at traces.

    Execute/Trace query both from SAP and sqlplus, tkprof/check trace files for the naked truth.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Feb 2001
    Posts
    128
    Hi,

    Do you mean like a 10046 trace? level 10 ?

    regards
    Vj

Posting Permissions

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



Click Here to Expand Forum to Full Width