DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: bitmap index query is slow in DW

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    bitmap index query is slow in DW

    All,

    I have Dataware house environment. I have bitmap index in all the dimension id's in fact table. Here is the query. This query took 10 min... It is not affordable time. enterprise_sales_det_fact has 30 million records. quote has 2 million, orders has 3 million.

    Any help?..

    SELECT count(*)
    FROM quote a, enterprise_sales_det_fact b, orders c
    WHERE a.quote_dim_sid = b.quote_dim_sid
    AND b.order_dim_sid = c.order_dim_sid


    We have bit map index on quote_dim_sid & order_dim_sid .

    Here is the analyze statement i used.

    exec dbms_stats.gather_table_stats(ownname => 'QUOTEPRD', tabname => 'ORDERS',estimate_percent => 10, method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

    Step # Step Name
    12 SELECT STATEMENT
    11 SORT [AGGREGATE]
    10 NESTED LOOPS
    8 NESTED LOOPS
    6 . VIEW
    5 HASH JOIN
    2 BITMAP CONVERSION [TO ROWIDS]
    1 QUOTEPRD.ENT_FACT_ORDER_BIDX BITMAP INDEX [FULL SCAN]
    4 BITMAP CONVERSION [TO ROWIDS]
    3 QUOTEPRD.ENT_FACT_QUOTE_BIDX BITMAP INDEX [FULL SCAN]
    7 QUOTEPRD.QUOTE_UIDX INDEX [UNIQUE SCAN]
    9 QUOTEPRD.ORDERS_UIDX INDEX [UNIQUE SCAN]



    Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
    1 This plan step retrieves one or more ROWIDs by scanning all bits in the bitmap index ENT_FACT_ORDER_BIDX to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --

    2 This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.

    3 This plan step retrieves one or more ROWIDs by scanning all bits in the bitmap index ENT_FACT_QUOTE_BIDX to find the rows which satisfy a condition specified in the querys WHERE clause. -- -- --

    4 This plan step accepts a bitmap representation of an index from its child node, and converts it to a ROWID that can be used to access the table.

    5 This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. -- 27,037,540 290,442.324

    6 This plan step represents the execution plan for the subquery defined by the view . 26,527 27,037,540 290,442.324

    7 This plan step retrieves a single ROWID from the B*-tree index QUOTE_UIDX. -- 1 0.007

    8 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 26,528 27,037,540 475,269.258

    9 This plan step retrieves a single ROWID from the B*-tree index ORDERS_UIDX. -- 1 0.007

    10 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 26,529 27,037,540 660,096.191

    11 This plan step accepts a row set (its only child) and returns a single row by applying an aggregation function. -- 1 0.024

    12 This plan step designates this statement as a SELECT statement. 26,529 -- --

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    do you have star transformations enabled?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    your query doens't make any sense and will certainly not show you any advantages of bitmap indexes.

    To test a feature it would be good to understand it. I would recommand oyu to read:
    http://oraclesvca2.oracle.com/docs/c...exes.htm#97322

  4. #4
    Join Date
    Dec 2005
    Posts
    195
    Slimdave,

    It is set to temp_disable. What does it mean? Is this slowing the query performance?

    SQLPLUS> select value from v$parameter where
    2 name ='star_transformation_enabled';

    VALUE
    --------------------------------------------------------------------------------
    TEMP_DISABLE

    Regards
    GT

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    remp_disable is a setting that prevents intermediate result sets from being materialized -- it's associated with some bugs. Search metalink to see if it applies to your version, or just test it by setting it to enable at the session level.

    Anyway, as Mike says your query is rather irregular -- not the usual data warehousing thing at all, and bitmap indexes on the fact table ought not to be used.

    However I would have thought that you'd see hash joins from the fact table to the two other tables -- those nested loops are a problem. You might try hinting to see if you can achieve better performance with that plan, and then we could see what can be done to achieve it without hints.

    Do you have FK's on those joins?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    such a query will not use the startransformation. so there is no problem related to the star transformation.

    For information related to oracle parameteres you may have a look at:
    http://download-west.oracle.com/docs...a96536/toc.htm
    or more specificly at:
    http://download-west.oracle.com/docs...04.htm#1024600

    To use star transformation you need to set some where condition (against the dimensions) to limit the number of returned row (eg where age>10 and city = 'San Francisco').

    In the test case you are using it would probably be faster to use b-tree indexes. So as I allready said it would be good to understand the benefit of a feature before testing it.

    http://download-west.oracle.com/docs...schem.htm#4859
    http://download-west.oracle.com/docs..._acc.htm#17538
    http://download-west.oracle.com/docs...exes.htm#99353

  7. #7
    Join Date
    Dec 2005
    Posts
    195
    Slimdave,

    We do not have any FK's on our star schema. Does it matter?
    But we do have unique index on dimension table for dim_sid's. We have bitmap index on fact tables.

    I changed the alter session set star_transformation_enabled = true at session level.
    But i don't see any better performance.

    11:09:34 SQLPLUS> alter session set star_transformation_enabled = true;

    Session altered.

    11:15:11 SQLPLUS>
    11:16:01 SQLPLUS>
    11:16:01 SQLPLUS>
    11:16:01 SQLPLUS> SELECT count(*)
    11:16:01 2 FROM quote a, enterprise_sales_det_fact b, orders c
    11:16:01 3 WHERE
    11:16:01 4 quote_created_date between '01-OCT-2005' and '31-OCT-2005'
    11:16:01 5 and a.quote_dim_sid = b.quote_dim_sid
    11:16:01 6 AND b.order_dim_sid = c.order_dim_sid
    11:16:01 7 /

    COUNT(*)
    ----------
    1035724

    11:18:49 SQLPLUS>
    11:18:49 SQLPLUS>

    Here is the database version i am using.

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
    PL/SQL Release 9.2.0.6.0 - Production
    CORE 9.2.0.6.0 Production
    TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
    NLSRTL Version 9.2.0.6.0 - Production



    Mike, Can you help me how to make this as a suitable query in star schema environment.
    I really need your advice here.

    Another problem is, we are using cognos. the cognos is interpreting the date column as a TIMESTAMP. The table field is DATE data type. When cognos build the query, it internally changes to TIMESTAMP.

    The following query took 48 minutes :
    this query is build by cognos.

    SELECT count(*)
    FROM quote a, enterprise_sales_det_fact b, orders c
    WHERE a.quote_dim_sid = b.quote_dim_sid
    AND b.order_dim_sid = c.order_dim_sid
    and quote_created_date between TIMESTAMP '2005-10-01 00:00:00.000' and TIMESTAMP '2005-10-31 23:59:59.999'

    while the following took 2 min 25 sec :
    This query is build by us.

    SELECT count(*)
    FROM quote a, enterprise_sales_det_fact b, orders c
    WHERE a.quote_dim_sid = b.quote_dim_sid
    AND b.order_dim_sid = c.order_dim_sid
    and quote_created_date between '01-OCT-2005' and '31-OCT-2005'


    Anyhow, the hash joins and nested loops are taking much time.

    I wanted to bring this query to 30 seconds. Meanwhile, i will gothrough the oracle metalink.

    Regards
    GT

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    The required conditions for a star transformation can be found in Chapter 17 of the Oracle9i Data Warehousing Guide
    Tuning Star Queries
    To get the best possible performance for star queries, it is important to follow some basic guidelines:
    • A bitmap index should be built on each of the foreign key columns of the fact table or tables.
    • The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to true. This enables an important optimizer feature for star-queries. It is set to false by default for backward-compatibility.
    • The cost-based optimizer should be used. This does not apply solely to star schemas: all data warehouses should always use the cost-based optimizer.

    When a data warehouse satisfies these conditions, the majority of the star queries running in the data warehouse will use a query execution strategy known as the star transformation. The star transformation provides very efficient query performance for star queries.
    http://download-west.oracle.com/docs...emas.htm#11616

    So yes you need FKs.

    Related to your 2 queries it would be good to see the explain plan (but first create the FKs and retest) to be able to compare the 2 queries.

    Which are the values, when you execute your queries, of the parameters:
    NLS_DATE_FORMAT
    NLS_TIMESTAMP_FORMAT
    WORKAREA_SIZE_POLICY
    PGA_AGGREGATE_TARGET
    HASH_AREA_SIZE
    SORT_AREA_SIZE
    BITMAP_MERGE_AREA_SIZE

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    For the original query it seems to me that the issue here is just to get the optimum join order and method between the three tables. Since there are only three tables and a limited number of join types it seems reasonable that you could test them quite easily. Have a look here http://download-west.oracle.com/docs...tsref.htm#8326 for hints to change the join order (ordered) and the join methodology (hash, merge, nl) and see what works best for you.

    If this is a realy critical query then you could get virtually instant response by using materialized views, although that would take a little research if you haven't used them before.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    Mar 2002
    Posts
    534
    2 more parameteres which i'm interested in are:
    OPTIMIZER_INDEX_CACHING
    OPTIMIZER_INDEX_COST_ADJ

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