Top Two Parameters in Oracle Exadata

You must have question in your mind that why only two parameters as there are n number of parameters related Exadata and all have it's own importance.

Well, we have choose these two because exadata performance more over dependent on these parameters and major two functionality works on these parameters.

These two parameters are:

_KCFIS_STORAGEIDX_DISABLED

CELL_OFFLOAD_PROCESSING

_KCFIS_STORAGEIDX_DISABLED

- There is not much you can do to control storage index behavior. However, the developers have built in a few hidden parameters that provide some flexibility.

- Above parameter is one of them to control the storage index functionality on exadata.

- This is undocumented and hidden parameter which is used to control the storage index function. It is highly recommended to check with the Oracle support before using it. 

- This parameter tells oracle storage if storage index need to be used while performing query execution. 

- It is used to enable and disable the usage of storage index when exadata is used for the database. 

- By default it is FLASE means storage index is enabled. If you need to disable the usage of storage index than make the value of this parameter TRUE.

Disable storage index:

SQL>alter system set _KCFIS_STORAGEIDX_DISABLED=TRUE;

Enable storage index:

SQL>alter system set _KCFIS_STORAGEIDX_DISABLED=FALSE;

You can also test this on session level.

CELL_OFFLOAD_PROCESSING

- Second parameter is related to the smart scan function of exadata. There are several parameters that apply to offloading. 

- The main one is CELL_OFFLOAD_PROCESSING, which turns offloading on and off.

- By default the value of this parameter is TRUE which means smart scan is enabled. In case if you want to test measure the performance of database using or without smart scan , you can use this parameter. 

- If it is disabled, it means that predicate filtering and storage indexes cannot be used to cut down the volume of data.

Disable smartscan:

SQL>alter system set CELL_OFFLOAD_PROCESSING=FALSE;

Enable smartscan:

SQL>alter system set CELL_OFFLOAD_PROCESSING=TRUE;

This parameter also can be tested on session level.

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.

History of Exadata

First Oracle's exadata was released in 2008 known as hardware and software engineered to work together. Since than we have seen 6 release of exadata machine with improved the combination of hardware and software. Latest X5-2 released in 2015. Here we have given short history of each version from V1 to X5-2.

V1: The first Exadata was released in 2008 and labeled as V1. It was a combination of HP hardware and Oracle software. There were no flash cache used in this version. At that time Exadata was made specially for Data ware housing only. This product was not accepted widely as it was suffered from overheating. 

V2: After V1 Oracle has launched Exadata V2 in 2009. It was a combination of Sun hardware and Oracle software. Here in V2 Oracle has upgraded storage cell with 384 GB of Exadata smart cache. Many o the customer has changed the Exadata from V to V2 because of the overheating problem in Exadata V1.

X2: This is the third edition of Oracle Exadata released in 2010.There are two version X2-2 stands for dual core CPUs and X2-8 stands for 8 core CPUs database server. This version is supporting both OLTP and Data warehouse. Also this has massive features compare to older Exadata in terms of CPUs, Memory, Compression, Smart scan etc...

X3: This is the forth edition of Exadata released in 2012. There is no much more difference between X2 and X3 except in CPU and RAM. This version has more RAM and CPU power then the previous one.

X4: It was released in 2013. In this version processing was increased to 2×12 core CPUs. It had the capacity to upgrade memory to 512GB in a compute node. Flash and disk storage was also increased. It featured a new model of high-capacity disk. 600GB disks were retired, and included 1.2TB, 10,000 RPM disks. These disks were a smaller form factor (2.5” vs 3.5”). The other big change with the X4-2 was the introduction of an active/active InfiniBand network connection. On the X4-2, Oracle broke the bonded connection and utilized each InfiniBand port independently. This allowed an increased throughput across the InfiniBand fabric.

X5: It was announced in early 2015, the sixth generation of Exadata. The X5-2 was a dramatic change in the platform, removing the high-performance Disk option in favor of an all-flash, NVMe (Non-Volatile Memory Express) model. High-capacity disk sizes remain the same at 4TB per disk. The size of the flash cards doubled to 6.4TB per storage server. Memory stayed consistent with a base of 256GB, upgradeable to 768 GB, and the CPU core count increased to 18 cores per socket. With X5, customers are allowed to purchase X5-2 with any configuration required—a base rack begins with two compute nodes and three storage servers.

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