12 Administering Oracle OLAP

This chapter describes the various administrative tasks that are associated with Oracle OLAP. It contains the following topics:

Setting Database Initialization Parameters
Storage Management
Security of Multidimensional Data in Oracle Database
Dictionary Views and System Tables
Partitioned Cubes and Parallelism
Monitoring Analytic Workspaces
Backup and Recovery
Export and Import

Setting Database Initialization Parameters

Table 12-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. You can monitor the effectiveness of these settings and adjust them as necessary.

See Also:

Oracle Database Performance Tuning Guide for information about tuning parameter settings
Oracle Database Reference for descriptions of individual parameters

Table 12-1 Initial Settings for Database Parameter Files

Parameter	Default Value	Recommended Setting	Description
`JOB_QUEUE_PROCESSES`	0	Number of CPUs, plus one additional process for every three CPUs; in a multi-core CPU, each core counts as a CPU For example, `JOB_QUEUE_PROCESSES=5` for a four-processor computer	Controls the degree of parallelism in OLAP builds
`PGA_AGGREGATE_TARGET`	10 MB or 20% SGA	50% of physical memory to start, then tune as indicated by performance statistics
`SGA_TARGET`	0	25% or less of physical memory to start, then tune as indicated by performance statistics
`SESSIONS`	Derived	2.5 * maximum number of simultaneous OLAP users	Provides sufficient background processes for each user
`UNDO_MANAGEMENT`	`MANUAL`	`AUTO`	Specifies use of an undo tablespace
`UNDO_TABLESPACE`	Derived	Name of the undo tablespace, which must already be defined	Identifies the undo tablespace defined for OLAP use, as shown in "Creating an Undo Tablespace"

To set the system parameters:

Open the init.ora initialization file in a text editor.
Add or change the settings in the file.
Stop and restart the database, using commands such as the following. Be sure to identify the initialization file in the STARTUP command.
```
SQLPLUS '/ AS SYSDBA'
SHUTDOWN IMMEDIATE
STARTUP pfile=$ORACLE_BASE/admin/orcl/pfile/init.ora.724200516420
```

Parameter Settings for BI Beans

OracleBI Beans performs best when the configuration parameters for the database are optimized for its use. During installation of Oracle Database, an OLAP configuration table is created and populated with ALTER SESSION commands that have been tested to optimize the performance of OracleBI Beans. Each time OracleBI Beans opens a session, it executes these ALTER SESSION commands.

If a database instance is being used only to support Java applications that use OracleBI Beans, then you can modify your server parameter file or init.ora file to include these settings. Alternatively, you might want to include some of the settings in the server parameter file and leave others in the table, depending upon how your database instance is going to be used. These are your choices:

Keep all of the parameters in the configuration table, so that they are set as part of the initialization of a OracleBI Beans session. This method fully isolates these configuration settings solely for OracleBI Beans. (Default)
Add some of the configuration parameters to the server parameter file or init.ora file, and delete those rows from the configuration table. This is useful if your database is being used by other applications that require the same settings.
Add all of the configuration parameters to the server parameter file or init.ora file, and delete all rows from the configuration table. This is the most convenient if your database instance is being used only by OracleBI Beans.

Regardless of where these parameters are set, you should check the Oracle Technology Network for updated recommendations.

See Also:

Oracle Database SQL Reference for descriptions of initialization parameters that can be set by the ALTER SESSION command

Storage Management

Analytic workspaces are stored in the owner's default tablespace, unless the owner specifies otherwise. All tablespaces for OLAP use should specify EXTENT MANAGEMENT LOCAL. Tablespaces created using default parameters may use resources inefficiently. You should create undo, permanent, and temporary tablespaces that are appropriate for storing analytic workspaces.

Creating an Undo Tablespace

Create an undo tablespace with the EXTENT MANAGEMENT LOCAL clause, as shown in this example:

CREATE UNDO TABLESPACE olapundo DATAFILE '$ORACLE_BASE/oradata/undo.dbf'
    SIZE 64M REUSE AUTOEXTEND ON NEXT 8M
    MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

After creating the undo tablespace, change your system parameter file to include the following settings, then restart the database as described in "Setting Database Initialization Parameters".

UNDO_TABLESPACE=tablespace
UNDO_MANAGEMENT=AUTO

Creating Permanent Tablespaces for OLAP Use

Each dimensional object occupies at least one extent. A fixed extent size may waste most of the allocated space. For example, if an object is 64K and the extents are set to a uniform size of 1M (the default), then only a small portion of the extent is used.

Create permanent tablespaces with the EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO clauses, as shown in this example:

CREATE TABLESPACE glo DATAFILE '$ORACLE_BASE/oradata/glo.dbf'
   SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Creating Temporary Tablespaces for OLAP Use

Oracle OLAP uses the temporary tablespace to store all changes to the data in a cube, whether the changes are the result of a data load or data analysis. Saving the cube moves the changes into the permanent tablespace and clears the temporary tablespace.

This usage creates numerous extents within the tablespace. A temporary tablespace suitable for use by Oracle OLAP should specify the EXTENT MANAGEMENT LOCAL clause and a UNIFORM SIZE clause with a small size, as shown in this example:

CREATE TEMPORARY TABLESPACE glotmp TEMPFILE '$ORACLE_BASE/oradata/glotmp.tmp'
   SIZE 50M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

Spreading Data Across Storage Resources

Oracle Database provides excellent storage management tools to simplify routine tasks. Automatic Storage Management (ASM) provides a simple storage management interface that virtualizes database storage into disk groups. You can manage a small set of disk groups, and ASM automates the placement of the database files within those disk groups.

ASM spreads data evenly across all available storage resources to optimize performance and utilization. After you add or drop disks, ASM automatically rebalances files across the disk group.

Because OLAP is part of Oracle Database, you can use ASM to manage both relational and dimensional data.

ASM is highly recommended for analytic workspaces. A system managed with ASM is faster than a file system and easier to manage than raw devices. ASM optimizes the performance of analytic workspaces both on systems with Oracle RAC and those without Oracle RAC.

However, you do not need ASM to use Oracle OLAP. You can still spread your data across multiple disks, just by defining the tablespaces like in this example:

CREATE TABLESPACE glo DATAFILE 
   'disk1/oradata/glo1.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1024M
   EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE glo ADD DATAFILE 
   'disk2/oradata/glo2.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M MAXSIZE 1024M,
   'disk3/oradata/glo3.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 8M 
      MAXSIZE UNLIMITED;

Security of Multidimensional Data in Oracle Database

Your company's data is a valuable asset. The information must be secure, private, and protected. Analytic data is particularly vulnerable because it is highly organized, easy to navigate, and summarized into meaningful units of measurement.

When you use Oracle OLAP, your data is stored in the database. It has the security benefits of Oracle Database, which leads the industry in security. You do not need to expose the data by transferring it to a standalone database. You do not need to administer security on a separate system. And you do not need to compromise your data by storing it in a less secure environment than Oracle Database.

Security Management

Because you have just one system to administer, you do not have to replicate basic security tasks such as these:

Creating user accounts
Creating and administering rules for password protection
Securing network connections
Detecting and eliminating security vulnerabilities
Safeguarding the system from intruders

The cornerstone of data security is the administration of user accounts and roles. Users open a connection with Oracle Database with a user name and password, and they have access to both dimensional and relational objects in the same session.

Users by default have no access rights to an analytic workspace or any other data type in another user's schema. The owner or an administrator must grant them, or a role to which they belong, any access privileges.

Granting Querying Privileges

To access the cubes in an analytic workspace, users must have the SELECT privilege on the table in which the analytic workspace is stored. The name of the table is the name of the analytic workspace with an AW$ prefix. For example, the GLOBAL analytic workspace is stored in the AW$GLOBAL relational table.

To access the relational views of dimensional objects, users must have SELECT privileges explicitly on those views.

Dictionary Views and System Tables

Oracle Database data dictionary views and system tables contain extensive information about analytic workspaces.

Static Data Dictionary Views

Among the static views of the database data dictionary are several that provide information about analytic workspaces. Table 12-2 provides brief descriptions of them. All data dictionary views have corresponding DBA and USER views.

Table 12-2 Static Data Dictionary Views for OLAP

View	Description
`ALL_AWS`	Describes all analytic workspaces accessible to the current user.
`ALL_AW_OBJ`	Describes the current objects in all analytic workspaces accessible to the current user.
`ALL_AW_PROP`	Describes the properties defined in all analytic workspaces accessible to the current user.
`ALL_AW_PS`	Describes the page spaces currently in use by all analytic workspaces accessible to the current user.

See Also:

"Querying the Active Catalog" for a list of views that describe OLAP dimensional objects
Oracle Database Reference for full descriptions of all data dictionary views

System Tables

The SYS user owns several tables associated with analytic workspaces. Table 12-3 provides brief descriptions.

Important:

These tables are vital for the operation of Oracle OLAP. Do not delete them or attempt to modify them directly without being fully aware of the consequences.

Table 12-3 OLAP Tables Owned By SYS

Table	Description
`AW$`	Maintains a record of all analytic workspaces in the database, recording its name, owner, and other information.
`AW$AWCREATE`	Stores the `AWCREATE` analytic workspace, which contains programs for using OLAP Catalog metadata in Oracle Database 10g Release 10.1.0.2 and earlier releases. It exists only for backward compatibility.
`AW$AWCREATE10G`	Stores the `AWCREATE10G` analytic workspace, which contains programs for using OLAP Catalog metadata in Oracle Database 10g Release 10.1.0.3. The OLAP Catalog is not used by later releases. It exists only for backward compatibility.
`AW$AWMD`	Stores the `AWMD` analytic workspace, which contains programs for creating metadata catalogs.
`AW$AWREPORT`	Stores the `AWREPORT` analytic workspace, which contains a program named `AWREPORT` for generating a summary space report.
`AW$AWXML`	Stores the `AWXML` analytic workspace, which contains programs for creating and managing analytic workspaces for Oracle Database 10g Release 10.1.0.4 and later.
`AW$EXPRESS`	Stores the `EXPRESS` analytic workspace. It contains objects and programs that support basic operations. `EXPRESS` is used any time a session is open.
`AW_OBJ$`	Describes the objects stored in analytic workspaces.
`AW_PRG$`	Stores program data. Not currently used.
`AW_PROP$`	Stores analytic workspace object properties.
`PS$`	Maintains a history of all page spaces. A page space is an ordered series of bytes equivalent to a file. Oracle OLAP manages a cache of workspace pages. Pages are read from storage in a table and written into the cache in response to a query. The same page can be accessed by several sessions. The information stored in `PS$` enables Oracle OLAP to discard pages that are no longer in use, and to maintain a consistent view of the data for all users, even when the workspace is being modified during their sessions. When changes to a workspace are saved, unused pages are purged and the corresponding rows are deleted from `PS$`.

Analytic Workspace Tables

Analytic workspaces are stored in tables in the Oracle database. The names of these tables always begin with AW$.

For example, if the GLOBAL user creates two analytic workspaces, one named MARKETING and the other named FINANCIALS, then these tables are created in the GLOBAL schema:

AW$FINANCIALS
AW$MARKETING

The tables store all of the object definitions and data.

Build Logs

When submitting a maintenance task to the job queue, be sure to note the job number so that you can verify that the job completed successfully. Runtime messages are stored in a table named XML_LOAD_LOG, which is owned by OLAPSYS. You must have either the OLAP_USER or the OLAP_DBA role to access this file.

Messages in XML_LOAD_LOG are identified by the digits in the job number. The following SQL statement returns the messages for job 54:

SELECT xml_message FROM olapsys.xml_load_log WHERE xml_loadid='54';

You can manage these jobs using tools such as Oracle Enterprise Manager Scheduler or the DBMS_SCHEDULER PL/SQL package. Example 12-1 shows a sample log file.

Partitioned Cubes and Parallelism

Cubes are often partitioned to improve build and maintenance times. For information about creating a partitioned cube, refer to "Choosing a Data Storage Strategy".

Creating and Dropping Partitions

The OLAP engine automatically creates and drops partitions as part of data maintenance, as members are added and deleted from the partitioning dimension.

For example, assume that in the sample Global analytic workspace, the Units cube is partitioned on the Time dimension, using the Calendar hierarchy, and at the Calendar Quarter level. The OLAP engine creates a partition for each Calendar Quarter and its children. The default top partition contains Calendar Years and all members of the Fiscal hierarchy. If Global has three years of data, then the Units cube has 13 partitions: Four bottom partitions for each Calendar Year, plus the top partition.

A data refresh typically creates new time periods and deletes old ones. Whenever a Calendar Quarter value is loaded into the Time dimension, a corresponding new partition is added to the cube. Whenever a Calendar Quarter value is deleted from the Time dimension, the corresponding empty partition is deleted from the cube.

Parallelism

You can improve the performance of data maintenance by enabling parallel processing. There are two levels of parallelism:

Parallel job execution: Loading and aggregating the data using multiple processes.
Parallel update: Moving the data from temporary to permanent tablespaces using multiple processes.

This number of parallel processes is controlled by these factors:

The number of objects that can be aggregated in parallel. Each cube and each partition (including the top partition) can use a separate process.

You can control the number of partitions in a cube on the Implementation Details tab of the cube property sheet in Analytic Workspace Manager.
The number of simultaneous database processes the user is authorized to run.

This number is controlled by the JOB_QUEUE_PROCESSES parameter. The setting for this parameter is based on the number of processors, as described in "Setting Database Initialization Parameters". You can obtain the current parameter setting with the following SQL command:
```
SHOW parameter job_queue_processes
```
For parallel update, the number of processes you allocate to the job. You can specify the number of processes in the Maintenance Wizard of Analytic Workspace Manager when specifying the task processing options.

Suppose that a cube is partitioned on the Quarter level of Time, and the cube contains three years of data. The cube has 3*4=12 bottom partitions, JOB_QUEUE_PROCESSES is set to 8, and you set the parallelism option to 4 for the build. Oracle Database processes the cube in this way:

Load and build the dimensions of the cube serially using a single process.
Load and build the 12 bottom partitions in parallel using 4 processes. As soon as one process finishes, another begins until all 12 are complete.

This cube could use the 8 processes allowed by JOB_QUEUE_PROCESSES, but it is limited to 4 by the build setting.
Load and build the top partition.

Oracle Database allocates the specified number of processes regardless of whether all of them can be used simultaneously at any point in the job. For example, if your job can use up to three processes, but you specify five, then two of the processes allocated to your job cannot be used by it or by any other job.

If Oracle Database is installed with Oracle RAC, then a script submitted to the job queue is distributed across all nodes in the cluster. The performance gains can be significant. For example, a job running on four nodes in a cluster may run up to four times faster than the same job running on a single computer.

The build log is stored in a file named XML_LOAD_LOG, which is owned by OLAPSYS. Example 12-1 shows excerpts from the job log for a completed build that used two parallel processes.

Example 12-1 Job Log Verifying Parallel Processing

SQL> SELECT xml_message FROM olapsys.xml_load_log WHERE xml_loadid='4';
 
XML_MESSAGE
-------------------------------------------------------------------------------------------------
11:13:02 Job# AWXML$_4 to Build(Refresh) Analytic Workspace GLOBAL.GLOBAL Submitted to the Queue.
11:13:05 Started Build(Refresh) of GLOBAL.GLOBAL Analytic Workspace.
11:13:10 Attached AW GLOBAL.GLOBAL in RW Mode.
11:13:10   Started Loading Dimensions.
11:13:20     Started Loading Dimension Members.
               .
               .
               .
11:13:27     Finished Loading Dimension Members.
11:13:27     Started Loading Hierarchies.
               .
               .
               .
11:13:33     Finished Loading Hierarchies.
11:13:33     Started Loading Attributes.
               .
               .
               .
11:13:44     Finished Loading Attributes.
11:13:44   Finished Loading Dimensions.
11:13:44   Started Updating Partitions.
11:13:46   Finished Updating Partitions.
11:14:37 Detached AW GLOBAL.GLOBAL.
11:14:37 Starting Parallel Processing.
11:14:42 Attached AW GLOBAL.GLOBAL in MULTI Mode.
11:14:44     Started Load of Measures: UNIT_PRICE, UNIT_COST from Cube PRICE_AND_COST_CUBE.CUBE.
11:14:45     Finished Load of Measures: UNIT_PRICE, UNIT_COST from Cube PRICE_AND_COST_CUBE.CUBE.
             Processed 5046 Records. Rejected 0 Records.
11:14:45     Started Auto Solve for Measures: UNIT_COST from Cube PRICE_AND_COST_CUBE.CUBE.
11:14:46     Finished Auto Solve for Measures: UNIT_COST from Cube PRICE_AND_COST_CUBE.CUBE.
11:14:46     Started Auto Solve for Measures: UNIT_PRICE from Cube PRICE_AND_COST_CUBE.CUBE.
11:14:46     Finished Auto Solve for Measures: UNIT_PRICE from Cube PRICE_AND_COST_CUBE.CUBE.
11:14:53 Attached AW GLOBAL.GLOBAL in MULTI Mode.
11:14:54     Started Load of Measures: UNITS, SALES from Cube UNITS_CUBE.CUBE.
11:15:17     Finished Load of Measures: UNITS, SALES from Cube UNITS_CUBE.CUBE. 
             Processed 222589 Records.Rejected 0 Records.
11:15:17     Started Auto Solve for Measures: SALES from Cube UNITS_CUBE.CUBE.
11:15:26     Finished Auto Solve for Measures: SALES from Cube UNITS_CUBE.CUBE.
11:15:26     Started Auto Solve for Measures: UNITS from Cube UNITS_CUBE.CUBE.
11:15:35     Finished Auto Solve for Measures: UNITS from Cube UNITS_CUBE.CUBE.
11:14:38 Started 1 Finished 0 out of 2 Tasks.
11:14:38 Running Jobs: AWXML$_4_1.
11:14:38 Started 2 Finished 0 out of 2 Tasks.
11:14:38 Running Jobs: AWXML$_4_1, AWXML$_4_2.
11:14:38 Started 2 Finished 0 out of 2 Tasks.
11:14:38 Running Jobs: AWXML$_4_1, AWXML$_4_2. Waiting for Tasks to Finish...
11:15:39 Finished Parallel Processing.
11:15:39 Completed Build(Refresh) of GLOBAL.GLOBAL Analytic Workspace.

Monitoring Analytic Workspaces

Oracle Database provides various tools to help you diagnose performance problems. As an Oracle DBA, you may find these tools useful in tuning the database:

Oracle Enterprise Manager Database Control (Database Control) is a general database management and administration tool. In addition to facilitating basic tasks like adding users and modifying datafiles, Database Control presents a graphic overview of a database's current status. It also provides an interface to troubleshooting and performance tuning utilities.
Automatic Workload Repository collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and saves session information.
Automatic Database Diagnostic Monitor watches database performance statistics to identify bottlenecks, analyze SQL statements, and offer suggestions to improve performance.

Oracle Database also provides system views to help you diagnose performance problems. The following topics identify views that are either specific to OLAP or provide database information that is pertinent to OLAP.

Dynamic Performance Views

Each Oracle Database instance maintains fixed tables that record current database activity. These tables collect data on internal disk structures and memory structures. Among them are tables that collect data on Oracle OLAP.

These tables are available to users through a set of dynamic performance views. By monitoring these views, you can detect usage trends and diagnose system bottlenecks. Table 12-4 provides a brief description of each view. Global dynamic performance views (GV$) are also provided.

See Also:

Oracle Database Reference for full descriptions of the OLAP dynamic performance views.

Table 12-4 OLAP Dynamic Performance Views

View	Description
`V$AW_AGGREGATE_OP`	Lists the aggregation operators available in analytic workspaces.
`V$AW_ALLOCATE_OP`	Lists the allocation operators available in analytic workspaces.
`V$AW_CALC`	Collects information about the use of cache space and the status of dynamic aggregation.
`V$AW_LONGOPS`	Collects status information about SQL fetches.
`V$AW_OLAP`	Collects information about the status of active analytic workspaces.
`V$AW_SESSION_INFO`	Collects information about each active session.

Table 12-5 describes some other dynamic performance views that are not specific to OLAP, but which you may want to use when tuning your database for OLAP.

Table 12-5 Selected Database Performance Views

View	Description
`V$LOG`	Displays log file information from the control file.
`V$LOGFILE`	Contains information about redo log files.
`V$PGASTAT`	Provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when `PGA_AGGREGATE_TARGET` is set.
`V$ROWCACHE`	Displays statistics for data dictionary activity. Each row contains statistics for one data dictionary cache.
`V$SYSSTAT`	Lists system statistics.

Basic Queries for Monitoring the OLAP Option

The following queries extract OLAP information from the data dictionary.

More complex queries are provided in a script that you can download from the Oracle OLAP Web site on the Oracle Technology Network. For descriptions of these scripts and download instructions, refer to "OLAP DBA Scripts".

Is the OLAP Option Installed in the Database?

The OLAP option is provided with Oracle Database Enterprise Edition. To verify that the OLAP components have been installed, issue this SQL command:

SELECT comp_name, version, status FROM dba_registry WHERE comp_name LIKE '%OLAP%';
 
COMP_NAME                 VERSION      STATUS
------------------------- ------------ -----------
OLAP Analytic Workspace   10.2.0.4.0   VALID
Oracle OLAP API           10.2.0.4.0   VALID
OLAP Catalog              10.2.0.4.0   VALID

What Analytic Workspaces are in the Database?

The DBA_AWS view provides information about all analytic workspaces. Use the following SQL command to get a list of names, their owners, and the version:

SELECT owner, aw_name, aw_version FROM dba_aws;
 
OWNER      AW_NAME                        AW_VERSION 
---------- ------------------------------ ---------- 
SYS        EXPRESS                        9.1
SYS        AWMD                           9.1
SYS        AWCREATE                       9.1
SYS        AWCREATE10G                    9.1
SYS        AWXML                          9.1
SYS        AWREPORT                       9.1
GLOBAL     GLOBAL                         10.2

See Also:

"System Tables" for descriptions of the analytic workspaces owned by SYS.

How Big is the Analytic Workspace?

To find out the size in bytes of the tablespace extents for a particular analytic workspace, use the following SQL statements, replacing GLOBAL with the name of your analytic workspace.

SELECT extnum, sum(dbms_lob.getlength(awlob)) bytes FROM global.aw$global 
     GROUP BY extnum;
 
    EXTNUM      BYTES
---------- ----------
         0   80254708

To see the size of the LOB table containing an analytic workspace, use a SQL command like the following, replacing GLOBAL.AW$GLOBAL with the qualified name of your analytic workspace.

SELECT ROUND(sum(dbms_lob.getlength(awlob))/1024,0) kb 
     FROM global.aw$global;
 
        KB
----------
     78374

When Were the Analytic Workspaces Created?

The DBA_OBJECTS view provides the creation date of the objects in your database. The following SQL command generates an easily readable report for analytic workspaces.

SELECT owner, object_name, created, status FROM dba_objects 
      WHERE object_name LIKE 'AW$%' AND object_name!='AW$' 
      GROUP BY owner, object_name, created, status 
      ORDER BY owner, object_name;
 
OWNER      OBJECT_NAME     CREATED   STATUS
---------- --------------- --------- -------
GLOBAL     AW$GLOBAL       10-AUG-07 VALID
SYS        AW$AWCREATE     01-AUG-07 VALID
SYS        AW$AWCREATE10G  01-AUG-07 VALID
SYS        AW$AWMD         01-AUG-07 VALID
SYS        AW$AWREPORT     01-AUG-07 VALID
SYS        AW$AWXML        01-AUG-07 VALID
SYS        AW$EXPRESS      01-AUG-07 VALID

OLAP DBA Scripts

You can download a file that contains several SQL scripts from the Oracle OLAP Web site on the Oracle Technology Network. These scripts typically extract information from two or more system views and generate a report that may be useful in monitoring and tuning a database. To download the file, use this URL:

http://www.oracle.com/technetwork/database/options/olap/olap-dba-scripts-393636.zip

Table 12-6 describes these scripts. For more information, refer to the README file provided with the scripts.

Table 12-6 OLAP DBA Scripts

SQL Script	Description
`aw_objects_in_cache`	Identifies the objects in the buffer cache that are related to analytic workspaces.
`aw_reads_writes`	Tallies the reads from temporary and permanent tablespaces, the writes to cache, and the rows processed in analytic workspaces.
`aw_segment_size`	Calculates the size of analytic workspace segments in tablespaces on disk.
`aw_size`	Displays the amount of disk space used by each analytic workspace.
`aw_tablespaces`	Provides extensive information about the tablespaces used by analytic workspaces.
`aw_total_size`	Tallies the sizes of all analytic workspaces accessible to the current user.
`aw_users`	Identifies the users of analytic workspaces.
`aw_wait_events`	Describes the wait events experienced by users of analytic workspaces over the previous hour.
`buffer_cache_hits`	Calculates the buffer cache hit ratio.
`cursor_parameters`	Indicates whether the database parameters that limit the number of open cursors are set too low.
`olap_hit_ratio`	Identifies the PGA, OLAP page pool, and OLAP hit/miss ratio for every user of analytic workspaces in the database.
`olap_pga_performance`	Determines how much PGA is in use, the size of the OLAP page pool, and the hit/miss ratio for OLAP pages for each user.
`olap_pga_use`	Determines how much PGA is consumed by the OLAP page pool to perform operations on analytic workspaces.
`session_resources`	Identifies the use of cursors, PGA, and UGA for each open session.
`shared_pool_hits`	Calculates the shared pool hit ratio.

Scripts for Monitoring Performance

Several of the scripts listed in "OLAP DBA Scripts" provide detailed information about the use of memory and other database resources by OLAP sessions. You can use these scripts as is, or you can use them as the starting point for developing your own scripts.

Example 12-2 shows the information returned by the session_resources script. It lists the use of resources such as cursors, PGA, and UGA.

Example 12-2 Querying Session Resources

SQL> @session_resources
 
USERNAME             NAME                                VALUE
-------------------- ------------------------------ ----------
GLOBAL:95            opened cursors cumulative             101
                     opened cursors current                  3
                     session cursor cache count             31
                     session cursor cache hits              68
                     session pga memory                1219292
                     session pga memory max            1219292
                     session stored procedure space          0
                     session uga memory                 432700
                     session uga memory max             432700
 
 
9 rows selected.

Scripts for Monitoring Disk Space

Several of the scripts listed in "OLAP DBA Scripts" provide detailed information about the use of disk space by analytic workspaces. Example 12-3 shows the information returned by the aw_size script. It lists all of the analytic workspaces in the database, the disk space they consume, and the tablespaces in which they are stored.

Example 12-3 Querying the Use of Disk Space By Analytic Workspaces

SQL> @aw_size
 
Analytic Workspace                            On Disk MB Tablespace
---------------------------------------- --------------- --------------------
GLOBAL.GLOBAL                                     239.38 GLOBAL
SYS.AWCREATE                                        9.81 SYSAUX
SYS.AWCREATE10G                                     1.38 SYSAUX
SYS.AWMD                                            7.00 SYSAUX
SYS.AWREPORT                                        1.50 SYSAUX
SYS.AWXML                                          12.00 SYSAUX
SYS.EXPRESS                                         2.69 SYSAUX
                                         ---------------
Total Disk:                                       273.75
 
7 rows selected.

Backup and Recovery

You can backup and recover analytic workspaces using the same tools and procedures as the rest of your database.

Oracle Recovery Manager (RMAN) is a powerful tool that simplifies, automates, and improves the performance of backup and recovery operations. RMAN enables one time backup configuration, automatic management of backups, and archived logs based on a user-specified recovery window, restartable backups and restores, and test restore/recovery.

RMAN implements a recovery window to control when backups expire. This lets you establish a period of time during which it is possible to discover logical errors and fix the affected objects by doing a database or tablespace point-in-time recovery. RMAN also automatically expires backups that are no longer required to restore the database to a point-in-time within the recovery window. Control file auto backup also allows for restoring or recovering a database, even when an RMAN repository is not available.

Export and Import

You can copy analytic workspaces in several different ways, either to replicate them on another computer or to back them up.

Data Pump. Analytic workspaces are copied with the other objects in a schema or database export. Use the expdp/impdp database utilities.
Transportable Tablespaces. Analytic workspaces are copied with the other objects to a transportable tablespace. However, you can only transport the tablespace to the same platform (for example, from Linux to Linux, Solaris to Solaris, or Windows to Windows) because the OLAP DECIMAL data type is hardware dependent. Use the expdp/impdp database utilities. Transportable tablespaces are much faster than dump files.
XML Templates. A template saves the XML definition of objects in an analytic workspace. You can save the entire analytic workspace, or individual cubes, dimensions, and calculated measures. Using a saved template, you can create a new analytic workspace exactly like an existing one. The template does not save any data, nor does it save any customizations to the analytic workspace. You can copy a template to a different platform.

The owner of an analytic workspace can create an XML template, or export the schema to a dump file. Only users with the EXP_FULL_DATABASE privilege or a privileged user (such as SYS or a user with the DBA role) can export the full database or create a transportable tablespace.

See Also:

"Using Templates to Re-Create Dimensional Objects" for information about XML templates
Oracle Database Utilities for information about Oracle Data Pump and the expdp/impdp commands