Home > Monitoring and Tuning Oracl... > Using Parallel Processing o...
![]() Previous |
![]() Next |
Cubes are often partitioned to improve build and maintenance times. For information about creating a partitioned cube, see "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
You can improve the performance of data maintenance by enabling parallel processing. There are two levels of parallelism:
Parallel job execution: Loading and aggregating the data using multiple processes.
Parallel update: Moving the data from temporary to permanent tablespaces using multiple processes.
The number of parallel processes is controlled by these factors:
The number of objects that can be aggregated in parallel. Each cube and each partition (including the top partition) can use a separate process.
You can control the number of partitions in a cube on the Create/Edit Cube: Partition panel.
The number of simultaneous database processes the user is authorized to run.
This number is controlled by the JOB_QUEUE_PROCESSES
parameter. If you have SYS
privileges, you can obtain the current parameter setting with the following SQL command:
SHOW PARAMETER JOB_QUEUE_PROCESSES
For parallel update, the number of processes you allocate to the job. You can specify the number of processes in the Building: Processing Options panel.
The number of processes allocated to SQL to fetch rows from the relational source tables. When PARALLEL_DEGREE_POLICY
is set to AUTO
or LIMITED
, the database can allocate additional processes for executing SQL statements.
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
:
Load and build the dimensions of the cube serially using a single process.
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.
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:
Processed the four dimensions serially
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.