Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Help me read my 10053 trace file

Help me read my 10053 trace file

From: Shao, Chunning <cshao_at_traffic.com>
Date: Thu, 21 Nov 2002 08:58:44 -0800
Message-ID: <F001.00508FD1.20021121085844@fatcity.com> 





I have a query in which the predictate is like this:
 WHERE ln.metro_id = i_metro_id AND

                                l.metro_id = :b1 ANd
                                eld.metro_id(+) = :b1 AND
            ln.status_desc = 'ACTIVE' AND
            ln.daset_id = ld.daset_id (+) AND
            ln.sect_id = ld.sect_id (+) AND
            ln.life_id = ld.life_id (+) AND
            ln.version_id = ld.version_id (+) AND
            ln.daset_id = l.daset_id AND
            ln.sect_id = l.sect_id AND
            ln.life_id = l.life_id AND
            ln.version_id = l.version_id AND
            l.status_desc = 'ACTIVE' AND
            l.ramp = 'Y' AND
            l.daset_id = eld.daset_id (+) AND
            l.sect_id = eld.sect_id (+) AND
            l.life_id = eld.life_id (+) AND
            l.version_id = eld.version_id (+) AND
            eld.life_id IS NULL





If the table life is analyzed, the bitmap index is not used, and the optimizer choose a full table scan, and it take hours to run.
But if the table life is not analyzed, bitmap index is used, and the query is takes only 1 minutes.



Here is some related info from event 10053



With table life ANALYZED




Table stats    Table: LIFE   Alias:  L


  (Using composite stats)


  TOTAL ::  CDN: 5485935  NBLKS:  7839  TABLE_SCAN_CST: 1190  AVG_ROW_LEN:  182



-- Index stats



  INDEX#: 3906609  COL#: 6 16



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 1   #LB: 268  #DK: 2  LB/K: 10  DB/K: 11  CLUF: 293

  INDEX#: 3905350  COL#: 1 2 3 4 5



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 2   #LB: 23411  #DK: 5485935  LB/K: 1  DB/K: 1  CLUF: 950571

  INDEX#: 3905296  COL#:



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800

  INDEX#: 3905332  COL#:



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800







SINGLE TABLE ACCESS PATH


Column:   METRO_ID  Col#: 5      Table: LIFE   Alias:  L
    NDV: 15        NULLS: 0         DENS: 6.6667e-02 LO:  1  HI: 48
Column: STATUS_DES  Col#: 16     Table: LIFE   Alias:  L
    NDV: 1         NULLS: 0         DENS: 1.0000e+00
Column:       RAMP  Col#: 6      Table: LIFE   Alias:  L
    NDV: 2         NULLS: 0         DENS: 5.0000e-01
  TABLE: LIFE     ORIG CDN: 5485935  CMPTD CDN: 182865


  Access path: tsc  Resc:  1190  Resp:  1190
  Access path: index (equal)

      INDEX#: 3906609  TABLE: LIFE
      CST: 134  IXSEL:  5.0000e-01  TBSEL:  5.0000e-01


******** Bitmap access path rejected ********
Cost: 5223  Selectivity: 0


Not believed to be index-only.


  BEST_CST: 1190.00  PATH: 2  Degree:  1





With table LIFE not analyzed





Table stats    Table: LIFE   Alias:  L


  (Averaging)

  PARTITION [0]    CDN: 798014  NBLKS:  9770  TABLE_SCAN_CST: 1483  AVG_ROW_LEN:  100
  PARTITION [1]    CDN: 1673215  NBLKS:  20485  TABLE_SCAN_CST: 3110  AVG_ROW_LEN:  100
  PARTITION [2]    CDN: 590384  NBLKS:  7228  TABLE_SCAN_CST: 1098  AVG_ROW_LEN:  100
  PARTITION [3]    CDN: 494246  NBLKS:  6051  TABLE_SCAN_CST: 919  AVG_ROW_LEN:  100
  PARTITION [4]    CDN: 577478  NBLKS:  7070  TABLE_SCAN_CST: 1074  AVG_ROW_LEN:  100
  PARTITION [5]    CDN: 1160673  NBLKS:  14210  TABLE_SCAN_CST: 2157  AVG_ROW_LEN:  100
  PARTITION [6]    CDN: 1365282  NBLKS:  16715  TABLE_SCAN_CST: 2538  AVG_ROW_LEN:  100
  PARTITION [7]    CDN: 654747  NBLKS:  8016  TABLE_SCAN_CST: 1217  AVG_ROW_LEN:  100
  PARTITION [8]    CDN: 614969  NBLKS:  7529  TABLE_SCAN_CST: 1143  AVG_ROW_LEN:  100
  PARTITION [9]    CDN: 427595  NBLKS:  5235  TABLE_SCAN_CST: 795  AVG_ROW_LEN:  100
  PARTITION [10]    CDN: 82  NBLKS:  1  TABLE_SCAN_CST: 1  AVG_ROW_LEN:  100
  PARTITION [11]    CDN: 572087  NBLKS:  7004  TABLE_SCAN_CST: 1064  AVG_ROW_LEN:  100
  PARTITION [12]    CDN: 420162  NBLKS:  5144  TABLE_SCAN_CST: 781  AVG_ROW_LEN:  100
  PARTITION [13]    CDN: 322065  NBLKS:  3943  TABLE_SCAN_CST: 599  AVG_ROW_LEN:  100
  PARTITION [14]    CDN: 479871  NBLKS:  5875  TABLE_SCAN_CST: 892  AVG_ROW_LEN:  100
  PARTITION [15]    CDN: 82  NBLKS:  1  TABLE_SCAN_CST: 1  AVG_ROW_LEN:  100
  PARTITION [16]    CDN: 733487  NBLKS:  8980  TABLE_SCAN_CST: 1364  AVG_ROW_LEN:  100


  TOTAL ::  (NOT ANALYZED)    CDN: 640261  NBLKS:  7838  TABLE_SCAN_CST: 1190  AVG_ROW_LEN:  100



-- Index stats



  INDEX#: 3906609  COL#: 6 16



-- Index stats



  INDEX#: 3906609  COL#: 6 16


    PARTITION[0]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[1]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[2]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[3]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[4]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[5]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[6]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[7]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[8]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[9]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[10]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[11]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[12]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[13]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[14]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[15]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[16]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800


    TOTAL :: (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 7838

  INDEX#: 3905350  COL#: 1 2 3 4 5


    PARTITION[0]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[1]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
    PARTITION[2]  (NOT ANALYZED)  LVLS: 1   #LB: 25  #DK: 100  LB/K: 1  DB/K: 1  CLUF: 800
...................




    Omitted here for space saving






SINGLE TABLE ACCESS PATH



Column:   METRO_ID  Col#: 5      Part#: 0     Table: LIFE   Alias:  L


    NO STATISTICS  (using defaults)

    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 1     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 2     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 3     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 4     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 5     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 6     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 7     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 8     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 9     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 10     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:   METRO_ID  Col#: 5      Part#: 11     Table: LIFE   Alias:  L




    NO STATISTICS  (using defaults)



....................... omited some lines here for space saving

Column:       RAMP  Col#: 6      Part#: 16     Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 20008     NULLS: 0         DENS: 4.9980e-05
Column:       RAMP  Col#: 6      Table: LIFE   Alias:  L
    NO STATISTICS  (using defaults)
    NDV: 1177      NULLS: 0         DENS: 8.4966e-04
  TABLE: LIFE     ORIG CDN: 640261  CMPTD CDN: 1


  Access path: tsc  Resc:  1190  Resp:  1190
  Access path: index (stp-guess)

      INDEX#: 3906609  TABLE: LIFE
      CST: 1  IXSEL:  1.0000e-04  TBSEL:  1.6000e-05


******** Bitmap access path accepted ********
Cost: 16  Selectivity: 0


Not believed to be index-only.


  BEST_CST: 15.80  PATH: 20  Degree:  1





Can somebody tell me, how does Oracle get the bitmap index cost of 5223 when table LIFE is analyzed, and cost of 16 when is not?



Thanks





-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 



Author: Shao, Chunning


  INET: cshao_at_traffic.com


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Nov 21 2002 - 10:58:44 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US