Creating Tablespaces for Analytic Workspaces - OLAP

Before you create an analytic workspace, you should create undo, permanent, and temporary tablespaces dedicated to their use. Analytic workspaces are created in the user's default tablespace, unless the user specifies otherwise. The default tablespace for all users is set initially to SYS. Creating analytic workspaces in the SYS tablespace can degrade overall performance. Similarly, analytic workspaces should not share tablespaces with relational tables, especially not the source star or snowflake schema.

Oracle OLAP makes heavy use of temporary tablespaces, so it is particularly important that they be set up correctly to prevent I/O bottlenecks. The tablespaces that you set up for use by Oracle OLAP are used by SQL for tasks such as creating and maintaining OLAP Catalog metadata and views of workspace data, in addition to their use by analytic workspaces. If possible, you should stripe the datafiles and temporary files across as many controllers and drives as are available.

Creating an UNDO Tablespace

The following SQL commands create an undo tablespace.

Where:
tablespace is the name of the tablespace
pathname is the fully qualified file name
size is an appropriate number of bytes
For example:

After creating the undo tablespace, change your system parameter file to include these settings, then restart the database as described in "Initialization Parameters for Oracle OLAP".

UNDO_TABLESPACE=tablespace UNDO_MANAGEMENT=AUTO

Creating a Permanent Tablespace for Analytic Workspaces

When a user creates an analytic workspace, it is created in the user's default tablespace, which is initially set to the SYS tablespace. The following SQL statements create a tablespace appropriate for storing analytic workspaces.

Where:
tablespace is the name of the tablespace
pathname is the fully qualified file name
size is an appropriate number of bytes
username is the name of a database user

For example:

If your computer has multiple disks, then you can stripe the tablespace across them. The next example shows SQL statements that distribute the GLO tablespace across three physical disks:

Creating a Temporary Tablespace for Analytic Workspaces

Oracle OLAP uses temporary tablespace to store all changes to the data in an analytic workspace, whether the changes are the result of a data load, what-if analysis, forecasting, aggregation, or some other analysis. An OLAP DML UPDATE command moves the changes into the permanent tablespace and clears the temporary tablespace.

Oracle OLAP also uses temporary tablespace to maintain different generations of an analytic workspace. This enables it to present a consistent view of the analytic workspace when one or more users are reading it while the contents are being updated. This usage creates numerous extensions within the tablespace, so be sure to specify a small EXTENT MANAGEMENT size.

The following commands create a temporary tablespace suitable for use by Oracle OLAP.

Where:
pathname is a fully qualified file name
size is an appropriate number of bytes
tablespace is the name of the tablespace
username is a database user

For example:

You can stripe temporary tablespaces across several disks the same as permanent tablespaces. The next example shows the GLOTMP temporary tablespace striped across three physical disks.

Querying the Size of an Analytic Workspace

To find out the size of the tablespace extensions for a particular analytic workspace, use the following SQL statements:

Where:
awname is the name of the analytic workspace.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics