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
db_cache_size	Half of physical memory
parallel_max_servers	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.
sessions	2.5 * maximum number of OLAP users

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:

   UTL_FILE_DIR=/users/oracle/OraHome1/olap

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

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.