2 Replies Latest reply: Jan 16, 2013 8:38 AM by Ahsan Shah RSS

    OBIEE  Performance Issues

    967489
      I am experiencing Performance issues with queries generated by OBIEE. The query generated by OBIEE run 2+ hours. Looking at the generated SQL, the execution plan is not utilizing indexes on the FACT table.
      We have dimension table linked to a partitioned FACT table. We have created local bitmap indexes on all dimension keys. The execution plan generated for the OBIEE generated SQL statement does not use indexes, it executes a FULL table scan on our FACT table which has approximately 260 million rows. When I extract out the SELECT portion retrieving the information from the tables, the execution plan changes and indexes are used. Does anyone know what would cause oracle not to execute the same execution plan for the OBIEE generated SQL?


      OBIEE generated SQL
      WITH SAWITH0
      AS ( SELECT SUM (T92891.DEBIT_AMOUNT) AS c1,
      SUM (T92891.CREDIT_AMOUNT) AS c2,
      T91932.COMPL_ACCOUNT_NBR AS c3,
      T92541.APPROP_SYMBOL AS c4,
      T92541.FUND_CODE AS c5,
      T91992.ACCOUNT_SERIES_NAME AS c6,
      T91932.ACCOUNT_NBR AS c7
      FROM DW_FRR.DIM_FUND_CODE_FISCAL_YEAR T92149,
      DW_ICE.DIM_FUND T92541,
      DW_FRR.DIM_ACCOUNT T91932,
      DW_FRR.DIM_ACCOUNT_SERIES T91992,
      DW_ICE.FACT_GL_TRANSACTION_DETAIL T92891
      WHERE (T91932.ACCOUNT_SID_PK = T92891.ACCOUNT_SID_FK
      AND T91932.ACCOUNT_SERIES_SID_FK =
      T91992.ACCOUNT_SERIES_SID_PK
      AND T92149.FUND_CODE_FISCAL_YEAR_SID_PK =
      T92891.FUND_CODE_FISCAL_YEAR_SID_FK
      AND T92541.FUND_SID_PK = T92891.FUND_SID_FK
      AND T92149.FISCAL_YEAR >= :"SYS_B_0")
      GROUP BY T91932.ACCOUNT_NBR,
      T91932.COMPL_ACCOUNT_NBR,
      T91992.ACCOUNT_SERIES_NAME,
      T92541.FUND_CODE,
      T92541.APPROP_SYMBOL),
      SAWITH1 AS (SELECT DISTINCT :"SYS_B_1" AS c1,
      D1.c3 AS c2,
      D1.c4 AS c3,
      D1.c5 AS c4,
      D1.c2 AS c5,
      D1.c1 AS c6,
      D1.c6 AS c7,
      D1.c7 AS c8
      FROM SAWITH0 D1)
      SELECT D1.c1 AS c1,
      D1.c2 AS c2,
      D1.c3 AS c3,
      D1.c4 AS c4,
      D1.c5 AS c5,
      D1.c6 AS c6
      FROM SAWITH1 D1
      ORDER BY c1,
      c3,
      c2,
      c4


      Execution PLan

      Plan
      SELECT STATEMENT ALL_ROWS Cost: 1 M
           29 PX COORDINATOR
                28 PX SEND QC (ORDER) PARALLEL_TO_SERIAL SYS.:TQ10005 :Q1005 Cost: 1 M Bytes: 1019 M Cardinality: 11 M
                     27 SORT GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1005 Cost: 1 M Bytes: 1019 M Cardinality: 11 M
                          26 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1005 Cost: 972 K Bytes: 1019 M Cardinality: 11 M
                               25 PX SEND RANGE PARALLEL_TO_PARALLEL SYS.:TQ10004 :Q1004 Cost: 972 K Bytes: 1019 M Cardinality: 11 M
                                    24 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1004 Cost: 972 K Bytes: 1019 M Cardinality: 11 M
                                         4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1004 Cost: 2 Bytes: 3 K Cardinality: 179
                                              3 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10002 :Q1002 Cost: 2 Bytes: 3 K Cardinality: 179
                                                   2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1002 Cost: 2 Bytes: 3 K Cardinality: 179
                                                        1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.DIM_FUND :Q1002 Cost: 2 Bytes: 3 K Cardinality: 179
                                         23 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1004 Cost: 972 K Bytes: 843 M Cardinality: 11 M
                                              20 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1004 Cost: 9 Bytes: 54 K Cardinality: 962
                                                   19 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10003 :Q1003 Cost: 9 Bytes: 54 K Cardinality: 962
                                                        18 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1003 Cost: 9 Bytes: 54 K Cardinality: 962
                                                             15 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003 Cost: 6 Bytes: 814 Cardinality: 22
                                                                  14 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001 Cost: 6 Bytes: 814 Cardinality: 22
                                                                       13 MERGE JOIN CARTESIAN PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 6 Bytes: 814 Cardinality: 22
                                                                            9 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1001
                                                                                 8 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 2 Bytes: 16 Cardinality: 2
                                                                                      7 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 2 Bytes: 16 Cardinality: 2
                                                                                           6 TABLE ACCESS BY INDEX ROWID TABLE DW_FRR.DIM_FISCAL_YEAR Cost: 2 Bytes: 16 Cardinality: 2
                                                                                                5 INDEX RANGE SCAN INDEX (UNIQUE) DW_FRR.UNQ_DIM_FISCAL_YEAR_IDX Cost: 1 Cardinality: 2
                                                                            12 BUFFER SORT PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 4 Bytes: 319 Cardinality: 11
                                                                                 11 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001 Cost: 2 Bytes: 319 Cardinality: 11
                                                                                      10 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT_SERIES :Q1001 Cost: 2 Bytes: 319 Cardinality: 11
                                                             17 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1003 Cost: 2 Bytes: 10 K Cardinality: 481
                                                                  16 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT :Q1003 Cost: 2 Bytes: 10 K Cardinality: 481
                                              22 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1004 Cost: 971 K Bytes: 4 G Cardinality: 207 M Partition #: 28 Partitions accessed #1 - #12
                                                   21 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.FACT_GL_TRANSACTION_DETAIL :Q1004 Cost: 971 K Bytes: 4 G Cardinality: 207 M Partition #: 28 Partitions accessed #1 - #132




      Inner SQL Statement without the OBIEE wrap around SQL
      SELECT SUM (T92891.DEBIT_AMOUNT) AS c1,
      SUM (T92891.CREDIT_AMOUNT) AS c2,
      T91932.COMPL_ACCOUNT_NBR AS c3,
      T92541.APPROP_SYMBOL AS c4,
      T92541.FUND_CODE AS c5,
      T91992.ACCOUNT_SERIES_NAME AS c6,
      T91932.ACCOUNT_NBR AS c7
      FROM DW_FRR.DIM_FUND_CODE_FISCAL_YEAR T92149,
      DW_ICE.DIM_FUND T92541,
      DW_FRR.DIM_ACCOUNT T91932,
      DW_FRR.DIM_ACCOUNT_SERIES T91992,
      DW_ICE.FACT_GL_TRANSACTION_DETAIL T92891
      WHERE (T91932.ACCOUNT_SID_PK = T92891.ACCOUNT_SID_FK
      AND T91932.ACCOUNT_SERIES_SID_FK =
      T91992.ACCOUNT_SERIES_SID_PK
      AND T92149.FUND_CODE_FISCAL_YEAR_SID_PK =
      T92891.FUND_CODE_FISCAL_YEAR_SID_FK
      AND T92541.FUND_SID_PK = T92891.FUND_SID_FK
      AND T92149.FISCAL_YEAR >= :"SYS_B_0")
      GROUP BY T91932.ACCOUNT_NBR,
      T91932.COMPL_ACCOUNT_NBR,
      T91992.ACCOUNT_SERIES_NAME,
      T92541.FUND_CODE,
      T92541.APPROP_SYMBOL


      Execution Plan
      Plan
      SELECT STATEMENT ALL_ROWS Cost: 25 K Bytes: 79 M Cardinality: 728 K
           28 PX COORDINATOR
                27 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10002 :Q1002 Cost: 25 K Bytes: 79 M Cardinality: 728 K
                     26 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1002 Cost: 25 K Bytes: 79 M Cardinality: 728 K
                          25 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002 Cost: 25 K Bytes: 79 M Cardinality: 728 K
                               24 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001 Cost: 25 K Bytes: 79 M Cardinality: 728 K
                                    23 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 25 K Bytes: 79 M Cardinality: 728 K
                                         22 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 12 K Bytes: 190 M Cardinality: 2 M
                                              4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 2 Bytes: 319 Cardinality: 11
                                                   3 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10000 :Q1000 Cost: 2 Bytes: 319 Cardinality: 11
                                                        2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000 Cost: 2 Bytes: 319 Cardinality: 11
                                                             1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT_SERIES :Q1000 Cost: 2 Bytes: 319 Cardinality: 11
                                              21 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 12 K Bytes: 142 M Cardinality: 2 M
                                                   6 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001
                                                        5 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT :Q1001 Cost: 2 Bytes: 12 K Cardinality: 481
                                                   20 VIEW PUSHED PREDICATE VIEW PARALLEL_COMBINED_WITH_PARENT SYS.VW_GBC_17 :Q1001 Bytes: 660 Cardinality: 11
                                                        19 SORT GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 10 K Bytes: 376 K Cardinality: 5 K
                                                             18 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 10 K Bytes: 2 M Cardinality: 36 K
                                                                  7 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.DIM_FUND :Q1001 Cost: 2 Bytes: 7 K Cardinality: 179
                                                                  17 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1001
                                                                       15 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 10 K Bytes: 1 M Cardinality: 36 K
                                                                            8 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_FISCAL_YEAR :Q1001 Cost: 2 Bytes: 16 Cardinality: 2
                                                                            14 PARTITION LIST ALL PARALLEL_COMBINED_WITH_PARENT :Q1001 Partition #: 22 Partitions accessed #1 - #11
                                                                                 13 PARTITION LIST ALL PARALLEL_COMBINED_WITH_PARENT :Q1001 Partition #: 23 Partitions accessed #1 - #12
                                                                                      12 BITMAP CONVERSION TO ROWIDS PARALLEL_COMBINED_WITH_PARENT :Q1001
                                                                                           11 BITMAP AND PARALLEL_COMBINED_WITH_PARENT :Q1001
                                                                                                9 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) PARALLEL_COMBINED_WITH_PARENT DW_ICE.FK_ACCOUNT_GLTRANS_IDX :Q1001 Partition #: 23 Partitions accessed #1 - #132
                                                                                                10 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) PARALLEL_COMBINED_WITH_PARENT DW_ICE.FK_FUNDCODE_FY_GLTRANS_IDX :Q1001 Partition #: 23 Partitions accessed #1 - #132
                                                                       16 TABLE ACCESS BY LOCAL INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.FACT_GL_TRANSACTION_DETAIL :Q1001 Cost: 10 K Bytes: 401 K Cardinality: 18 K Partition #: 23 Partitions accessed #1


      Any and all help would be greatly appreciated.
        • 1. Re: OBIEE  Performance Issues
          Robin Harris
          Have you gathered statistics in the data warehouse recently? That's one reason the optimizer might choose the wrong execution plan.

          Is the schema a star schema? If so do you have the init.ora parameter 'STAR_TRANSFORMATION_ENABLED' set to yes in the DW? This can drastically affect performance.

          Please test any changes you make in a test system before applying to live as altering things can have unrequired impacts/

          Thanks
          Robin
          • 2. Re: OBIEE  Performance Issues
            Ahsan Shah
            Do you have a FILTER on the FACT table in the OBIEE report that uses the FACT PARTITION KEY?