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

15-2. Controlling Cell Offload Behavior
Problem
You wish to control cell offload and Smart Scan behavior for a particular session or SQL statement.
Solution
In this recipe, you will learn how to employ techniques to force or prevent cell offload functionality systemwide, at a
session level, for specific SQL statements, and at the ASM disk group level.
Enabling or Disabling Cell Offload Systemwide
To disable cell offload for all sessions in your database, issue the following SQL statement while connected as a
database user with the ALTER SYSTEM system privilege:
SQL> alter system set cell_offload_processing=false;
System altered.
SQL>
To enable cell offload for all sessions in your database, issue the following SQL statement:
SQL> alter system set cell_offload_processing=false;
System altered.
SQL>
Enabling or Disabling Cell Offload for a Session
To disable or enable cell offload for your individual session, execute the following commands:
SQL> alter session set cell_offload_processing=false;
Session altered.
SQL> alter session set cell_offload_processing=true;
Session altered.
SQL>
Enabling or Disabling Cell Offload for a Specific SQL Statement
To control cell offload behavior for individual SQL statements, use the opt_param hint. Following is a sample SQL
statement that is hinted to disable cell offload:
SQL> select /*+ opt_param('cell_offload_processing','false') */ count(1)
2 from mtl_material_transactions;
COUNT(1)
----------
8783431
SQL>