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
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;
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,
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_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,
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,
FROM v$aw_olap vo,
WHERE sid = vo.session_id
AND vo.session_id = vc.session_id
GROUP BY username,
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?