2 Replies Latest reply on Jul 30, 2012 8:47 AM by 950522

    OLAP Cube comparison

      I have made two 11g OLAP cubes in Oracle AWM 11.2.0, but I was wondering if there is a way to gather statistics on the cubes, so I can compare them against each other.
      The aim is to create a number of cubes, all with different changes i.e. 1 would be compressed, 1 would contain twice the amount of dimensions so I can determine the scalability. I would like to know if I 'double the dimensions, does it double the cube size' etc. Additional information such as the CPU usage, refresh time, what happens if there is lots of 'nulls' in the data would also be required, as well as questions along those lines.
      I am quite new to the topic so any information would be grateful.

      I have found commands to run in the OLAP worksheet, such as:
      select sum(dbms_lob.getlength(awlob))/1024/1024 as mb from AW$SALESTRACK;
      Which displays the size of the workspace, but is there a list of commands or a way of gathering information on the cubes themselves, perhaps through OBIEE?

      Thank you very much
        • 1. Re: OLAP Cube comparison
          Jameson White-Oracle
          Hi Chris,

          If you're doing comparisons like this then I'd recommend storing your Cubes in different Analytic Workspaces. It may consume more space, however, you'll be better able to quantify your results.

          To monitor performance I'd recommend using AWR. Take a snapshot before and after your runs and generate an AWR Report using the cooresponding snap_id's. I've included a couple scripts that might help. If you need anything more granular please let us know.

          Determine Names and Size of Analytic Workspace Objects

          SET lines110 pages 500
          column partname FOR a50 hea AW_OBJECT
          column kb FOR 999,999,990

          BREAK ON REPORT;
          comp SUM lab Total OF kb ON REPORT;

          SELECT partname,
          ROUND(SUM(dbms_lob.getlength(awlob))/1024,0) KB
          FROM olaptrain.aw$salestrack
          WHERE extnum = 0
          GROUP BY partname
          ORDER BY kb;

          Verify Datatype State of Analytic Workspaces

          SET pages 500 lines 110

          BREAK ON REPORT;
          comp SUM lab "Total Disk:" OF mb ON REPORT;

          column awname format a35 heading "Analytic Workspace (Partitions)"
          column tablespace_name format a18 heading "Tablespace"
          column logging format a8 heading "Logging"
          column mb format 999,999,990.00 heading "On Disk MB"

          SELECT dl.owner||'.'||SUBSTR(dl.table_name,4)||' ('||COUNT(ds.segment_name)||')' awname,
          SUM(ds.bytes)/1024/1024 AS mb,
          FROM dba_lobs dl,
          dba_segments ds
          WHERE dl.column_name = 'AWLOB'
          AND dl.segment_name = ds.segment_name
          GROUP BY dl.owner,
          ORDER BY dl.owner,

          Performance Snapshot for Analytic Workspaces Sessions

          If you would like to determine how much PGA (in use and max), OLAP Page Pool
          and OLAP Pages Hit Miss Ratio for every User accessing Analytic Workspaces then
          consider using the script below.

          SET lines 110 pages 500
          column usn FOR a23 hea "USER (SID,SER,SVR)"
          column inst_id FOR 99 hea IN
          column pga_used FOR 9,990.9 hea "PGA MB|USED"
          column pga_max FOR 9,990.9 hea "PGA MB|MAX"
          column olap_pp FOR 9,990.9 hea "OLAP MB"
          column olap_hrate FOR 99.9 hea "OLAP|Hit %"

          BREAK ON REPORT;

          comp AVG lab AVG MIN lab MIN MAX lab MAX OF olap_hrate ON REPORT;
          comp AVG lab AVG SUM lab Total MIN lab MIN MAX lab MAX OF pga_used ON REPORT;
          comp AVG lab AVG SUM lab Total MIN lab MIN MAX lab MAX OF pga_max ON REPORT;
          comp AVG lab AVG SUM lab Total MIN lab MIN MAX lab MAX OF olap_pp ON REPORT;

          SELECT vs.username||' '||vs.sid||','||vs.serial#||','|| DECODE(server, 'DEDICATED', 'D', 'SHARED', 'S', 'U') usn,
          ROUND((pga_used_mem)/1024/1024,1) pga_used,
          ROUND((pga_max_mem) /1024/1024,1) pga_max,
          ROUND((pool_size) /1024/1024,1) olap_pp,
          ROUND(100 *((pool_hits)/((pool_hits)+(pool_misses))),1) olap_hrate
          FROM gv$process vp,
          gv$session vs,
          gv$aw_calc va
          WHERE va.session_id = vs.sid
          AND vp.addr = vs.paddr
          AND vp.inst_id = vs.inst_id
          AND vs.inst_id = va.inst_id
          ORDER BY vs.inst_id,

          Monitor Reads, Writes and Rows Processed in Analytic Workspaces

          SET lines 110
          column usn for a24 heading "User (SID,SERIAL#)"
          column temp_reads for 99,999,999
          column lob_reads for 99,999,999
          column cache_wrs for 99,999,999
          column rows_procd for 99,999,999

          SELECT username||' ('||sid||','||serial#||')' usn,
          SUM(temp_space_reads) temp_reads,
          SUM(lob_reads) lob_reads,
          SUM(cache_writes) cache_wrs,
          SUM(rows_processed) rows_procd
          FROM v$aw_olap vo,
          v$aw_calc vc,
          v$aw_longops vl
          WHERE sid = vo.session_id
          AND vo.session_id = vc.session_id
          GROUP BY username,
          1 person found this helpful
          • 2. Re: OLAP Cube comparison
            Thank you, it was very helpful. However the ‘Performance Snapshot for Analytic Workspaces Sessions’ and ‘Monitor Reads, Writes and Rows Processed in Analytic Workspaces’ are not returning any data, are there aspects of the code that will need to be changed to relate to my AW?
            Also is there a way to gather additional information such as CPU statistics?
            Many thanks