Performance Tuning Tricks Oracle Olap Cubes

  1. Check if the available memory parameters for the DB SGA target are set high enough – this should be set at 35% of all RAM available to the db.
  2. OLAP_PAGE_POOL_SIZE should be set to 0 so that auto dynamic page pool is on and is managed by the database (will be set to 50% of PGA size). This is the default. However, if you have over 8G of memory available then you should set the parameter manually to a good value ,for data loading it is to set to 256MB and for multiple users querying concurrently, 64MB.
  3. Are you running RAC ? Can you try a single node to check if this is causing overhead. Try turning off parallelism for AW updates only
alter system set "_olap_parallel_update_threshold"=2147483647 scope=spfile;
alter system set "_olap_parallel_update_small_threshold"=2147483647 scope=spfile;
  1. Is your I/O throughput fast enough ? Check the block reads on each disk through Database Control
  2. Is your tablespace preallocated ? That is, have the blocks already been used in a warm environment ?
  3. A few more parameters that may need to be changed are:

increase db_writer_processes to improve I/O output and thus improve loads and updates.
SORTBUFFERSIZE should be increased since OLAP AWs use this parameter instead of SORT_AREA_SIZE for aggregation sorts. This can be increased in a session as follows:

exec DBMS_AW.EXECUTE('aw attach SCOTT.MYAW rwx');
exec DBMS_AW.EXECUTE('shw sortbuffersize'); < default is 262,411>
exec DBMS_AW.EXECUTE('SortBufferSize=10485760′);
exec DBMS_AW.EXECUTE('upd');
exec DBMS_AW.EXECUTE('commit');
exec DBMS_AW.EXECUTE('aw detach SCOTT.MYAW');

This parameter can be set permanently for the instance as follows:

alter system set "_olap_sort_buffer_size"=10485760 scope=spfile;

Note, the above is only an example; sortbuffersize can be set to be the value of the total number of tuples in a partition (largest for the AW).

Turn off Logging (REDO) or increase REDO Log Size to between 100M and 500M, and b. modify LOG_BUFFER parameter to 10M (for example) to allow for more efficient index lob creation, and c. move TEMP, UNDO and REDO logs to fastest disk.

Turn Parallel Update of AWs off by setting it to any high value e.g. SQL> alter system set “_olap_parallel_update_threshold”=2000000000

Change PARALLEL_MAX_SERVERS to the number of processors less 1 so that AWs use as many parallel slaves as available (normally this is set too high by default install) and have small uniform extents for TEMP (eg 256k)

AW Modelling: The AW should be logically partitioned and modeled well and then should also be physically partitioned as it improves update performance by reducing index lob contention. For example, create a physical partition for every logical partition you have, i.e. you have 60 months of data and have logically partitioned by month in the AW. Now create 60 physical partitions as follows: SQL> exec dbms_aw.execute(‘aw create scott.product_AW partitions 60′);


select dbal.owner||'.'||substr(dbal.table_name,4) awname, sum(dbas.bytes)/1024/1024 as mb, dbas.tablespace_name from dba_lobs dbal, dba_segments dbas where dbal.column_name = 'AWLOB' and dbal.segment_name = dbas.segment_name group by dbal.owner, dbal.table_name, dbas.tablespace_name order by dbal.owner, dbal.table_name


select vses.username||':'||vsst.sid username,, max(vsst.value) value
from v$sesstat vsst, v$statname vstt, v$session vses
where vstt.statistic# = vsst.statistic# and vsst.sid = vses.sid and
VSES.USERNAME LIKE ('ATTRIBDW_OWN') AND in ('session pga memory', 'session pga memory max', 'session uga memory','session uga memory max', 'session cursor cache count', 'session cursor cache hits', 'session stored procedure space', 'opened cursors current', 'opened cursors cumulative') and
vses.username is not null group by vsst.sid, vses.username, order by vsst.sid, vses.username,


select 'OLAP Pages Occupying: '|| round((((select sum(nvl(pool_size,1)) from v$aw_calc)) / (select value from v$pgastat where name = 'total PGA inuse')),2)*100||'%' info from dual union select 'Total PGA Inuse Size: '||value/1024||' KB' info from v$pgastat where name = 'total PGA inuse' union select 'Total OLAP Page Size: '|| round(sum(nvl(pool_size,1))/1024,0)||' KB' info from v$aw_calc order by info desc


select vs.username, vs.sid, round(pga_used_mem/1024/1024,2)||' MB' pga_used, round(pga_max_mem/1024/1024,2)||' MB' pga_max, round(pool_size/1024/1024,2)||' MB' olap_pp, round(100*(pool_hits-pool_misses)/pool_hits,2) || '%' olap_ratio from v$process vp, v$session vs, v$aw_calc va where session_id=vs.sid and addr = paddr