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

Thread: index is not used after a table reorg

  1. #11
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    yes indeed; like:
    Code:
    alter session set timed_statistics = true;
    alter session set sql_trace = true;
    set autotrace on explain
    alter session set events='10046 trace name context forever, level 12';
    ++ EXECUTE SQL ++
    alter session set sql_trace = false;
    alter session set timed_statistics = false;
    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.

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

    I am thinking this could be due to histograms.

    If you run the following on sqlplus you should have the full table scan back
    with bind varaibles instead of hard coded literals.

    Code:
    explain plan for
    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
    
    select * from table(dbms_xplan.display)
    You might want to try and delete the histograms on the table and collect stats using dbms_stats and see what happens.

    regards

  3. #13
    Join Date
    Feb 2001
    Posts
    128
    Hi,

    There are no histograms on that table columns.

    SELECT column_name, num_distinct, num_buckets, histogram FROM USER_TAB_COL_STATISTICS WHERE table_name = 'JCDS'

    COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
    MANDT 1 1 NONE
    OBJNR 18262124 1 NONE
    STAT 406 1 NONE
    CHGNR 670 1 NONE
    USNAM 22186 1 NONE
    UDATE 4065 1 NONE
    UTIME 86400 1 NONE
    TCODE 125 1 NONE
    CDTCODE 271 1 NONE
    INACT 2 1 NONE
    CHIND 2 1 NONE

    Regards
    Jay

  4. #14
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Would you mind showing me the output of the statements that i have posted below.

    Code:
    explain plan for
    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
    
    select * from table(dbms_xplan.display)
    and also the plan with this

    Code:
    SELECT /*+ gather_plan_statistics */ * FROM 
    JCDS 
    WHERE 
    MANDT = 100 
    AND OBJNR BETWEEN 'VB*' AND 'VB99999999999999999999' 
    AND INACT = ' ';
    
    select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
    Last edited by hrishy; 11-04-2009 at 03:27 AM.

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