This discussion is archived
2 Replies Latest reply: Jan 16, 2013 6:38 AM by Ahsan Shah RSS

OBIEE  Performance Issues

967489 Newbie
Currently Being Moderated
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
    rharrispcl Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    Do you have a FILTER on the FACT table in the OBIEE report that uses the FACT PARTITION KEY?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points