Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Exadata Cell Offload Processing

Posted by Tyler Muth on June 18, 2010

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)
About these ads

8 Responses to “Exadata Cell Offload Processing”

  1. Hi Tyler,

    Nice post. I’d like to point out one thing if you will allow. It is regarding the following quote:

    “You’ll notice some odd alter sessions and the parallel hint in the query. These are to get cell offload processing to kick in

    The hints and session altering commands were not succinctly to get offload processing to “kick in.” 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.

    Oops, I lied. I have two things to point out. 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.

    Sorry if I’m sounding like a ninny.

    • Tyler Muth said

      Kevin,

      Thanks for your comments. I’ve always said that I learn more from feedback on my blog than the community learns from the content and this post is no exception. I added your comments to the original post so everyone can learn from them.

      Thanks,
      Tyler

  2. [...] Exadata Cell Offload Processing [...]

  3. I/O Resource Management with Exadata…

    Agenda Exadata Overview I/O Scheduling Overview I/O Resource Manager Concepts Intra-Database IORM Inter-Database IORM Category IORM Demo Q&A Exadata Architecture Breaks Data Bandwidth Bottleneck Exadata has Bigger Pipes InfiniBand interconnect tran…

  4. Rich Headrick said

    How can I get my hands on a copy of that simulator? I’ve seen it running on an Oracle employee’s laptop.

    –R

    • Tyler Muth said

      Rich,

      If there was a simulator, and I’m not saying there is, it would probably be labeled as “Internal Only”. At the end of the day it would likely be of limited value. It would be useful to get comfortable with a few of these concepts. OLTP will work exactly the same, so then you’re only playing around with Data Warehouse loads. The key differentiator with Exadata is performance, which is something a simulator would lack (in theory). So, you can’t have it, but you probably wouldn’t get much out of it… if it existed ;)

      Tyler

  5. Rich Headrick said

    My only reason for the interest, would be to get familiar with the cellcli and dcli command line tools. There really is a VM Exadata. Glenn F. from Oracle OCS had it on his laptop. Of course, performance would be horrible, but at least one could prototype some queries and check for cell offload, and maybe do some basic compression tests.

    Thanks Tyler,

    Rich

  6. bdrouvot said

    Hello,

    I just want to let you know that I developed a script to extract exadata real-time metric information based on cumulative metrics.

    The main idea is that cumulative, instantaneous, rates and transition exadata metrics are not enough to answer all the basic questions.

    That’s why the script has been created as it provides a better understanding of what’s is going on on the cells right now.

    More details (on how and why) here : http://bdrouvot.wordpress.com/2012/11/27/exadata-real-time-metrics-extracted-from-cumulative-metrics/

    Please don’t hesitate to give your opinion and report any issue you may found with it.

    Thx
    Bertrand

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 87 other followers

%d bloggers like this: