Why does Optimizer not pick up indexes?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Why does Optimizer not pick up indexes?

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    Why does Optimizer not pick up indexes?

    The sql in our database is log and complicated. There were a few report sql statements getting so much slower in these a few weeks. It only took about 5 minutes before, but now it needs 2 hours.

    I checked explain plan, there are a lot full table scan, even though there are indexes on the tables for those columns.

    What could be the cause for the sql being so slow? Why does the optimizer not picking up the indexes?

    Thanks!

  2. #2
    Join Date
    Aug 2002
    Location
    Sofia, Bulgaria
    Posts
    84
    You must put more details about the problem SQL statements. The reasons can be many.
    Radoslav Rusinov
    OCP 8i,9i,10g DBA
    http://dba-blog.blogspot.com

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Perhaps it is still using too many indexes.

  5. #5
    Join Date
    May 2002
    Posts
    19
    Are you using CBO ?

    Are your stats for the tables and indexes up to date ?

  6. #6
    Join Date
    Jun 2000
    Posts
    315
    1. Here is the sql that is having problems.

    select a12.DIAGNOSTIC_TROUBLE_CODE_STRING
    DIAGNOSTIC_TROUBLE_CODE_STRING,
    a12.DTC_DESC DTC_DESCRIPTION,
    count(a11.DIAGNOSTIC_TROUBLE_CODE_KEY) WJXBFS1,
    count(distinct a11.VEHICLE_KEY) WJXBFS2
    from QIS2OWNER.FACT_DTC_INCIDENCE a11,
    QIS2OWNER.DIAGNOSTIC_TROUBLE_CODE a12,
    (select pa1.DIAGNOSTIC_TROUBLE_CODE_STRING
    DIAGNOSTIC_TROUBLE_CODE_STRING
    from (select a15.DIAGNOSTIC_TROUBLE_CODE_STRING
    DIAGNOSTIC_TROUBLE_CODE_STRING,
    rank () over( order by
    count(a11.DIAGNOSTIC_TROUBLE_CODE_KEY) desc nulls last) WJXBFS1
    from QIS2OWNER.FACT_DTC_INCIDENCE a11,
    QIS2OWNER.CLAIM_DTC_MAP a12,
    QIS2OWNER.FACT_CLAIM a13,
    QIS2OWNER.DATE_DIMENSION a14,
    QIS2OWNER.DIAGNOSTIC_TROUBLE_CODE a15,
    QIS2OWNER.GMPT_RPOS a16,
    QIS2OWNER.VU_ENGINE_RPO a17,
    QIS2OWNER.LABOR_CODE a18
    where a11.DTC_INCIDENCE_KEY = a12.DTC_INCIDENCE_KEY and
    a12.CLAIM_KEY = a13.CLAIM_KEY and
    a12.MODEL_YEAR = a13.MODEL_YEAR and
    a13.SERVICE_CASE_PROCESS_DATE_KEY = a14.DATE_KEY and
    a11.DIAGNOSTIC_TROUBLE_CODE_KEY =
    a15.DIAGNOSTIC_TROUBLE_CODE_KEY and
    a11.MODEL_YEAR = a16.MODEL_YEAR and
    a11.VEHICLE_KEY = a16.VEHICLE_KEY and
    a16.ENGINE_RPO_KEY = a17.RPO_KEY and
    a13.LABOR_CODE_KEY = a18.LABOR_CODE_KEY
    and (a11.MODEL_YEAR in (2005)
    and a17.ENGINE_RPO_CODE in ('L52')
    and a18.SECTOR in ('ENGINE')
    and a11.SCAN_SOURCE_KEY < 9
    and a14.START_DATE_OF_THE_WEEK >= To_Date('18-Jul-05'))
    group by a15.DIAGNOSTIC_TROUBLE_CODE_STRING
    ) pa1
    where (pa1.WJXBFS1 <= 10.0)
    ) pa2,
    QIS2OWNER.CLAIM_DTC_MAP a13,
    QIS2OWNER.FACT_CLAIM a14,
    QIS2OWNER.DATE_DIMENSION a15,
    QIS2OWNER.GMPT_RPOS a16,
    QIS2OWNER.VU_ENGINE_RPO a17,
    QIS2OWNER.LABOR_CODE a18
    where a11.DIAGNOSTIC_TROUBLE_CODE_KEY = a12.DIAGNOSTIC_TROUBLE_CODE_KEY and
    a12.DIAGNOSTIC_TROUBLE_CODE_STRING =
    pa2.DIAGNOSTIC_TROUBLE_CODE_STRING and
    a11.DTC_INCIDENCE_KEY = a13.DTC_INCIDENCE_KEY and
    a13.CLAIM_KEY = a14.CLAIM_KEY and
    a13.MODEL_YEAR = a14.MODEL_YEAR and
    a14.SERVICE_CASE_PROCESS_DATE_KEY = a15.DATE_KEY and
    a11.MODEL_YEAR = a16.MODEL_YEAR and
    a11.VEHICLE_KEY = a16.VEHICLE_KEY and
    a16.ENGINE_RPO_KEY = a17.RPO_KEY and
    a14.LABOR_CODE_KEY = a18.LABOR_CODE_KEY
    and (a11.MODEL_YEAR in (2005)
    and a17.ENGINE_RPO_CODE in ('L52')
    and a18.SECTOR in ('ENGINE')
    and a11.SCAN_SOURCE_KEY < 9
    and a15.START_DATE_OF_THE_WEEK >= To_Date('18-Jul-05'))
    group by a12.DIAGNOSTIC_TROUBLE_CODE_STRING,
    a12.DTC_DESC

    2. Does too many indexes stop optimizer to use index?

    3. The optimizer_mode = CHOOSE.
    As usual, I analyze all tables every week. I newly created some more indexes on one of the major tables with compute statistics.

    Thanks for the help!

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Do you analyze columns (gathering histograms) as well as the tables and indexes?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool

    Try setting OPTIMIZER_INDEX_COST_ADJ to a low value (10).
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  9. #9
    Join Date
    Jun 2000
    Posts
    315
    Alter session set optimizer_index_cost_adj = 1
    and then check the explain plan for the sql. It did pick up the indexes. But the problem is the query is still running very slow! What could have happened to the database? This query was running more than ten times faster a couple of weeks ago!

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    In retrospect, it would have been a good idea to store execution plans for queries while they were running well, for comparison when things go badly. Not too late to start n that now though.

    You can have a shot at event 10046 tracing, to see what is delaying the result, but it seems as if something has changed and that prompted a change to the execution plan.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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