Oracle Database - Granule (Parallel Data Access)

Card Puncher Data Processing

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.

Granule Type

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.

Oracle Database Block Based Granule

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

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.

Documentation / Reference





Discover More
Oracle Database Hash Redistribution
Oracle Database - Data Redistribution (Parallel)

Data redistribution is not unique to the Oracle Database. In fact, this is one of the most fundamental principles of parallel processing, being used by every product that provides parallel capabilities....
Parallel Linearly Scalable Operation
Oracle Database - Parallel Operations

The fundamental difference and advantage of Oracle's capabilities, however, is that parallel data access and therefore the necessary data redistribution are not constrained by any given hardware architecture...
Card Puncher Data Processing
Oracle Database - Parallel execution with Oracle Partitioning (parallel partition-wise join)

There are specific optimizations between SQL parallel execution and Oracle Partitioning that you should bear in mind when you plan to use these functionalities together. For example, two large partitioned...



Share this page:
Follow us:
Task Runner