Skip Headers

Oracle9i OLAP User's Guide
Release 2 (

Part Number A95295-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF

Initialization Parameters for Oracle OLAP

Several packages described in this guide require that utl_file_dir be set. This parameter enables the RDBMS to write to a file.

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

Table 6-1  Database Performance Initialization Parameter Settings
Parameter Setting


Half of physical memory


The number of processors minus one

This parameter limits the number of processes that are used for a parallel update. The number of parallel processes is also dependent on the number of analytic workspace extension files that are being updated.


2.5 * maximum number of OLAP users

See Also:

Oracle9i SQL Reference for information about these parameters.

Take the following steps to set system parameters:

  1. Open the initsid.ora parameters file in a text editor.

    The parameters file is located in $ORACLE_HOME/admin/sid/pfile, where sid is the system identifier as defined in $ORACLE_HOME/network/admin/tnsnames.ora.

  2. 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 OraHome1/olap directory:


  3. Stop and restart the database, using commands such as the following. Be sure to identify the parameters file in the STARTUP command.
    sqlplus '/ as sysdba'
    shutdown immediate
    startup pfile=/users/oracle/OraHome1/admin/rel9dw/pfile/initrel9dw.ora


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 an OLAP session.

The OLAP paging cache is allocated at the start of an OLAP session and released when the user exits the database. An OLAP session can be initiated by the OLAP_TABLE function, the DBMS_AW PL/SQL package, or via command line in OLAP Worksheet.

The minimum value of OLAP_PAGE_POOL_SIZE is 2 MB. The default value is 32 MB.

The OLAP paging cache 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 in multi-threaded server mode (MTS), the UGA is part of the Shared Global Area (SGA).

When the database is running in dedicated mode, you can reset the value of OLAP_PAGE_POOL_SIZE in an ALTER SESSION statement. If you decrease the value, you should first do an UPDATE in the analytic workspace and a COMMIT in the database. If you increase the value to a size greater than the available memory, OLAP_PAGE_POOL_SIZE remains the same.

If OLAP_PAGE_POOL_SIZE is greater than available memory, OLAP session initialization will fail.

For performance reasons, it is generally preferable to use a small OLAP paging cache and a larger DB_CACHE_SIZE. An OLAP paging cache of 4 MB is fairly typical, with 2 MB used for systems with limited memory resources.