Skip Headers
Previous
Previous
 
Next
Next

Using Parallel Processing on Partitioned Cubes

Cubes are often partitioned to improve build and maintenance times. For information about creating a partitioned cube, see "Cube Partitioning".

Querying Metadata for Cube Partitioning

To discover the current partitioning, query the ALL_CUBES data dictionary view. The PARTITION_DIMENSION_NAME, PARTITION_HIERARCHY_NAME, and PARTITION_LEVEL_NAME columns display partitioning information. For example, the following query shows that the Units Cube is partitioned on the Time dimension, the Calendar hierarchy, and the Calendar Year level.

SELECT partition_dimension_name, partition_hierarchy_name, 
     partition_level_name FROM all_cubes
     WHERE owner='GLOBAL' AND cube_name='UNITS_CUBE';

PARTITION_DIMENSION_NAME  PARTITION_HIERARCHY_NAME  PARTITION_LEVEL_NAME
------------------------- ------------------------- --------------------
TIME                      CALENDAR                  CALENDAR_YEAR

Parallelism

You can improve the performance of data maintenance by enabling parallel processing. There are two levels of parallelism:

The number of parallel processes is controlled by these factors:

Suppose that a cube is partitioned on the Quarter level of Time, and the cube contains three years of data. The cube has 3*4=12 bottom partitions, JOB_QUEUE_PROCESSES is set to 8, and you set the parallelism option to 4 for the build. Oracle Database processes the cube in this way when PARALLEL_DEGREE_POLICY is set to its default value of MANUAL:

  1. Load and build the dimensions of the cube serially using a single process.

  2. Load and build the 12 bottom partitions in parallel using 4 processes. As soon as one process finishes, another begins until all 12 are complete.

    This cube could use the 8 processes allowed by JOB_QUEUE_PROCESSES, but it is limited to 4 by the build setting.

  3. Load and build the top partition.

When PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED, Oracle Database may allocate more than the designated processes.

Example: Build Log for Global Units Cube shows excerpts from CUBE_BUILD_LOG for a build of the Units cube and its dimensions. Partitioning on the Calendar Year level of the Time dimension created 10 bottom partitions for 1998 to 2007. JOB_QUEUE_PROCESSES is set to 2 and the parallelism option is set to 2 for the build also. The log shows that Oracle Database processed the Global Units Cube in this way:

  1. Processed the four dimensions serially

  2. Processed each partition of the Units cube in parallel

Build Log for Global Units Cube

SLAVE_NUMBER STATUS     COMMAND              BUILD_OBJECT    PARTITION
------------ ---------- -------------------- --------------- ---------------
           0 STARTED    BUILD
           0 STARTED    ATTACH AW RW WAIT
           0 COMPLETED  ATTACH AW RW WAIT
           0 STARTED    FREEZE
           0 COMPLETED  FREEZE
           0 STARTED    LOAD NO SYNCH        TIME
           0 SQL        LOAD NO SYNCH        TIME
               .
               .
               .
           0 SQL        LOAD NO SYNCH        PRODUCT
           0 SQL        LOAD NO SYNCH        PRODUCT
           0 COMPLETED  LOAD NO SYNCH        PRODUCT
           0 STARTED    COMPILE              PRODUCT
           0 COMPLETED  COMPILE              PRODUCT
           0 STARTED    COMPILE AGGMAP       UNITS_CUBE
           0 COMPLETED  COMPILE AGGMAP       UNITS_CUBE
           0 STARTED    COMPILE AGGMAP       PRICE_CUBE
           0 COMPLETED  COMPILE AGGMAP       PRICE_CUBE
           0 STARTED    UPDATE/COMMIT        PRODUCT
           0 COMPLETED  UPDATE/COMMIT        PRODUCT
           0 STARTED    UPDATE/COMMIT
           0 COMPLETED  UPDATE/COMMIT
           0 STARTED    REATTACH AW MULTI TH
                        AW
 
           0 COMPLETED  REATTACH AW MULTI TH
                        AW
 
           0 STARTED    SLAVE                UNITS_CUBE      P10:CY2007
           0 STARTED    SLAVE                UNITS_CUBE      P9:CY2006
           1 STARTED    BUILD                                P10:CY2007
           1 STARTED    ATTACH AW MULTI THAW UNITS_CUBE      P10:CY2007
           1 COMPLETED  ATTACH AW MULTI THAW UNITS_CUBE      P10:CY2007
           1 STARTED    ACQUIRE              UNITS_CUBE      P10:CY2007
           1 COMPLETED  ACQUIRE              UNITS_CUBE      P10:CY2007
           1 STARTED    LOAD                 UNITS_CUBE      P10:CY2007
           1 SQL        LOAD                 UNITS_CUBE      P10:CY2007
           1 COMPLETED  LOAD                 UNITS_CUBE      P10:CY2007
           1 STARTED    UPDATE/COMMIT        UNITS_CUBE      P10:CY2007
           1 COMPLETED  UPDATE/COMMIT        UNITS_CUBE      P10:CY2007
               .
               .
               .
          10 STARTED    BUILD                                P1:CY1998
          10 STARTED    ATTACH AW MULTI THAW UNITS_CUBE      P1:CY1998
          10 COMPLETED  ATTACH AW MULTI THAW UNITS_CUBE      P1:CY1998
          10 STARTED    ACQUIRE              UNITS_CUBE      P1:CY1998
          10 COMPLETED  ACQUIRE              UNITS_CUBE      P1:CY1998
          10 STARTED    LOAD                 UNITS_CUBE      P1:CY1998
          10 SQL        LOAD                 UNITS_CUBE      P1:CY1998
          10 COMPLETED  LOAD                 UNITS_CUBE      P1:CY1998
          10 STARTED    SOLVE                UNITS_CUBE      P1:CY1998
          10 COMPLETED  SOLVE                UNITS_CUBE      P1:CY1998
          10 STARTED    UPDATE/COMMIT        UNITS_CUBE      P1:CY1998
          10 COMPLETED  UPDATE/COMMIT        UNITS_CUBE      P1:CY1998
          10 STARTED    DETACH AW            UNITS_CUBE      P1:CY1998
          10 COMPLETED  DETACH AW            UNITS_CUBE      P1:CY1998
          10 COMPLETED  BUILD                                P1:CY1998
           0 COMPLETED  SLAVE                UNITS_CUBE      P1:CY1998
           0 STARTED    REATTACH AW MULTI TH
                        AW
 
           0 COMPLETED  REATTACH AW MULTI TH
                        AW
 
           0 STARTED    SLAVE                UNITS_CUBE      P0
          11 STARTED    BUILD                                P0
          11 STARTED    ATTACH AW MULTI THAW UNITS_CUBE      P0
          11 COMPLETED  ATTACH AW MULTI THAW UNITS_CUBE      P0
          11 STARTED    ACQUIRE              UNITS_CUBE      P0
          11 COMPLETED  ACQUIRE              UNITS_CUBE      P0
          11 STARTED    LOAD                 UNITS_CUBE      P0
          11 COMPLETED  LOAD                 UNITS_CUBE      P0
          11 STARTED    SOLVE                UNITS_CUBE      P0
          11 COMPLETED  SOLVE                UNITS_CUBE      P0
          11 STARTED    UPDATE/COMMIT        UNITS_CUBE      P0
          11 COMPLETED  UPDATE/COMMIT        UNITS_CUBE      P0
          11 STARTED    DETACH AW            UNITS_CUBE      P0
          11 COMPLETED  DETACH AW            UNITS_CUBE      P0
          11 COMPLETED  BUILD                                P0
           0 COMPLETED  SLAVE                UNITS_CUBE      P0
           0 STARTED    REATTACH AW RW WAIT
           0 COMPLETED  REATTACH AW RW WAIT
           0 STARTED    ANALYZE              UNITS_CUBE
           0 COMPLETED  ANALYZE              UNITS_CUBE
           0 STARTED    THAW
           0 COMPLETED  THAW
           0 STARTED    DETACH AW
           0 COMPLETED  DETACH AW
           0 COMPLETED  BUILD
 
268 rows selected.

Oracle Database allocates the specified number of processes regardless of whether all of them can be used simultaneously at any point in the job. For example, if your job can use up to three processes, but you specify five, then two of the processes allocated to your job cannot be used by it or by any other job.

If Oracle Database is installed with Real Application Clusters (Oracle RAC), then a script submitted to the job queue is distributed across all nodes in the cluster. The performance gains can be significant. For example, a job running on four nodes in a cluster may run up to four times faster than the same job running on a single computer.