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:
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 query lists the OLAP system tables.
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.Table 2-1 briefly describes each OLAP dynamic performance view.
Fixed View | Description |
---|---|
Lists the aggregation operators available in analytic workspaces |
|
Lists the allocation operators available in analytic workspaces |
|
Collects information about the use of cache space and the status of dynamic aggregation |
|
Collects status information about SQL fetches |
|
Collects information about the status of active analytic workspaces |
|
Collects information about each active session |
V$AW_AGGREGATE_OP
lists the aggregation operators available in analytic workspaces. You can use this view in an application to provide a list of choices.
Column | Datatype | Description |
---|---|---|
NAME |
VARCHAR2(14) |
Operator keyword used in the RELATION command |
LONGNAME |
VARCHAR2(30) |
Descriptive name for the operator |
DEFAULT_WEIGHT |
NUMBER |
Default weight factor for weighted operators |
V$AW_ALLOCATE_OP
lists the allocation operators available in analytic workspaces. You can use this view in an application to provide a list of choices.
Column | Datatype | Description |
---|---|---|
NAME |
VARCHAR2(14) |
Operator keyword used in the RELATION command |
LONGNAME |
VARCHAR2(30) |
Descriptive name for the operator |
V$AW_CALC
reports on the effectiveness of various caches used by Oracle OLAP and the status of processing by the AGGREGATE
function.
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 internal cache used by the aggregation subsystem during querying. It stores the children of a given dimension member, such as Q1-04,
Q2-04
, Q3-04
, and Q4-04
as the children of 2004
.
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 User Global Area (UGA), which Oracle OLAP maintains for the session. The page pool is associated with a particular session and caches records from all the analytic workspaces attached in that session. 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, 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 Application Developer's Guide for full discussions of data storage issues and aggregation.V$AW_CALC
provides status information about dynamic aggregation in each OLAP session. Dynamic aggregation is performed by the AGGREGATE
function.
V$AW_CALC
reports the number of logical NAs generated when AGGINDEX
is set. AGGINDEX
is an index of all composite tuples for the data. When a composite tuple does not exist, the AGGREGATE
function returns NA.
V$AW_CALC
also reports the number of times the AGGREGATE
function uses a precomputed aggregate, and the number of times the AGGREGATE
function has to calculate an aggregate value.
Column | Datatype | Description |
---|---|---|
SESSION_ID |
NUMBER |
A unique numeric identifier for the session |
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 kilobytes in the OLAP page pool |
CURR_DML_COMMAND |
VARCHAR2(64) |
The OLAP DML command currently being executed |
PREV_DML_COMMAND |
VARCHAR2(64) |
The OLAP DML command most recently completed |
AGGR_FUNC_LOGICAL_NA |
NUMBER |
The number of times the AGGREGATE function returns a logical NA because AGGINDEX is on and the composite tuple does not exist |
AGGR_FUNC_PRECOMPUTE |
NUMBER |
The number of times the AGGREGATE function finds a value in a position that it was called to calculate |
AGGR_FUNC_CALCS |
NUMBER |
The number of times the AGGREGATE function calculates a parent value based on the values of its children |
V$AW_LONGOPS
provides status information about active SQL cursors initiated in analytic workspaces.
A cursor can be initiated within an analytic workspace 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(64) |
The name assigned to the cursor in an OLAP DML SQL DECLARE CURSOR or SQL PREPARE CURSOR command |
COMMAND |
VARCHAR2(7) |
An OLAP DML command (SQL IMPORT , SQL FETCH , or SQL EXECUTE ) that is actively fetching data from relational tables |
STATUS |
VARCHAR2(9) |
One of the following values:
|
ROWS_PROCESSED |
NUMBER |
The number of rows already inserted, updated, or deleted |
START_TIME |
TIMESTAMP(3) |
The time the command started executing |
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 numeric identifier for a session |
AW_NUMBER |
NUMBER |
A unique numeric identifier for an analytic workspace. To get the name of the analytic workspace, join this column to the AW_NUMBER column of the USER_AWS view or to the AWSEQ# column of the AW$ table |
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 |
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 |
---|---|---|
SESSION_ID |
NUMBER |
A unique numeric identifier for a session |
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 |
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 |
TRANSACTION_TIME |
NUMBER |
The elapsed time in milliseconds of the mostly recently completed transaction |
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 |