Initialization Parameters for Oracle OLAP - OLAP

Table identifies the parameters that affect the performance of Oracle OLAP. Alter your server parameter file or init.ora file to these values, then restart your database instance. You can monitor the effectiveness of these settings and adjust them as necessary.

Initial Settings for Database Parameter Files

Initial Settings for Database Parameter Files

Initial Settings for Database Parameter Files

Procedure: Setting System Parameters for OLAP

Take the following steps to set system parameters:

  1. Open the initsid.ora initialization file in a text editor.
    The initialization file is located in $ORACLE_HOME/admin/sid/pfile, where sid is the system identifier as defined in
  2. $ORACLE_HOME/network/admin/tnsnames.ora.
  3. Add or change the settings in the file.
    For example, you might enter a command like this so that Oracle can write files to the olapscripts directory:
  4. UTL_FILE_DIR=/users/oracle/olapscripts
  5. Stop and restart the database, using commands such as the following. Be sure to identify the initialization file in the STARTUP command.
  6. SQLPLUS '/ AS SYSDBA' SHUTDOWN IMMEDIATE STARTUP pfile=$ORACLE_HOME/admin/rel10g/pfile/initrel10g.ora

About the OLAP_PAGE_POOL_SIZE Setting

OLAP_PAGE_POOL_SIZE is an initialization parameter that is specific to Oracle OLAP. This parameter specifies in bytes the maximum size of the paging cache to be allocated to each OLAP session. The minimum value of OLAP_PAGE_POOL_SIZE is 2 MB. The default value is 32 MB.
These are the basic guidelines for setting OLAP_PAGE_POOL_SIZE:

  • In the database initialization file, set OLAP_PAGE_POOL_SIZE to a value based on the maximum number of simultaneous OLAP users. The setting should be in the order to 2-8MB; 4MB is typical. Larger is better, but remember that each user is allocated that amount.
  • For data loads, use a SQL ALTER SESSION statement to enlarge the OLAP page pool as much as possible just for the duration of the load, on the basis that the page pool is not shared with other users at this time. The setting should be in the order of 100-400MB, but smaller than DB_CACHE_SIZE.

The OLAP page pool is allocated at the start of an OLAP session and released when the user closes the session. An OLAP session can be initiated by the OLAP_TABLE function, the DBMS_AWM PL/SQL package, or using the command line in OLAP Worksheet.
The OLAP page pool is allocated from the User Global Area (UGA). When the database is running in dedicated mode, the UGA is part of the Process Global Area (PGA). When the database is running as a shared server process, the UGA is part of the Shared Global Area (SGA).

When the OLAP page pool is full, it uses the DB cache as a swap space. This in-memory swapping is a relatively fast operation. When the DB cache is full, it swaps to disk, which is a relatively slow operation. If the DB cache must swap to disk frequently, then performance will suffer significantly.


PGA_AGGREGATE_TARGET is used by SQL statements, particularly when performing SELECT statements with GROUP BY and ORDER BY clauses. It is not used by the OLAP engine. However, PGA_AGGREGATE_TARGET can affect the performance of the BI Beans when selecting data from relational tables. If your Oracle Database supports this type of application, set GA_AGGREGATE_TARGET initially to 200-400MB, and use the database performance monitoring tools to recommend adjustments.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

OLAP Topics