Smart Scan and Cell Offload - Oracle Exadata Recipes: A Problem-Solution Approach - page 441

To enable cell offload, use the the following SQL statement:

SQL> select /*+ opt_param('cell_offload_processing','true') */ count(1)
2 from mtl_material_transactions;
COUNT(1)
----------
8783431
SQL>

Enabling and Disabling Smart Scan for an ASM Disk Group
There may be cases in which you wish to disable or enable Smart Scan operations for an entire ASM disk group and
all its segments. This is accomplished by altering the ASM disk group attribute cell.smart_scan_capable . While
connected as SYSASM to an ASM instance, execute the following command:

SQL> alter diskgroup sdata_cm01
set attribute 'cell.smart_scan_capable'='FALSE' ;
Diskgroup altered.
SQL>

How It Works
There are a number of techniques available to control Smart Scan behavior at a variety of levels. Table 15-1
summarizes these techniques.

Table 15-1. Controlling Cell Offload Behavior

Technique Scope Example

cell_offload_processing
initialization parameter

alter system set cell_offload_processing=false;
alter session set cell_offload_processing=true;

System or
session

opt_param hint select /*+ opt_param('cell offload processing','true' */ ...

select /*+ opt_param('cell offload processing','false' */ ...

SQL
statement

alter diskgroup set
attribute

alter diskgroup [DG] set attribute 'cell.smart_scan_
capable'='[TRUE|FALSE]';

ASM disk
group

15-3. Measuring Smart Scan with Statistics
Problem
You wish to determine whether a SQL query, session, or system processed SQL statements using Exadata Smart Scan.

Solution
In this recipe you will learn how to use Oracle's Smart Scan related statistics to determine whether Smart Scan
was utilized and measure the I/O bytes transmitted over the storage interconnect. We will focus on measuring
Smart Scan at four levels: systemwide, for a specific session, within your own Oracle session, and from Oracle's
Automatic Workload Repository (AWR). In each section, we will provide scripts that report on the cell physical
IO interconnect bytes , cell physical IO bytes eligible for predicate offload , and cell physical IO
interconnect bytes returned by Smart Scan statistics.