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.
• Database cache. The larger cache maintained by the Oracle RDBMS for the database instance.

  See Also: Oracle9i OLAP Developer's Guide to the OLAP DML for full discussions of data storage issues and aggregation. Oracle9i OLAP DML Reference help under 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.