Oracle Database - Granule (Parallel Data Access)

> Database > Oracle Database

1 - About

A granule is the smallest unit of work when accessing data.

Because Oracle database uses a shared everything architecture (unlike the shared nothing architecture), Oracle can – and will - choose this smallest unit of work solely based on the SQL statement and the degree of parallelism

It will decides whether:

granules lead to a more optimal execution, you cannot influence this behaviour.

The number of granules must be always higher than the requested DOP in order to get an even distribution of work among parallel server processes.

Advertising

3 - Granule Type

3.1 - Block

The basic mechanism the Oracle Database uses to distribute work for parallel execution is block ranges on disk – so-called block-based granules.

Oracle does have to partition the data for parallel access at runtime.

This methodology is unique to Oracle and is independent of whether the underlying objects have been partitioned. Access to the underlying objects is divided into a large number of granules, which are given out to PX servers to work on (and when a PX server finishes the work for one granule the next one is given out).

Although block-based granules are the basis to enable parallel execution for most operation, there are some operations that can benefit from the underlying data structure and leverage individual partitions as granules of work.

The operation 'PX BLOCK ITERATOR ' literally is the iteration over all generated block range granules.

3.2 - Partition

With partition-based granules only one PX server performs the work for all data in a single partition.

The Oracle Optimizer considers partition-based granules if the number of (sub)partitions accessed in the operation is at least equal to the DOP (and ideally much higher if there may be skew in the sizes of the individual (sub)partitions). The most common operations that use partition-based granules are partition-wise joins.

4 - Documentation / Reference