27 DBMS_AW_STATS

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.

Using DBMS_AW_STATS

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

Syntax

DBMS_AW_STATS.ANALYZE
     (object       IN VARCHAR2);

Parameters

Table 27-2 ANALYZE Procedure Parameters

Parameter Description

Parameter	Description
`object`	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`.

object

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.

Example

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

BEGIN
     DBMS_AW_STATS.ANALYZE('units_cube');
     DBMS_AW_STATS.ANALYZE('time');
     DBMS_AW_STATS.ANALYZE('customer');
     DBMS_AW_STATS.ANALYZE('product');
     DBMS_AW_STATS.ANALYZE('channel');
END;
/

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;
 
Explained.
 
SQL>  SELECT plan_table_output FROM TABLE(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
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.