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

Home -> Community -> Usenet -> c.d.o.server -> Re: Cost of Access Plan using Bitmap Indexes

Re: Cost of Access Plan using Bitmap Indexes

From: Fabrizio Napolitano <fnapolitano_at_belgacom.net>
Date: Fri, 26 Sep 2003 15:18:09 +0000 (UTC)
Message-ID: <7075e4bf7c731503705e3d7f7a7ae72d.119705@mygate.mailgate.org> 






Jonathan,



Here I am... back...and still fighting 


with the plans for bitmap indexes.



First of all let me tell you that I read all 
your articles and I really liked them.



Know I have also read a lot of documentation (in my free time) and
other related threads ... please forgive me if I am missing
something quite obvious, but I am not yet used to Oracle.



Ok now some more detail on my env:


Win2000 4 processor (more or less 3 GB RAM)
Oracle 8.1.7.4.1 (not sure about the last .1)
db_block_size 16384



This is on of my many query that cannot access bitmap indexes:
SELECT/*+ INDEX(t1 TABLE1_IDX_BIT_05) */ -t1."col1" "c1",

       NVL (t1."col2", 0) * 1 "c2",
       NVL (t1."col3", 0) * 1 "c3",
       ...
       t1."last_col" "c53"




FROM   "MY_SCHEMA"."TABLE2" t2, "MY_SCHEMA"."TABLE1" t1

WHERE  t2."col_t2_join" = t1."col_t1_join"
AND    t1."col_t1_where_is_null" IS NULL
AND    t1."col_t1_in_a_b" IN ('XYZ', 'WQR')
AND    t1."col_t1_equal_to" = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'))


AND    (   t1."col_t1_part_of_patition_key_used_in_equal_to_three_OR" =
TO_NUMBER (TO_CHAR (SYSDATE, 'MM'))



        OR t1."col_t1_part_of_patition_key_used_in_equal_to_three_OR" =
TO_NUMBER (TO_CHAR (SYSDATE, 'MM'))





Table1 has more or less 80,000,000 rows and it is partitioned by



col_t1_part_of_patition_key_equal,col_t1_part_of_patition_key_used_in_equal_to_three_OR
in 221 partitions... unfortunately not balanced
on col:


col_t1_join ,col_t1_where_is_null,col_t1_in_a_b,col_t1_equal_to
there is built an one col bitmap index.



here you can also find my 10053 trace, I post only the first part
because already from this part you can find that the indexes cost 
is too high... 


<snip>




PARAMETERS USED BY THE OPTIMIZER




OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 2097152
HASH_JOIN_ENABLED = TRUE




HASH_MULTIBLOCK_IO_COUNT = 0



OPTIMIZER_SEARCH_LIMIT = 5



PARTITION_VIEW_ENABLED = FALSE



_ALWAYS_STAR_TRANSFORMATION = FALSE


_B_TREE_BITMAP_PLANS = FALSE




STAR_TRANSFORMATION_ENABLED = TRUE



_COMPLEX_VIEW_MERGING = FALSE


_PUSH_JOIN_PREDICATE = FALSE




PARALLEL_BROADCAST_ENABLED = FALSE


OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 43




QUERY_REWRITE_ENABLED = FALSE



QUERY_REWRITE_INTEGRITY = ENFORCED



_INDEX_JOIN_ENABLED = FALSE


_SORT_ELIMINATION_COST_RATIO = 0


_OR_EXPAND_NVL_PREDICATE = TRUE


_NEW_INITIAL_JOIN_ORDERS = FALSE


_OPTIMIZER_MODE_FORCE = TRUE


_OPTIMIZER_UNDO_CHANGES = FALSE


_UNNEST_SUBQUERY = FALSE


_PUSH_JOIN_UNION_VIEW = FALSE


_FAST_FULL_SCAN_ENABLED = TRUE


_OPTIM_ENHANCE_NNULL_DETECTION = TRUE


_ORDERED_NESTED_LOOP = FALSE


_NESTED_LOOP_FUDGE = 100


_NO_OR_EXPANSION = FALSE


_QUERY_COST_REWRITE = TRUE




QUERY_REWRITE_EXPRESSION = TRUE



_IMPROVED_ROW_LENGTH_ENABLED = TRUE


_USE_NOSEGMENT_INDEXES = FALSE


_ENABLE_TYPE_DEP_SELECTIVITY = TRUE


_IMPROVED_OUTERJOIN_CARD = TRUE


_OPTIMIZER_ADJUST_FOR_NULLS = TRUE


_OPTIMIZER_CHOOSE_PERMUTATION = 0


_USE_COLUMN_STATS_FOR_FUNCTION = TRUE


_SUBQUERY_PRUNING_ENABLED = TRUE


_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50


_SUBQUERY_PRUNING_COST_FACTOR = 20


_LIKE_WITH_BIND_AS_EQUALITY = FALSE


_TABLE_SCAN_COST_PLUS_ONE = FALSE


_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE


_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE


_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE




DB_FILE_MULTIBLOCK_READ_COUNT = 64



SORT_AREA_SIZE = 2097152



<snip>



I know that DB_FILE_MULTIBLOCK_READ_COUNT = 64 is too high and 
I already asked to change it(even if I am not the DBA here and 
not having access to some informations I cannot really see what
should be the best... I would like a value of 16



<snip>




BASE STATISTICAL INFORMATION





Table stats    Table: TABLE1   Alias: T1
  (Using composite stats)


  TOTAL ::  CDN: 81362697  NBLKS:  209747  TABLE_SCAN_CST: 8100

AVG_ROW_LEN:  408



-- Index stats


  INDEX#: 298810  COL#: 8 



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 1   #LB: 1605  #DK: 118  LB/K: 1  DB/K: 1  CLUF:

11465


  INDEX#: 299044  COL#: 10 



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 1   #LB: 1968  #DK: 6  LB/K: 5  DB/K: 6  CLUF: 2649

  INDEX#: 299284  COL#: 15 



    USING COMPOSITE STATS



    TOTAL ::  LVLS: 1   #LB: 1971  #DK: 14  LB/K: 9  DB/K: 10  CLUF:

2471




Table stats    Table: TABLE2   Alias: T2
  TOTAL ::  CDN: 390124  NBLKS:  13509  TABLE_SCAN_CST: 522

AVG_ROW_LEN:  475



-- Index stats


  INDEX#: 287074  COL#: 4 6 8 10 15 73 



    TOTAL ::  LVLS: 2   #LB: 1031  #DK: 374253  LB/K: 1  DB/K: 1  CLUF:

374253


  INDEX#: 287075  COL#: 43 



    TOTAL ::  LVLS: 1   #LB: 143  #DK: 44759  LB/K: 1  DB/K: 1  CLUF:

44759


  INDEX#: 287076  COL#: 15 



    TOTAL ::  LVLS: 1   #LB: 19  #DK: 20  LB/K: 1  DB/K: 1  CLUF: 34

  INDEX#: 3299  COL#: 1 



    TOTAL ::  LVLS: 1   #LB: 384  #DK: 372864  LB/K: 1  DB/K: 1  CLUF:

15360




SINGLE TABLE ACCESS PATH



  TABLE: TABLE2     ORIG CDN: 390124  CMPTD CDN: 390124

  Access path: tsc  Resc:  522  Resp:  522
  Access path: index (no sta/stp keys)

      INDEX#: 287075  TABLE: TABLE2
      CST: 144  IXSEL:  1.0000e+000  TBSEL:  1.0000e+000
  Access path: index (no sta/stp keys)
      INDEX#: 287076  TABLE: TABLE2
      CST: 20  IXSEL:  1.0000e+000  TBSEL:  1.0000e+000
  Access path: index (no sta/stp keys)
      INDEX#: 3299  TABLE: TABLE2
      CST: 385  IXSEL:  1.0000e+000  TBSEL:  1.0000e+000
  Access path: index (no sta/stp keys)
      INDEX#: 287076  TABLE: TABLE2
      CST: 20  IXSEL:  1.0000e+000  TBSEL:  1.0000e+000


******** Bitmap access path rejected ********
Cost: 11401  Selectivity: 1


Not believed to be index-only.


  BEST_CST: 522.00  PATH: 2  Degree:  1




SINGLE TABLE ACCESS PATH


Column: ASS_MM_FI_  Col#: 9      Table: TABLE1   Alias: T1
    NDV: 11        NULLS: 75624220  DENS: 9.0909e-002 LO:  411  HI:
755129
Column: SOURCE_REF  Col#: 10     Table: TABLE1   Alias: T1
    NDV: 5         NULLS: 0         DENS: 2.0000e-001
Column:       col_t1_equal_to  Col#: 15     Table: TABLE1   Alias: T1
    NDV: 4         NULLS: 0         DENS: 2.5000e-001 LO:  1997  HI:
2003
Column:     col_t1_part_of_patition_key_used_in_equal_to_three_OR  Col#:
14     Table: TABLE1   Alias: T1
    NDV: 12        NULLS: 0         DENS: 8.3333e-002 LO:  1  HI: 12
Column: OPERATING_  Col#: 13     Table: TABLE1   Alias: T1
    NDV: 18        NULLS: 0         DENS: 5.5556e-002
  TABLE: TABLE1     ORIG CDN: 81362697  CMPTD CDN: 86872


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

      INDEX#: 299284  TABLE: TABLE1
      CST: 507  IXSEL:  2.5000e-001  TBSEL:  2.5000e-001
  Access path: index (equal)
      INDEX#: 299044  TABLE: TABLE1
      CST: 408  IXSEL:  2.0000e-001  TBSEL:  2.0000e-001
  Access path: index (equal)
      INDEX#: 299044  TABLE: TABLE1
      CST: 408  IXSEL:  2.0000e-001  TBSEL:  2.0000e-001


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


Not believed to be index-only.


  BEST_CST: 35359.16  PATH: 20  Degree:  2
<snip>



etc. etc..



from another post of you that I found I would expected a value of
18000 more or less... but twice that???



any way if anyone can give me an idea I would really much appreciate!!!



Thanks again 


Fabrizio



-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG


Received on Fri Sep 26 2003 - 10:18:09 CDT












Original text of this message













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