DBMS_AW_STATS contains a subprogram that generates and stores optimizer statistics for cubes and dimensions. Generating the statistics does not have a significant performance cost.

See Also:

Oracle OLAP User's Guide regarding use of the OLAP option to support business intelligence and analytical applications

This chapter contains the following topic:


Cubes and dimensions are first class data objects that support multidimensional analytics. They are stored in a container called an analytic workspace. Multidimensional objects and analytics are available with the OLAP option to Oracle Database.

Optimizer statistics are used to create execution plans for queries that join two cube views or join a cube view to a table or a view of a table. They are also used for query rewrite to cube materialized views. You need to generate the statistics only for these types of queries.

Queries against a single cube do not use optimizer statistics. These queries are automatically optimized within the analytic workspace.

Summary of DBMS_AW_STATS Subprograms

Table 27-1 DBMS_AW_STATS Package Subprograms

Subprogram Description

ANALYZE Procedure

Generates optimizer statistics on OLAP cubes and dimensions.

ANALYZE Procedure

This procedure generates optimizer statistics on a cube or a dimension.

For a cube, the statistics are for all of the measures and calculated measures associated with the cube. These statistics include:

  • The average length of data values

  • The length of the largest data value

  • The minimum value

  • The number of distinct values

  • The number of null values

For a dimension, the statistics are for the dimension and its attributes, levels, and hierarchies. These statistics include:

  • The average length of a value

  • The length of the largest value

  • The minimum value

  • The maximum value


     (object       IN VARCHAR2);


Table 27-2 ANALYZE Procedure Parameters

Parameter Description


The qualified name of a cube or a dimension.

For a cube, the format of a qualified name is owner.cube_name.

For a dimension, the format is owner.dimension_name.


This sample script generates optimizer statistics on UNITS_CUBE and its dimensions.


The optimizer statistics enable Oracle Database to generate an execution plan for queries against UNITS_CUBE, as shown here:

SQL> EXPLAIN PLAN FOR SELECT * from units_cube_view;
SQL>  SELECT plan_table_output FROM TABLE(dbms_xplan.display());
Plan hash value: 3488499021
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT        |            |  4475K|    93M|   661  (85)| 00:00:08 |
|   1 |  CUBE SCAN PARTIAL OUTER| UNITS_CUBE |  4475K|    93M|   661  (85)| 00:00:08 |
8 rows selected.