6 OLAP Fixed Views

Oracle collects statistics in fixed tables, and creates user-accessible views from these tables. This chapter describes the fixed views that contain data on Oracle OLAP.

See Also:

For additional information about fixed tables and views, refer to the following:

This chapter contains the following topics:

System Tables Referenced by OLAP Fixed Views
Summary of OLAP Fixed Views
V$AW_AGGREGATE_OP
V$AW_ALLOCATE_OP
V$AW_CALC
V$AW_LONGOPS
V$AW_OLAP
V$AW_SESSION_INFO

6.1 System Tables Referenced by OLAP Fixed Views

Each Oracle database instance maintains a set of virtual tables that record current database activity and store data about the instance. These tables are called the V$ tables. They are also referred to as the dynamic performance tables, because they store information that pertains primarily to performance. Views of the V$ tables are sometimes called fixed views because they cannot be altered or removed by the database administrator.

The V$ tables collect data on internal disk structures and memory structures. They are continuously updated while the database is in use. Among them are tables that collect data on Oracle OLAP.

The SYS user owns the V$ tables. In addition, any user with the SELECT CATALOG role can access the tables. The system creates views from these tables and creates public synonyms for the views. The views are also owned by SYS, but the DBA can grant access to them to a wider range of users.

The names of the OLAP V$ tables begin with V$AW. The view names also begin with V$AW. The following sample SQL*Plus session shows the list of OLAP system tables.

% sqlplus '/ as sysdba'
        .
        .
        .
SQL> SELECT name FROM v$fixed_table WHERE name LIKE 'V$AW%';

NAME
- - - - - - - - - - - - - - -
V$AW_AGGREGATE_OP
V$AW_ALLOCATE_OP
V$AW_CALC
V$AW_LONGOPS
V$AW_OLAP
V$AW_SESSION_INFO

See Also:

For more information on the V$ views in the Database, see the Oracle Database Reference.

6.2 Summary of OLAP Fixed Views

Table 6-1 briefly describes each OLAP fixed view.

Table 6-1 OLAP Fixed Views

Fixed View	Description
V$AW_AGGREGATE_OP	Lists the aggregation operators available in the OLAP DML.
V$AW_ALLOCATE_OP	Lists the allocation operators available in the OLAP DML.
V$AW_CALC	Collects information about the use of cache space.
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.

6.3 V$AW_AGGREGATE_OP

V$AW_AGGREGATE_OP lists the aggregation operators available in the OLAP DML. You can use this view in an application to provide a list of choices.

Column	Datatype	Description
`NAME`	`VARCHAR2`	Operator keyword used in the OLAP DML `RELATION` command
`LONGNAME`	`VARCHAR2`	Descriptive name for the operator
`DEFAULT_WEIGHT`	`NUMBER`	Default weight factor for weighted operators

6.4 V$AW_ALLOCATE_OP

V$AW_ALLOCATE_OP lists the allocation operators available in the OLAP DML. You can use this view in an application to provide a list of choices.

Column	Datatype	NULL	Description
`NAME`	`VARCHAR2`		Operator keyword used in the OLAP DML `RELATION` command
`LONGNAME`	`VARCHAR2`		Descriptive name for the operator

6.5 V$AW_CALC

V$AW_CALC reports on the effectiveness of various caches used by Oracle OLAP. Because OLAP queries tend to be iterative, the same data is typically queried repeatedly during a session. The caches provide much faster access to data that has already been calculated during a session than would be possible if the data had to be recalculated for each query.

The more effective the caches are, the better the response time experienced by users. An ineffective cache (that is, one with few hits and many misses) probably indicates that the data is not being stored optimally for the way it is being viewed. To improve runtime performance, you may need to reorder the dimensions of the variables (that is, change the order of fastest to slowest varying dimensions).

Oracle OLAP uses the following caches:

Aggregate cache. An optional cache used by the AGGREGATE function in the OLAP DML. The AGGREGATE function calculates aggregate data at runtime in response to a query. When a cache is maintained, AGGREGATE can retrieve data that was previously calculated during the session instead of recalculating it each time the data is queried.
Session cache. Oracle OLAP maintains a cache for each session for storing the results of calculations. When the session ends, the contents of the cache are discarded.
Page pool. A cache allocated from the program global area (PGA) in the database, which Oracle OLAP maintains for the session. The page pool is associated with a particular session and is shared by all attached analytic workspaces. If the page pool becomes too full, then Oracle OLAP writes some of the pages to the database cache. When an UPDATE command is issued in the OLAP DML, the changed pages associated with that analytic workspace are written to the permanent LOB, using temporary segments as the staging area for streaming the data to disk. The size of the page pool is controlled by the OLAP_PAGE_POOL initialization parameter.

Database cache. The larger cache maintained by the Oracle RDBMS for the database instance.

See Also:

Oracle OLAP DML Reference for full discussions of data storage issues and aggregation. See the CACHE command for information about defining an aggregate cache.

Column	Datatype	Description
`AGGREGATE_CACHE_HITS`	`NUMBER`	The number of times a dimension member is found in the aggregate cache (a hit). The number of hits for run-time aggregation can be increased by fetching data across the dense dimension.
`AGGREGATE_CACHE_MISSES`	`NUMBER`	The number of times a dimension member is not found in the aggregate cache and must be read from disk (a miss).
`SESSION_CACHE_HITS`	`NUMBER`	The number of times the data is found in the session cache (a hit).
`SESSION_CACHE_MISSES`	`NUMBER`	The number of times the data is not found in the session cache (a miss).
`POOL_HITS`	`NUMBER`	The number of times the data is found in a page in the OLAP page pool (a hit).
`POOL_MISSES`	`NUMBER`	The number of times the data is not found in the OLAP page pool (a miss).
`POOL_NEW_PAGES`	`NUMBER`	The number of newly created pages in the OLAP page pool that have not yet been written to the workspace LOB.
`POOL_RECLAIMED_PAGES`	`NUMBER`	The number of previously unused pages that have been recycled with new data.
`CACHE_WRITES`	`NUMBER`	The number of times the data from the OLAP page pool has been written to the database cache.
`POOL_SIZE`	`NUMBER`	The number of pages in the OLAP page pool.

6.6 V$AW_LONGOPS

V$AW_LONGOPS provides status information about active SQL cursors initiated in the OLAP DML.

A cursor can be initiated within the OLAP DML using SQL FETCH, SQL IMPORT, or SQL EXECUTE, that is, SQL statements that can be declared and executed.

Column	Datatype	Description
`SESSION_ID`	`NUMBER`	The identifier for the session in which the fetch is executing. This table can be joined with V$SESSION to get the user name.
`CURSOR_NAME`	`VARCHAR2`	The name assigned to the cursor in an OLAP DML `SQL DECLARE CURSOR` or `SQL PREPARE CURSOR` command.
`COMMAND`	`VARCHAR2`	An OLAP DML command (`SQL IMPORT`, `SQL FETCH`, or `SQL EXECUTE`) that is actively fetching data from relational tables.
`STATUS`	`VARCHAR2`	One of the following values: `EXECUTING`. The command has begun executing. `FETCHING`. Data is being fetched into the analytic workspace. `FINISHED`. The command has finished executing. This status appears very briefly before the record disappears from the table.
`ROWS_PROCESSED`	`NUMBER`	The number of rows already inserted, updated, or deleted.
`START_TIME`	`TIMESTAMP`	The time the command started executing.

6.7 V$AW_OLAP

V$AW_OLAP provides a record of active sessions and their use with analytic workspaces. A row is generated whenever an analytic workspace is created or attached. The first row for a session is created when the first DML command is issued. It identifies the SYS.EXPRESS workspace, which is attached automatically to each session. Rows related to a particular analytic workspace are deleted when the workspace is detached from the session or the session ends.

Column	Datatype	Description
`SESSION_ID`	`NUMBER`	A unique numerical identifier for a session.
`AW_NUMBER`	`NUMBER`	A unique numerical identifier for an analytic workspace.
`ATTACH_MODE`	`VARCHAR2(10)`	`READ ONLY` or `READ WRITE`.
`GENERATION`	`NUMBER`	The generation of an analytic workspace. Each `UPDATE` creates a new generation. Sessions attaching the same workspace between `UPDATE` commands share the same generation.
`TEMP_SPACE_PAGES`	`NUMBER`	The number of pages stored in temporary segments for the analytic workspace.
`TEMP_SPACE_READS`	`NUMBER`	The number of times data has been read from a temporary segment and not from the page pool.
`LOB_READS`	`NUMBER`	The number of times data has been read from the table where the analytic workspace is stored (the permanent LOB).
`POOL_CHANGED_PAGES`	`NUMBER`	The number of pages in the page pool that have been modified in this analytic workspace.
`POOL_UNCHANGED_PAGES`	`NUMBER`	The number of pages in the page pool that have not been modified in this analytic workspace.

6.8 V$AW_SESSION_INFO

V$AW_SESSION_INFO provides information about each active session.

A transaction is a single exchange between a client session and Oracle OLAP. Multiple OLAP DML commands can execute within a single transaction, such as in a call to the DBMS_AW.EXECUTE procedure.

Column	Datatype	Description
`CLIENT_TYPE`	`VARCHAR2(64)`	`OLAP`
`SESSION_STATE`	`VARCHAR2(64)`	`TRANSACTING`, `NOT_TRANSACTING`, `EXCEPTION_HANDLING`, `CONSTRUCTING`, `CONSTRUCTED`, `DECONSTRUCTING`, or `DECONSTRUCTED`
`SESSION_HANDLE`	`NUMBER`	The session identifier
`USERID`	`VARCHAR2(64)`	The database user name under which the session opened
`CURR_DML_COMMAND`	`VARCHAR2(64)`	The DML command currently being executed
`PREV_DML_COMMAND`	`VARCHAR2(64)`	The DML command most recently completed.
`TOTAL_TRANSACTION`	`NUMBER`	The total number of transactions executed within the session; this number provides a general indication of the level of activity in the session
`TOTAL_TRANSACTION_TIME`	`NUMBER`	The total elapsed time in milliseconds in which transactions were being executed
`AVERAGE_TRANSACTION_TIME`	`NUMBER`	The average elapsed time in milliseconds to complete a transaction
`TRANSACTION_CPU_TIME`	`NUMBER`	The total CPU time in milliseconds used to complete the most recent transaction
`TOTAL_TRANSACTION_CPU_TIME`	`NUMBER`	The total CPU time used to execute all transactions in this session; this total does not include transactions that are currently in progress
`AVERAGE_TRANSACTION_CPU_TIME`	`NUMBER`	The average CPU time to complete a transaction; this average does not include transactions that are currently in progress