Oracle Exadata Cell offloading/ smart scan Not working for sqlid

 

Recently  user reported  that cell offloading was not working for  certain  sql id  . To explore further  on this  planned to  write handy Blog  with some handily information 

Below are parameters controlling offloading : 

cell_offload_compaction: Cell packet compaction strategy.

cell_offload_decryption: Enable SQL processing offload of encrypted data to cells.

cell_offload_parameters: Additional cell offload parameters.

cell_offload_plan_display: Cell offload explain plan display.

cell_offload_processing: Enable SQL processing offload to cells. 

What operations benefit from Smart Scan

Full scan of a heap table.

Fast full scan of a B-Tree or bitmap index.

What operations do not benefit from Smart Scan

Scans of IOTs or clustered tables.

Index range scans.

Access to a compressed index.

Access to a reverse key index.

Secure Enterprise Search.

CREATE INDEX with NOSORT

LONG or LOB column

Cache clause queries

Query more than 255 column

Clustered table

Below checks  were  done to check if   cell offloading is working and to force offloading

--> Check if   cell offloading enabled on database 

show parameter cell 

--> Check if   sqlid is  eligible for  Cell offloading 

select  sql_id , sum(IO_OFFLOAD_ELIG_BYTES_TOTAL) eligible from dba_hist_sqlstat   where  sqlid='99jjh86790j'   group by sql_id ;

--> Check if  offloading is working fine at database level  for other sql 

select stat_name ,  sum(value)  from  dba_hist_sysstat  where stat_name like 'cell physical IO%'  group by stat_name ; 

select stat_name ,  sum(value)  from  dba_hist_sysstat  where stat_name  in ('cell physical IO bytes saved by storage index',

'cell physical IO interconnect bytes returned by smart scan')  group by   STAT_NAME ; 

--> Check if Cell offloading working for sql id 

column  sql_test format a10 

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='cbhjyp1iio'; 

try generating sql monitoring report 

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

/

-->  Ensure Database should not be in DST upgrade state. 

Reference  Exadata: Database Performance Degrades when Database is in Timezone Upgrade Mode (Doc ID 1583297.1)

select name, value$ from sys.props$ where name like '%DST%';

Ways to  force cell offloading 

using parallel hint 

"_serial_direct_read"=TRUE 

 /*+ OPT_PARAM('cell_offload_processing' 'true') */

alter session set "_simple_view_merging"=TRUE;

alter session set "_with_subquery"=materialize;

Making index invisible 

Use full hint . Eg   /*+ FULL(emp) */