6 OLAP Fixed Views

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

6.2 Summary of OLAP Fixed Views

Table 6-1 briefly describes each OLAP fixed view.

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.

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.

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.

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.

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.