4 Replies Latest reply on Jul 27, 2012 5:01 PM by 952065

    OLAP Query Performance


      I am new to Oracle AWM/OLAP, so please bear with me. I have an OLAP cube with 6 dimensions. It builds fine within a reasonable time without any issues. When I query out about 95 calculated measures specifying a day for time dimension and specifying other stuff for the other dimensions, the query comes back within reasonable time, a couple of seconds. But if I do the same query specifying a day, but leave 1 dimension open for example because I want to see all rows (about 5000), the query takes about 5 minutes to run. Why is that? Is it because 95 calculated measures has to be done on the fly for about 5000 rows? Is there anyway to speed it up?

      Thanks in advance

      System Info:_
      Oracle 11g
      Cube Partition by Month
      Top Partition = 0
      Bottom Partition = 35
      Cube is compressed
      Fact Table about 4MM
        • 1. Re: OLAP Query Performance
          The 95 calculated measues do have to be calculated on the fly, but it would depend on what the measures are and how they are defined. As an example, a measure defined as 'sales * 100' would be much faster than a measure defined as 'sales year to date'.

          You say there are 5000 rows in the result set, but how many members are there in the 'open dimension'? If it is substantially more than 5000 members, then there may be a looping problem (where the code is calculating the measure for all members of the dimension, not just the 5000 rows you see).

          Finally, do you really need to see the results of all 95 measures at the same time? If you run "SELECT *" from the cube view, then you will see all 95. But what happens if you choose only a subset of measures in an explicit select list? The code should calculate only the measures in the select list.
          • 2. Re: OLAP Query Performance
            Hi David,

            Thanks for the reply. Here are some answers to your questions.

            1) the 95 measures are period to date or parallel period
            2) about 84K. How would I know if it is a looping issue? I am just writing a simple SQL using the views generated by AWM, specifying all levels in the hierarchies in the dimensions when I join to it.
            3) Yes, I need to see all 95 measures. The cube actually has a lot more than that, in the hundreds. I do get pretty good performance gains if I remove some of the measures.

            FYI, I have about 20ish stored measures.
            Explain Plan Cost=8K, I/O Cost = 2, CPU Cost = 210000002750
            • 3. Re: OLAP Query Performance
              Please run the following in a single SQL session.
              exec dbms_cube_log.table_create(dbms_cube_log.type_operations)
              truncate table cube_operations_log;
              exec dbms_cube_log.enable(dbms_cube_log.type_operations, dbms_cube_log.target_table, dbms_cube_log.level_high);
              Then run your query in the same session. The query below should then give some diagnostic information.
              set linesize 200
              column NAME_VALUE_DETAILS format a200
                 RPAD(NAME, MAX(LENGTH(NAME)) OVER()) 
                 || '      ' 
                 || RPAD(VALUE, MAX(LENGTH(VALUE)) OVER()) 
                 || '      '
                 || DETAILS NAME_VALUE_DETAILS 
                NAME LIKE '%DIM%' 
                OR NAME LIKE '%ROW%' 
                OR NAME LIKE '%LOOP%' 
                OR NAME LIKE '%SUPPRE%' 
                OR NAME LIKE '%TIME%' 
                OR NAME LIKE '%PAG%' 
              ORDER BY TIME
              • 4. Re: OLAP Query Performance

                I ran the diagnostic query you sent. Below is the results. FYI, I hid all my Schema/AWM/Cube/Dimension name in results below.

                1 SELECT
                2 RPAD(NAME, MAX(LENGTH(NAME)) OVER())
                3 || ' '
                4 || RPAD(VALUE, MAX(LENGTH(VALUE)) OVER())
                5 || ' '
                6 || DETAILS NAME_VALUE_DETAILS
                7 FROM CUBE_OPERATIONS_LOG
                8 WHERE
                9 NAME LIKE '%DIM%'
                10 OR NAME LIKE '%ROW%'
                11 OR NAME LIKE '%LOOP%'
                12 OR NAME LIKE '%SUPPRE%'
                13 OR NAME LIKE '%TIME%'
                14 OR NAME LIKE '%PAG%'
                15* ORDER BY TIME

                STAT_COMP_TIME 0.922
                NUM_ROWS 300000000
                AVE_ROW_LEN 2260
                INITIALIZE_TIME 0.00005

                FILTER_PRUNING_DIMEN <SCHEMA_NAME>.<AWM_NAME>!<TIME_DIMENSION> 2 predicates pruned
                FILTER_PRUNING_DIMEN <SCHEMA_NAME>.<AWM_NAME>!<1st_DIMENSION> 2 predicates pruned
                FILTER_PRUNING_DIMEN <SCHEMA_NAME>.<AWM_NAME>!<2nd_DIMENSION> 1 predicates pruned
                FILTER_PRUNING_DIMEN <SCHEMA_NAME>.<AWM_NAME>!<3rd_DIMENSION> 1 predicates pruned
                LIMITS_TOTAL_TIME 4 0.03701
                LOOP_ENGINE_INIT 0.02048
                CUR_DIM_LENGTH 10468 <AWM_NAME>!<1st_DIMENSION>
                CUR_DIM_LENGTH 21831 <AWM_NAME>!<3rd_DIMENSION>
                CUR_DIM_LENGTH 3914 <AWM_NAME>!<2nd_DIMENSION>
                LOOP_TIME 4.25929
                FETCH_TIME 123.31359
                ROWS_READ 5836
                ROWS_RETURNED 4324
                NULLTRACK_SUPPRESSED 1512
                ROWS_FAILED_FILTER 0
                PAGING_HITS 29057646
                PAGING_MISSES 3579059
                PAGEPOOLSIZE 206686032
                TOTAL_TIME 255.44819 5836 ROWS READ
                LOOKUP_TIME 0.00000 0 ROWS
                FILTER_TIME 0.00235
                TOTAL_PAGING_HITS 29062975
                TOTAL_PAGING_MISSES 3579070

                29 rows selected.

                Elapsed: 00:00:02.32