1 Reply Latest reply: Apr 18, 2014 8:21 PM by PBizme RSS

    Partitioning strategy for OBIEE query performance

    PBizme

      I'm using partitioning for the first time and I'm having trouble determining if I can partition my fact table in a way that will allow partition pruning to work with the queries that OBIEE generates.  I've put together a simple example using queries that I wrote to illustrate my problem.  In this example I have a star schema with a fact table and I'm joining in two dimensions.  My fact table is LIST-RANGE partitioned on JOB_ID and TIME_ID and those are the keys that tie to the two dimensions I'm using in this query.

       

      select sum(boxbase) from TEST_RESPONSE_COE_JOB_QTR A      join DIM_STUDY C on A.job_id = C.job_id      join DIM_TIME B on A.response_time_id = B.time_id where C.job_name = 'FY14 CSAT' and B.fiscal_quarter_name = 'Quarter 1';

       

      From what I can tell though, because the query is actually filtering on columns in the dimensions instead of they fact table columns, the pruning isn't actually taking place.  I'm actually seeing slightly better performance from a non-partitioned table even though I wrote this query specifically targeted at the partitioning strategy that is in place now.

       

      If I run the statement below, it runs much faster and an explain plan is very simple and looks to me like it is pruning down to one sub partition like I hoped.  This isn't how any query generated by OBIEE is going to look though.

       

       

      select sum(boxbase) from TEST_RESPONSE_COE_JOB_QTR where job_id = 101123480 and response_time_id < 20000000;

       

      Any suggestions?  I do get some benefit from partition exchange using this setup, but if I'm going to sacrifice reporting performance then that may not be worthwhile or at a minimum I'd need to get rid of my sub partitions if they aren't providing any benefit.

        • 1. Re: Partitioning strategy for OBIEE query performance
          PBizme

          Here are the explain plans I got for the two queries in my original post:

           

          OperationObject NameRowsBytesCostObject NodeIn/OutPStartPStop
          SELECT STATEMENT Optimizer Mode=ALL_ROWS120960
            SORT AGGREGATE113
              VIEWSYS.VW_ST_5BC3A99F101 K1 M20960
                NESTED LOOPS101 K3 M20950
                  PARTITION LIST SUBQUERY101 K2 M1281KEY(SUBQUERY)KEY(SUBQUERY)
                    PARTITION RANGE SUBQUERY101 K2 M1281KEY(SUBQUERY)KEY(SUBQUERY)
                      BITMAP CONVERSION TO ROWIDS101 K2 M1281
                        BITMAP AND
                          BITMAP MERGE
                            BITMAP KEY ITERATION
                              BUFFER SORT
                                INDEX SKIP SCANCISCO_SYSTEMS.DIM_STUDY_UK1171
                              BITMAP INDEX RANGE SCANCISCO_SYSTEMS.FACT_RESPONSE_JOB_ID_BMID_12            KEYKEY
                          BITMAP MERGE
                            BITMAP KEY ITERATION
                              BUFFER SORT
                                VIEWCISCO_SYSTEMS.index$_join$_0525468 K9
                                  HASH JOIN
                                    INDEX RANGE SCANCISCO_SYSTEMS.DIM_TIME_QUARTER_IDX5468 K2
                                    INDEX FAST FULL SCANCISCO_SYSTEMS.TIME_ID_PK5468 K8
                              BITMAP INDEX RANGE SCANCISCO_SYSTEMS.FACT_RESPONSE_TIME_ID_BMIDX_11            KEYKEY
                  TABLE ACCESS BY USER ROWIDCISCO_SYSTEMS.TEST_RESPONSE_COE_JOB_QTR11519679            ROWIDROW L
          OperationObject NameRowsBytesCostObject NodeIn/OutPStartPStop
          SELECT STATEMENT Optimizer Mode=ALL_ROWS11641
            SORT AGGREGATE113
              PARTITION LIST SINGLE198 K2 M1641            KEYKEY
                PARTITION RANGE SINGLE198 K2 M164111
                  TABLE ACCESS FULLCISCO_SYSTEMS.TEST_RESPONSE_COE_JOB_QTR198 K2 M16413636