How Exadata Cell Offloading Works ?

First let's know what is offloading and benefit of cell offloading in Exadata?

What is Cell Offloading :-

It refers to the fact that part of the traditional SQL processing done by the database can be “offloaded” from the database layer to the storage layer.

Benefit: The primary benefit of Offloading is the reduction in the volume of data that must be returned to the database server. This is one of the major bottlenecks of most  large databases.

Below is the example of cell offloading.

We have created table cell_offload with 20 lac records.

#### Checking the cell offloading process in DB parameter  ####

SQL> show parameter cell_offload_processing

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cell_offload_processing              boolean     TRUE

# Flush buffer_cache and shared_pool to get exact result of offloading scenario #

SQL> alter system flush buffer_cache;

System altered.

SQL>  alter system flush SHARED_POOL;

System altered.

## Disabling Cell_offloading_process and checking the query result ##

Exadata Offload Processing

Exadata offloading is to offload processing to the storage tier. Offloading and Smart Scan are two terms that are used somewhat interchangeably. Offloading is a more generic term that means doing work at the storage tier that would otherwise have to be done on the database tier (this can include work that is not related to executing queries such as optimization of incremental backups). Smart Scans on the other hand are the access mechanism used to offload query processing tasks. For example, storage servers can apply predicate filters at the storage layer, instead of shipping every possible block back to the database server(s). Another thing that happens with Smart Scans is that the volume of data returned can be further reduced by column projection (i.e. if you only select 1 column from a 100 column table, there is no need to return the other 99 columns). Offloading is geared to long running queries that access a large amount of data. Offloading only works if Oracle decides to use its direct path read mechanism.

There three primary goals to use the smart scan/Offloading.

• Reduce the volume of data transferred from disk systems to the database servers.
• Reduce CPU usage on database servers.
• Reduce disk access times at the storage layer.

Reducing the volume was the main focus and primary goal. The majority of the optimizations introduced by Offloading contribute to this goal. Reducing CPU load is important as well, but is not the primary benefit provided by Exadata and therefore takes a back seat to reducing the volume of data transferred. (As you’ll see, however, decompression is a notable exception to that generalization, as it is performed on the storage servers.) Several optimizations to reduce disk access time were also introduced, and while some of the results can be quite stunning, we don’t consider them to be the bread-and-butter optimizations of Exadata.

Exadata Features

Let's make a note of major features of exadata. We will see all the features in depth in next couple of posts.

1. Smart Scan / Offloading
2. Storage Index
3. Hybrid Columnar Compression (HCC)
4. Smart Flash Cache
5. IORM