Creating Tablespaces for Analytic Workspaces

Before users begin creating analytic workspaces, you should create tablespaces that will be used for temporary and permanent storage of analytic workspaces. By default, these tablespaces are created in the SYS tablespace, which can degrade overall performance. Oracle OLAP makes heavy use of temporary tablespaces, so it is particularly important that they be set up correctly to prevent I/O bottlenecks.

These are some of the objects that Oracle OLAP stores in temporary tablespaces:

• The results of what-if analysis or other changes to the analytic workspace before they are committed to the database
• Output logs
• Views in a self-join
• Output of a table function when it exceeds 64KB

If possible, you should stripe the datafiles and temporary files across as many controllers and drives as are available.

Example 6-1 provides an example of a session in SQL*PLUS in which these tablespaces are created.

Example 6-1 Creating Tablespaces

The SQL commands in this example do the following:

• Create a tablespace named OLAPUNDO in a disk file named olapundo.f.
• Create and modify a rollback segment named OLAPSEG in the OLAPUNDO tablespace.
• Create a temporary tablespace named OLAPTEMP that uses up to four temporary disk files named temp1.f, temp2.f, temp3.f, and temp4.f. The additional disk files are located on separate physical disks (user2, user3, and user4).
• Grant the SCOTT user access rights to use OLAPTEMP.
• Create a tablespace named OLAPTS in up to three disk files named olapdf1.f, olapdf2.f, and olapdf3.f.

Following this example is an explanation of the statements beginning with "Creating a Tablespace for Rollbacks".

SQL>  CREATE TABLESPACE olapundo DATAFILE '/user1/oracle/datafiles/olapundo.f'
   2   SIZE 200M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM;

Tablespace created.

SQL> CREATE ROLLBACK SEGMENT olapseg TABLESPACE olapundo STORAGE (OPTIMAL 6M);

Rollback segment created.

SQL> ALTER ROLLBACK SEGMENT olapseg ONLINE;

Rollback segment altered.

SQL> CREATE TEMPORARY TABLESPACE olaptemp TEMPFILE
   2  '/user2/oracle/datafiles/temp1.f' SIZE 1024M REUSE 
   3  AUTOEXTEND ON NEXT 100M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

SQL> ALTER TABLESPACE olaptemp ADD TEMPFILE 
   2 '/user2/oracle/datafiles/temp2.f' SIZE 1024M REUSE 
AUTOEXTEND ON NEXT 100M MAXSIZE 4096,
   3 '/user3/oracle/datafiles/temp3.f' SIZE 1024M REUSE 
AUTOEXTEND ON NEXT 100M MAXSIZE 4096,
   4 '/user4/oracle/datafiles/temp4.f' SIZE 1024M REUSE 
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; 

Tablespace altered.

SQL> ALTER USER scott TEMPORARY TABLESPACE olaptemp;

User altered.

SQL> CREATE TABLESPACE olapts DATAFILE 
  2  '/user1/oracle/olapdf1.f' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M 
MAXSIZE 4096M,
  3  '/user2/oracle/olapdf2.f' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M 
MAXSIZE 4096M,
  4  '/user3/oracle/olapdf3.f' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M 
MAXSIZE UNLIMITED;

Tablespace created.

Creating a Tablespace for Rollbacks

The following SQL commands create a tablespace that Oracle OLAP uses to store changes to active analytic workspaces so that the changes can be rolled back if necessary.

CREATE TABLESPACE tablespacename DATAFILE 'pathname' SIZE size REUSE 
     AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM;

CREATE ROLLBACK SEGMENT segmentname TABLESPACE tablespacename 
     STORAGE (OPTIMAL size);

Where:

segmentname is the name of the segment.

pathname is the fully qualified file name.

size is an appropriate size for these tablespaces.

tablespacename is the name of the tablespace being defined.

Creating a Temporary Tablespace

Oracle OLAP uses temporary tablespace to maintain different generations of an analytic workspace. This allows it to present a consistent view of the analytic workspace when one or more users are reading it while the contents are being updated.

CREATE TEMPORARY TABLESPACE tablespacename TEMPFILE 'pathname1' 
   SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE size EXTENT MANAGEMENT LOCAL;
ALTER TABLESPACE tablespacename ADD TEMPFILE 
   'pathname2' SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE size,
   'pathname3' SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE size,
   'pathname4' SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE size;

ALTER USER username TEMPORARY TABLESPACE tablespacename;

Where:

segmentname is the name of the segment.

pathname1... pathname4 are the fully qualified file names of files that located on separate disk drives if possible.

size is an appropriate size for these tablespaces.

tablespacename is the name of the tablespace being defined.

username is a user or group that you want to grant access rights to this tablespace.

workspacename is the name of a new analytic workspace.

Creating Tablespaces for Analytic Workspaces

When a user creates an analytic workspace, it is created by default in the SYS tablespace. The following commands create a tablespace that a user or group of users can specify as the storage location for their analytic workspaces. Using this temporary tablespace instead of the SYS tablespace will result in better performance. Note that this tablespace can be located on a separate disk drive.

CREATE TABLESPACE tablespacename DATAFILE
     'pathname1' SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE size,
     'pathname2' SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE size,
     'pathname3' SIZE size REUSE AUTOEXTEND ON NEXT size MAXSIZE UNLIMITED;

Where:

segmentname is the name of the segment.

pathname1... pathname3 are the fully qualified names of files located on separate disk drives if possible.

size is an appropriate size for these tablespaces.

tablespacename is the name of the tablespace.

username is a user or group that you want to grant access rights to this tablespace.

workspacename is the name of a new analytic workspace.

After creating this tablespace, be sure to instruct the users with access rights to create their analytic workspaces with OLAP DML commands such as the following one. Otherwise, their analytic workspaces will still be created in the SYS tablespace, even though you have created a separate tablespace for this purpose.

AW CREATE workspacename TABLESPACE tablespacename

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:

COLUMN DBMS_LOB.GETLENGTH(AWLOB) HEADING "Bytes";
SELECT EXTNUM, DBMS_LOB.GETLENGTH(AWLOB) FROM AW$workspacename;

Where:

workspacename is the name of the analytic workspace.