1 Reply Latest reply: Apr 18, 2012 1:33 PM by Nasar-Oracle RSS

    OLAP 10g to 11g olap_table problem

    930159
      After upgrading OLAP 10g to 11g olap_table function does not work correctly

      changed execution plan
      and the query runs slowly

      how to make 11g work the same way as 10g?

      query sample:

      select sum(T77595.DEBIT) as c1
      from
      MOLAP.ADVANCES_CUBE_V T77595,
      MOLAP.CRCATEGORIES_DIM_V T728,
      MOLAP.RESIDENT_DIM_V T873,
      MOLAP.LEGALPRIVATE_DIM_V T819,
      MOLAP.DEPARTMENTS_DIM_V1 T752,
      MOLAP.TIMES_DIM_V1 T11356
      where
      T728.CRCATEGORIES_ID = T77595.CRCATEGORIES_ID
      and T752.DEPARTMENTS_ID = T77595.DEPARTMENTS_ID
      and T819.LEGALPRIVATES_ID = T77595.LEGALPRIVATES_ID
      and T873.RESIDENTS_ID = T77595.RESIDENTS_ID
      and T11356.TIMES_ID = T77595.TIMES_ID
      and T728.CRCATEGORIES_LEVEL = 'TOTAL'
      and T752.DEPARTMENTS_LEVEL = 'TOTAL'
      and T819.LEGALPRIVATE_LEVEL = 'TOTAL'
      and T873.RESIDENT_LEVEL = 'TOTAL'
      and T11356.TIMES_LEVEL = 'TOTAL'


      oracle 10g plan

      Plan
      SELECT STATEMENT ALL_ROWSCost: 218 Bytes: 20.033 Cardinality: 1                                                   
           30 SORT AGGREGATE Bytes: 20.033 Cardinality: 1                                              
                29 HASH JOIN Cost: 218 Bytes: 3.888.204.970 Cardinality: 194.090                                         
                     4 VIEW VIEW MOLAP.TIMES_DIM_V1 Cost: 36 Bytes: 328.328 Cardinality: 164                                    
                          3 BUFFER SORT Bytes: 328 Cardinality: 164                               
                               2 SQL MODEL AW HASH Bytes: 328 Cardinality: 164                          
                                    1 COLLECTION ITERATOR PICKLER FETCH PROCEDURE OLAP_TABLE                     
                     28 HASH JOIN Cost: 181 Bytes: 2.133.914.757 Cardinality: 118.347                                    
                          8 VIEW VIEW MOLAP.DEPARTMENTS_DIM_V1 Cost: 36 Bytes: 328.328 Cardinality: 164                               
                               7 BUFFER SORT Bytes: 328 Cardinality: 164                          
                                    6 SQL MODEL AW HASH Bytes: 328 Cardinality: 164                     
                                         5 COLLECTION ITERATOR PICKLER FETCH PROCEDURE OLAP_TABLE                
                          27 HASH JOIN Cost: 144 Bytes: 1.156.700.727 Cardinality: 72.163                               
                               12 VIEW VIEW MOLAP.LEGALPRIVATE_DIM_V Cost: 36 Bytes: 328.328 Cardinality: 164                          
                                    11 BUFFER SORT Bytes: 328 Cardinality: 164                     
                                         10 SQL MODEL AW HASH Bytes: 328 Cardinality: 164                
                                              9 COLLECTION ITERATOR PICKLER FETCH PROCEDURE OLAP_TABLE           
                               26 HASH JOIN Cost: 108 Bytes: 617.216.054 Cardinality: 44.002                          
                                    16 VIEW VIEW MOLAP.RESIDENT_DIM_V Cost: 36 Bytes: 328.328 Cardinality: 164                     
                                         15 BUFFER SORT Bytes: 328 Cardinality: 164                
                                              14 SQL MODEL AW HASH Bytes: 328 Cardinality: 164           
                                                   13 COLLECTION ITERATOR PICKLER FETCH PROCEDURE OLAP_TABLE      
                                    25 HASH JOIN Cost: 71 Bytes: 322.630.750 Cardinality: 26.830                     
                                         20 VIEW VIEW MOLAP.CRCATEGORIES_DIM_V Cost: 36 Bytes: 328.328 Cardinality: 164                
                                              19 BUFFER SORT Bytes: 328 Cardinality: 164           
                                                   18 SQL MODEL AW HASH Bytes: 328 Cardinality: 164      
                                                        17 COLLECTION ITERATOR PICKLER FETCH PROCEDURE OLAP_TABLE
                                         24 VIEW VIEW MOLAP.ADVANCES_CUBE_V Cost: 35 Bytes: 163.976.280 Cardinality: 16.360                
                                              23 BUFFER SORT Bytes: 32.720 Cardinality: 16.360           
                                                   22 SQL MODEL AW HASH Bytes: 32.720 Cardinality: 16.360      
                                                        21 COLLECTION ITERATOR PICKLER FETCH PROCEDURE OLAP_TABLE


      oracle 11g plan

      Plan
      SELECT STATEMENT ALL_ROWSCost: 4.734 Bytes: 20.033 Cardinality: 1                                                   
           30 SORT AGGREGATE Bytes: 20.033 Cardinality: 1                                              
                29 HASH JOIN Cost: 4.734 Bytes: 2.504.125 Cardinality: 125                                         
                     4 VIEW VIEW MOLAP.TIMES_DIM_V1 Cost: 2 Bytes: 10.010 Cardinality: 5                                    
                          3 BUFFER SORT Bytes: 5.600 Cardinality: 5                               
                               2 SQL MODEL AW HASH Bytes: 5.600 Cardinality: 5                          
                                    1 CUBE SCAN OUTER MOLAP.MOLAP Cost: 2 Bytes: 5.600 Cardinality: 5                     
                     28 HASH JOIN Cost: 4.732 Bytes: 45.077.500 Cardinality: 2.500                                    
                          8 VIEW VIEW MOLAP.DEPARTMENTS_DIM_V1 Cost: 2 Bytes: 10.010 Cardinality: 5                               
                               7 BUFFER SORT Bytes: 5.300 Cardinality: 5                          
                                    6 SQL MODEL AW HASH Bytes: 5.300 Cardinality: 5                     
                                         5 CUBE SCAN OUTER MOLAP.MOLAP Cost: 2 Bytes: 5.300 Cardinality: 5                
                          27 HASH JOIN Cost: 4.729 Bytes: 801.450.000 Cardinality: 50.000                               
                               12 VIEW VIEW MOLAP.LEGALPRIVATE_DIM_V Cost: 2 Bytes: 10.010 Cardinality: 5                          
                                    11 BUFFER SORT Bytes: 1.000 Cardinality: 5                     
                                         10 SQL MODEL AW HASH Bytes: 1.000 Cardinality: 5                
                                              9 CUBE SCAN OUTER MOLAP.MOLAP Cost: 2 Bytes: 1.000 Cardinality: 5           
                               26 HASH JOIN Cost: 4.722 Bytes: 14.027.000.000 Cardinality: 1.000.000                          
                                    16 VIEW VIEW MOLAP.RESIDENT_DIM_V Cost: 2 Bytes: 10.010 Cardinality: 5                     
                                         15 BUFFER SORT Bytes: 1.000 Cardinality: 5                
                                              14 SQL MODEL AW HASH Bytes: 1.000 Cardinality: 5           
                                                   13 CUBE SCAN OUTER MOLAP.MOLAP Cost: 2 Bytes: 1.000 Cardinality: 5      
                                    25 HASH JOIN Cost: 4.627 Bytes: 240.500.000.000 Cardinality: 20.000.000                     
                                         20 VIEW VIEW MOLAP.CRCATEGORIES_DIM_V Cost: 2 Bytes: 10.010 Cardinality: 5                
                                              19 BUFFER SORT Bytes: 2.300 Cardinality: 5           
                                                   18 SQL MODEL AW HASH Bytes: 2.300 Cardinality: 5      
                                                        17 CUBE SCAN OUTER MOLAP.MOLAP Cost: 2 Bytes: 2.300 Cardinality: 5
                                         24 VIEW VIEW MOLAP.ADVANCES_CUBE_V Cost: 2.776 Bytes: 4.009.200.000.000 Cardinality: 400.000.000                
                                              23 BUFFER SORT Bytes: 112.000.000.000 Cardinality: 400.000.000           
                                                   22 SQL MODEL AW HASH Bytes: 112.000.000.000 Cardinality: 400.000.000      
                                                        21 CUBE SCAN OUTER MOLAP.MOLAP Cost: 2.776 Bytes: 112.000.000.000 Cardinality: 400.000.000

      Edited by: 927156 on 12.04.2012 2:24
        • 1. Re: OLAP 10g to 11g olap_table problem
          Nasar-Oracle
          Few points to keep in mind, in case you are not aware of it.

          (1). Starting with OBIEE 11.1.1.5, it understands the olap metadata. So if you are using OBIEE 11.1.1.5 or later, then there is no need to create custom OLAP_TABLE views. Once olap metadata is imported into RPD, then OBIEE generates OLAP_TABLE queries on-the-fly at reporting time - somewhat similar to what 'Discoverer Plus OLAP' or 'Bi spreadsheet Addin' used to do in the past.

          (2). If you are using custom OLAP_TABLE views, then 'LOOP OPTIMIZATION' keyword is needed in limitmap, which makes a big difference to the queries. http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_app_sqlfunc001.htm

          (3). 'LOOP OPTIMIZATION' uses measure properties $LOOP_DENSE and $LOOP_VAR, so make sure that you have set those properties for each measure.
          http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_app_sqlfunc001.htm#CIHJHCFC

          (4). If you are using 11.2.0.2 version of Oracle database, then you can use the new logging features. Look at CUBE_BUILD_LOG package at http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_cube_log.htm#ARPLS72789 You can enable the query logging by setting the log level to highest, then execute your OLAP_TABLE query and then set the logging level back to medium. You can then see which "step" is taking a long time.

          BEGIN
          dbms_cube_log.enable(DBMS_CUBE_LOG.TYPE_OPERATIONS, DBMS_CUBE_LOG.TARGET_TABLE, DBMS_CUBE_LOG.LEVEL_HIGHEST);
          END;

          <.... execute your query...>

          BEGIN
          dbms_cube_log.enable(DBMS_CUBE_LOG.TYPE_OPERATIONS, DBMS_CUBE_LOG.TARGET_TABLE, DBMS_CUBE_LOG.LEVEL_MEDIUM);
          END;


          SELECT * from CUBE_OPERATIONS_LOG order by time desc;

          Edited by: Nasar Ali-Khan on Apr 18, 2012 2:33 PM