Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


Improving OLAP Performance in Oracle Database 10g Release 1
April 5, 2005
Mark Rittman

Oracle's Anthony Waite posted some useful guidelines today on the OTN OLAP Forum, for improving the OLAP performance in Oracle Database 10g Release 1. According to the posting (I've reformatted it slightly to make it more readable):

"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.

* Turn Logging off prior to commencing build.


* Turn Logging on after build completes.


  1. 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.

  1. 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.

  1. 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.

  1. 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.

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


  1. 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;"


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational