Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    Berlin, Germany
    Posts
    2

    Post Unanswered: CBO doesn't use Index with low costs

    Hi there,
    Another CBO doesn't use index thread, but I think I've done some of my homeworks.
    There is a combined b*Tree index over 4 columns of 9 Table. When I do a query over these 4 columns CBO chooses FullTableScan (Costs 12799, elapsed 00:01:69.60, 91222cons. gets). When I give it a Hint to use the Index it does (Costs 874, elapsed 00:00:00.71, 480 cons. gets). With the Index it's much faster, but why doesn't CBO use it??
    whole schema analyzed (dbms_utility.analyze_schema compute);
    Here are some statistics I collected:
    Index Statistics for Table PFLEGEDURCHFUEHRUNGEN

    IndexName Uni %NN IndexKeys DistKeys RSL BGES RFTS
    ------------------------------ ---- ------------ ------------ ----------- ----------- ---------
    PFLEGEDURCHF_PK YES 100 4.974.893 4.974.893 442.871 4 10.125
    PFLDF_IZ_CH_SZ_LEIST NO 100 4.974.893 4.971.786 17.034 5 10.125

    PRFTS = Physical Reads per Full Table Scan
    RSL = Range Scan Limit (max. rows# to be retrieved by this index)
    BGES = Blocks Gets per Equality Search (BGES > PRFTS = bad)
    BGFRS = Blocks Gets per Full Range Scan
    Index Statistics for pflegedurchfuehrungen
    #leaf #dist. AVG AVG
    Index Uni LEV blk keys LF/k Data Clust. Factor
    ------------------------------ --- ---- -------- ---------- ---- ---- -----------
    PFLDF_IZ_CH_SZ_LEIST NON 3 41'530 4'971'786 1 1 2'915'595
    When I delete statistics on the Index it's used by CBO.
    Any Ideas?
    Uwe

  2. #2
    Join Date
    Nov 2001
    Posts
    25
    Provided Answers: 1
    use event 10053 to trace what CBO is doing.

  3. #3
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Try rebuilding the index, then analyze.

    Might be that the index is too much fragmentated.

    Ruud
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  4. #4
    Join Date
    Feb 2002
    Location
    Berlin, Germany
    Posts
    2
    Hi Ruud,
    the index is new, it's a Database for testings with production data in it. I will try out the tip with event 10053, but I never did this before, so I must read some doku's.
    Uwe

Posting Permissions

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