Exadata Cell Offload Processing

In my previous post I talked about Exadata Intelligent Storage.  One of my favorite aspects of Intelligent Storage is the concept of Cell Offload Processing. “The term Offload Processing encompasses things that also have nothing to do with a query. Offload Processing is comprised of Smart Scan, fast file creation, RMAN block scanning, etc. This post is specifically about Smart Scan” – Kevin Closson. The idea that the storage cells can understand and process most of the common query predicates and only send back the data we actually need is just brilliant.  It also can have a huge impact on performance when working with large data sets.  What predicates do the cells understand?

=, !=, <, >, <=, >=, IS[NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OFtype, NOT, AND, OR

The following is the explain plan output of the same query, first without cell offload processing (traditional), then with cell offload processing:

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |   816 | 33456 |  5559   (1)| 00:01:07 |
|   1 |  SORT ORDER BY      |          |   816 | 33456 |  5559   (1)| 00:01:07 |
|   2 |   HASH GROUP BY     |          |   816 | 33456 |  5559   (1)| 00:01:07 |
|*  3 |    TABLE ACCESS FULL| ALL_OBJS |   816 | 33456 |  5557   (1)| 00:01:07 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 3 - filter(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE' OR
 "OBJECT_TYPE"='VIEW') AND "OBJECT_NAME" LIKE 'DBMS_RESOURCE%')

Now with cell offload processing:

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   816 | 33456 |  5559   (1)| 00:01:07 |
|   1 |  SORT ORDER BY              |          |   816 | 33456 |  5559   (1)| 00:01:07 |
|   2 |   HASH GROUP BY             |          |   816 | 33456 |  5559   (1)| 00:01:07 |
|*  3 |    TABLE ACCESS STORAGE FULL| ALL_OBJS |   816 | 33456 |  5557   (1)| 00:01:07 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 3 - storage(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE' OR
 "OBJECT_TYPE"='VIEW') AND "OBJECT_NAME" LIKE 'DBMS_RESOURCE%')
 filter(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE' OR
 "OBJECT_TYPE"='VIEW') AND "OBJECT_NAME" LIKE 'DBMS_RESOURCE%')

Notice the difference on line 6 as well as the predicate details.
Now lets take a look at what impact that has on the amount of data we send from storage to the database. To illustrate this, I’ll run the same query with and without cell offload processing enabled and query v$mystat for “cell physical IO interconnect bytes” before and after each query.  The following output is just a summary to make it more clear, the full script is at the end of this post.  Also, I’d like to thank Kevin Closson for steering me in the right direction of v$mystat as I was headed down a Wireshark packet sniffing rat hole for this demo that was leading nowhere. Also keep in mind that this is on a simulator, not a real Exadata box so the timing is not accurate, but the relative times are still interesting.

-- query v$mystat
select /*+PARALLEL(all_objs, 4)*/ count(*)
 from all_objs
 where object_type = 'TABLE'
 and object_name like '%RESOURCE%';
-- Elapsed: 00:00:00.96

-- query v$mystat

alter session set "cell_offload_processing" = FALSE;
select /*+PARALLEL(all_objs, 4)*/ count(*)
 from all_objs
 where object_type = 'TABLE'
 and object_name like '%RESOURCE%';
-- Elapsed: 00:00:10.00
 -- query v$mystat

SMART_SCAN_MB NON_SMART_SCAN_MB
------------- -----------------
 4.3427887         160.71875

That means that in this case there was 37x more network traffic with traditional storage than with Exadata Cell Offload Processing.  Also keep in mind the 40 Gb Infiniband network offers 10x the throughput of traditional Fibre Channel.  Forget Flash Cache, Compression, and Storage Indexes for a minute.  The performance gains alone of 37x less data over a 10x bigger pipe are pretty remarkable.

The following is the full demo script.  You’ll notice some odd alter sessions and the parallel hint in the query.  These are to get cell offload processing to kick in on a relatively small table (1.4 million rows) “The prime ingredient in Smart Scan is access method full and PGA buffering. So you forced the plan and set serial_direct_reads. Without this you’d likely get a different access method buffered in the SGA and thus no Smart Scan” – Kevin Closson.  There’s also some sqlplus magic to store the values that I had to dig up from my days working on the APEX installer.

ALTER TABLE all_objs STORAGE( CELL_FLASH_CACHE none);

alter session set "_serial_direct_read"=true;

column NON_SMART_SCAN_KB format 999,999,999,999.0
column SMART_SCAN_KB format 999,999,999,999.0
set define '^'
set verify off
column cell_initial_bytes new_val INITIAL_BYTES
column cell_offload_bytes new_val OFFLOAD_BYTES
column cell_non_offload_bytes new_val NON_OFFLOAD_BYTES

set linesize 200
set wrap off
column name format a50

alter session set "_small_table_threshold"=1;

ALTER SYSTEM FLUSH BUFFER_CACHE;
alter session set "cell_offload_processing" = TRUE;

select s.name, m.value cell_initial_bytes
 from v$mystat m, v$statname s
 where s.statistic# = m.statistic#
 and name = 'cell physical IO interconnect bytes';

set timing on
-- set termout off

select /*+PARALLEL(all_objs, 4)*/ count(*)
 from all_objs
 where object_type = 'TABLE'
 and object_name like '%RESOURCE%';

set termout on
set timing off
select s.name, m.value cell_offload_bytes
 from v$mystat m, v$statname s
 where s.statistic# = m.statistic#
 and name = 'cell physical IO interconnect bytes';

alter session set "cell_offload_processing" = FALSE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
-- set termout off

set timing on
select /*+PARALLEL(all_objs, 4)*/ count(*)
 from all_objs
 where object_type = 'TABLE'
 and object_name like '%RESOURCE%';

 set timing off
set termout on

select s.name, m.value cell_non_offload_bytes
 from v$mystat m, v$statname s
 where s.statistic# = m.statistic#
 and name = 'cell physical IO interconnect bytes';

SELECT (^OFFLOAD_BYTES - ^INITIAL_BYTES)/1024/1024 smart_scan_mb, (^NON_OFFLOAD_BYTES - ^OFFLOAD_BYTES)/1024/1024 non_smart_scan_mb FROM DUAL;

n on a relatively small table (1.4 million rows)