"Here are some guidelines for improving the OLAP performance in Oracle Database 10g Release 1.

1. Turn off LOGGING (REDO) during builds to improve data insertion performance.

Disable Logging during build to improve overall load performance. Once load is complete turn it on.

If you choose to set NOLOGGING for the LOB segment (of the AW$ table containing your Analytic Workspace) check out MOSC 1058851.6 for information pertaining to event 10359 which can reduce I/O for frequently updated NOLOGGING LOBs. http://MOSC.oracle.com/MOSC/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1058851.6

* Turn Logging off prior to commencing build.

ALTER TABLE GLOBAL.AW$GLOBAL MODIFY LOB (awlob) (CACHE READS NOLOGGING);

* Turn Logging on after build completes.

ALTER TABLE GLOBAL.AW$GLOBAL MODIFY LOB (awlob) (CACHE READS LOGGING);

2. Increase REDO Log size and log_buffer parameter to reduce log switch waits and improve overall time.

Ask the DBA to increase this to somewhere between 100M and 500M from the default of 10M. Use ADDM to determine the ideal size.

3. Build Analytic Workspaces (LOBs) in separate preallocated Tablespaces to improve overall data load performance.

This can reduce device contention while preallocating space avoids allocation during inserts and updates.

4. Move TEMP, UNDO and REDO Logs to fastest disks to improve overall build performance.

Ask the DBA to place TEMP, UNDO and REDO Logs on fastest disks. No RAID5. Use RAW whenever possible and consider RAID10 or 0+1. RAID 5 can severely affect performance on highly updated databases.

5. Specify better OLAP DML for SQL IMPORT or SQL INSERT DIRECT to improve analytic workspace load performance.

SQL INSERT DIRECT is up to 53x faster than SQL INSERT.

NOTE: Source tables should be ordered by the inverse of the target object's dimensionality when loading data from relational sources into an analytic workspace.

SQL IMPORT is up to 7x faster than SQL FETCH.

6. Use AW TRUNCATE instead of AW DELETE if you wish to keep the analytic workspace name. Performs better with less overhead.

EXEC DBMS_AW.EXECUTE('AW TRUNCATE SCOTT.EMPAW');

7. Use the OLAP_PAGE_POOL_SIZE hidden parameters to fine tune overall analytic workspace performance.

Before using or recommending these parameters you should fully understand how they work. The defaults should work fine in nearly all cases.

* olap_page_pool_size

Size of the olap page pool in bytes. Leaving it unset or setting it to 0 would turn on auto dynamic page pool management.

* _olap_page_pool_shrink_rate

Rate of pool decrease as a percentage of current pool size. The default is 50%.

* _olap_page_pool_expand_rate

Rate of pool increase as a percentage of current pool size. The default is 20%.

* _olap_page_pool_hi

Pool size high watermark, a percentage of PGA Aggregate Target. The default is 50%. It won't grow beyond that.

* _olap_page_pool_low

Pool size low watermark in bytes. It won't shrink below that point. The default is 256k.

* _olap_page_pool_pressure

The point at which OLAP will shrink the pool, a percentage of PGA Aggregate Target. The default is 90%.

* _olap_page_pool_hit_target

Page cache hit ratio target. Default is 100%. (Not much need to change.)

NOTE: These hidden parameters don't apply to MTS mode.

To view these parameters run the following select statement as SYS:

col par format a28 heading 'Parameter' col val format a8 heading 'Value' col des format a36 heading 'Description' select ppi.ksppinm par, psv.ksppstvl val, ksppdesc des from sys.x$ksppi ppi, sys.x$ksppsv psv where ppi.indx = psv.indx and ppi.ksppinm like '%olap_page_pool%' order by ppi.ksppinm;"