Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

Part Number B28419-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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.

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:


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:

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

Syntax

DBMS_AW_STATS.ANALYZE
     (object       IN VARCHAR2);

Parameters

Table 27-2 ANALYZE Procedure Parameters

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.


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.