CELL SMART SCAN/OFFLOADING SECRET SAUCE OF EXADATA

Offloading in its name says that something is offloaded somewhere.Here in exadata ,high volume of data is offloaded to the cell layer so that the storage gets the total hit rather than the database.

You often heard of ‘smart work vs hard work’ and would have seen this picture

In similar way,exadata has a smart storage which has the intelligence to process the data in storage layer without disturbing the database layer much.So the database layer would always be happy đŸ™‚ with less pressure from the clients who retrieve,insert and update repeated data using sql query

There is a saying always that “a single poorly written sql query might collapse a powerful database “

However it is not the problem in database itself but a combination of layers like OS,network,storage and database layer

So sql query which is resource intensive is offloaded to storage layer and volume of data resturned to the database layer will be filtered and reduced with multiple layers of abstraction.

In cell offloading,there are three types of filtering the data

  • Column projection(columns of interest)
  • Predicate filtering(rows of interest)
  • Storage indexes(min-max value)

We will see all these optimizations in detail in another post.So lets dont waste time in definitions of above three points and test the exciting features of exadata

I perform a test case which proves the efficiency of the cell smart scan

I print the statistics for cell before testing which is zero for all category

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%');   2    3    4

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                                 0
physical write total bytes                                                0
cell physical IO interconnect bytes                                       0
cell physical IO bytes saved during optimized file creation               0
cell physical IO bytes saved during optimized RMAN file restore           0
cell physical IO bytes eligible for predicate offload                     0
cell physical IO bytes saved by storage index                             0
cell physical IO bytes sent directly to DB node to balance CPU            0
cell physical IO interconnect bytes returned by smart scan                0
cell IO uncompressed bytes                                                0

10 rows selected.

Sales table is what we use for testing which has million rows

kish@exdbx<>select count(*) from sales;

  COUNT(*)
----------
   1048576

I flush the contents in shared pool and buffer cache so that we dont blindly get fooled by the speed which we get from the cached data in buffer gets and soft parse in SGA

 kish@exdbx<>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.00
kish@exdbx<>alter system flush buffer_cache;

System altered.
Elapsed: 00:00:00.02

TEST1: Disable the cell offloading and check the response time ! In this test,i get the aggregate value of unit cost and total profit columns and use inner join sales with sales3 table

kish@exdbx<>alter session set cell_offload_processing=false;

Session altered.


kish@exdbx<>select a.country,a.order_date,sum(a.unit_cost),sum(a.total_profit) from sales a
inner join sales2 b on a.order_id = b.order_id
group by a.country,a.order_date
order by sum(a.total_profit) desc;  2    3    4

220105 rows selected.
 
Elapsed: 01:44:58.07   <======================

TEST2:Enable cell offloading and check the response time

kish@exdbx<>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
kish@exdbx<>alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.16

See the difference between previous run and after enabling smart scan !

kish@exdbx<>alter session set cell_offload_processing=true;

Session altered.

Elapsed: 00:00:00.00
kish@exdbx<>select a.country,a.order_date,sum(a.unit_cost),sum(a.total_profit) from sales a
inner join sales2 b on a.order_id = b.order_id
group by a.country,a.order_date 
order by sum(a.total_profit) desc ;  2    3    4

220105 rows selected.

Elapsed: 00:00:11.37 <==================

There is a huge difference in response time from 1 hour 44 minutes to 11 minutes 37 seconds which accounts to almost 1 hour 33 minutes time saved by smart scan with IO bytes saved

The statistics information is also recorded in sysstat and mystat views where there is good evidence for the total IO saved by storage cell

See ‘cell physical IO interconnect bytes returned by smart scan‘ and other rows highlighted with arrow which shows that IO’s requested for database is taken care by storage cell thus reducing the volume of data

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%');  2    3    4

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                        2429.28906
physical write total bytes                                         .0078125
cell physical IO interconnect bytes                              792.847153 <========
cell physical IO bytes saved during optimized file creation               0
cell physical IO bytes saved during optimized RMAN file restore           0
cell physical IO bytes eligible for predicate offload             2224.3125  <========
cell physical IO bytes saved by storage index                             0
cell physical IO bytes sent directly to DB node to balance CPU    20.734375
cell physical IO interconnect bytes returned by smart scan       587.862778 <=========
cell IO uncompressed bytes                                       2198.10156

Take a look at the execution plan with column projection information

As we have both column values and where clause in the previous query ,oracle took both the benefits by using predicate filtering and column projection

Column projection works when there is no where clause and predicate filtering works if there is where clause in predicate

Check out the new term highlighted with * in execution plan called ‘Table access storage full’ which denotes that the table is scanned on the storage layer and this term cant be seen in normal database apart from exadata.

Identify the sql_id of the query

Generated the execution plan with column projection information

kish@exdbx<>select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','+projection'));
Enter value for sql_id: cupasgmbw87j5
Enter value for child_no: 0
old   1: select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','+projection'))
new   1: select * from table(dbms_xplan.display_cursor('cupasgmbw87j5','0','+projection'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cupasgmbw87j5, child number 0
-------------------------------------
select a.country,a.order_date,sum(a.unit_cost),sum(a.total_profit) from
sales a inner join sales2 b on a.order_id = b.order_id group by
a.country,a.order_date order by sum(a.total_profit) desc

Plan hash value: 2885808317

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |       |       |       | 21560 (100)|          |
|   1 |  SORT ORDER BY               |        |   157K|  7524K|    56M| 21560   (1)| 00:04:19 |
|   2 |   HASH GROUP BY              |        |   157K|  7524K|    56M| 21560   (1)| 00:04:19 |
|*  3 |    HASH JOIN                 |        |  1053K|    49M|    18M| 10082   (1)| 00:02:01 |
|   4 |    *** TABLE ACCESS STORAGE FULL***| SALES2 |  1048K|  6144K|       |  3224   (1)| 00:00:39 |
|   5 |   ***  TABLE ACCESS STORAGE FULL***| SALES  |  1048K|    43M|       |  3228   (1)| 00:00:39 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ORDER_ID"="B"."ORDER_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) SUM("A"."TOTAL_PROFIT")[22], "A"."COUNTRY"[VARCHAR2,380],
       "A"."ORDER_DATE"[DATE,7], SUM("A"."UNIT_COST")[22]
   2 - "A"."COUNTRY"[VARCHAR2,380], "A"."ORDER_DATE"[DATE,7],
       SUM("A"."TOTAL_PROFIT")[22], SUM("A"."UNIT_COST")[22]
   3 - (#keys=1) "A"."COUNTRY"[VARCHAR2,380], "A"."ORDER_DATE"[DATE,7],
       "A"."TOTAL_PROFIT"[NUMBER,22], "A"."UNIT_COST"[NUMBER,22]
   4 - "B"."ORDER_ID"[NUMBER,22]
   5 - "A"."COUNTRY"[VARCHAR2,380], "A"."ORDER_DATE"[DATE,7],
       "A"."ORDER_ID"[NUMBER,22], "A"."UNIT_COST"[NUMBER,22], "A"."TOTAL_PROFIT"[NUMBER,22]


37 rows selected.

Elapsed: 00:00:01.16

Lets check the individual sql statement for IO conserve by smart scan

kish@exdbx<>select sql_id,IO_CELL_OFFLOAD_ELIGIBLE_BYTES,IO_INTERCONNECT_BYTES,100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)/IO_CELL_OFFLOAD_ELIGIBLE_BYTES "IO_SAVED_%",sql_text from v$sql where sql_id='cupasgmbw87j5';

SQL_ID        IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_INTERCONNECT_BYTES IO_SAVED_% SQL_TEXT
------------- ------------------------------ --------------------- ---------- ----------
cupasgmbw87j5                      194363392              49433192 74.5666139 select a.c
                                                                              ountry,a.o
                                                                              rder_date,
                                                                              sum(a.unit
                                                                              _cost),sum                                                                   

As you can see that approx 75% of IO is saved by storage scan.If same IO is processed by database layer,then think about how the database badly gets overloaded by resource intensive queries during busy schedule !

kish@exdbx<>set verify off
set pagesize 999
set lines 190
col sql_text format a40 trunc
col child format 99999 heading CHILD
col execs format 9,999,999
col avg_etime format 99,999.99
col avg_cpu  format 9,999,999.99
col avg_lio format 999,999,999
col avg_pio format 999,999,999
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offload for a7

select sql_id, child_number child,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,
100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_                                      SAVED_%",
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
--decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,buffer_gets/decode(nvl(executions,0),0                                      ,1,executions),null) avg_lio,
--decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,disk_reads/decode(nvl(executions,0),0,                                      1,executions),null) avg_pio,
sql_text
from v$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
Enter value for sql_text: select UNITS_SOLD from sales where order_id > 10000
Enter value for sql_id: 6xtxn2qknnw1p

SQL_ID         CHILD OFFLOAD IO_SAVED_%  AVG_ETIME SQL_TEXT
------------- ------ ------- ---------- ---------- ----------------------------------------
6xtxn2qknnw1p      0 Yes          87.60        .52 select UNITS_SOLD from sales where order

Again we check the IO SAVED by offloading for a particular query and you can see that 88 % of IO hits on storage layer

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s