smart scan in exadata

Smart scan : Find the statistics related to cell offload [ID 1438173.1]

Applies to:

Oracle Exadata Storage Server Software - Version 11.2.1.3.0 and later
Oracle Exadata Hardware - Version 11.2.0.1 and later
Information in this document applies to any platform.

Purpose

*Purpose
Explain to the reader the purpose of the reference document.

Purpose of this document is to understand the Smart Scan feature and collect statistics for queries using the smart scan.

Scope

  Oracle Exadata Storage Server Software - Version 11.2.1.2.0 to 11.2.3.1.0 [Release 11.2]
  Oracle Exadata Hardware - Database version 11.2.0.1 or higher

Details

What is Smart Scan

Exadata Smart Scan is a feature of the Database Machine. It offloads the data search and retrieval processing to the storage cell. Exadata Cell evaluate query predicates at the storage level to optimize the performance of certain classes of bulk data processing. For example the performance of queries that require a full table or index scans to evaluate selective predicates can be improved by pushing the database expression evaluations to the storage cell. These expressions include simple SQL command predicates, such as id > 10, and column projections, such as SELECT customer_name.
For example:

select object_name from test_objects where object_id='54'
In the preceding example, only rows satisfying the predicate, and only the column specified, are returned to the database server, eliminating unproductive I/O operations.

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.

Considerations/Parameters that will effect smart scan

1. cell_offload_processing should be set to TRUE(Default).

SQL> alter session set cell_offload_processing=TRUE;

2. No quarantines for the sql_id
dcli -g cell_group -l root cellcli -e list quarantine
list quarantine <#> detail  -- run for each quarantine

3. cell.smart_scan_capable attribute of the diskgroup is set (Default).

Query to check what is being offloaded

set pagesize 999
set lines 190
col sql_text format a70 trunc
col child format 99999
col execs format 9,999
col avg_etime format 99,999.99
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offload for a7

select sql_id, child_number child, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions)/
decode(px_servers_executions,0,1,px_servers_executions/decode(nvl(executions,0),0,1,executions)) avg_etime,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
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_%",
sql_text
from v$sql s
where upper(sql_text) like upper(nvl(q'[&sql_text]',sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like '%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_text not like '/* SQL Analyze(%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/

Save the above script as file offload.sql now execute and provide the input for the information of required query

sql>@offload.sql
Enter value for sql_text: % test %
Enter value for sql_id:
Enter value for inst_id:



NOTE: IO_CELL_OFFLOAD_ELIGIBLE_BYTES in V$SQL to indicate whether a statement can be offloaded or not.V$SQLFN_METADATA has a list of all valid functions and whether they are off-loadable or not.

Example where offload is not happening:

SQL> @offload.sql
Enter value for sql_text: select * from dba_objects
old  10: where upper(sql_text) like upper(nvl(q'[&sql_text]',sql_text))
new  10: where upper(sql_text) like upper(nvl(q'[select * from dba_objects]',sql_text))
Enter value for sql_id: 6avfua5g1gkh2
old  14: and sql_id like nvl('&sql_id',sql_id)
new  14: and sql_id like nvl('6avfua5g1gkh2',sql_id)

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
6avfua5g1gkh2      0 2703984749      1        .35      0 No             .00 select * from dba_objects

Example where offload is happening:

SQL> @offload.sql
Enter value for sql_text:
old  10: where upper(sql_text) like upper(nvl(q'[&sql_text]',sql_text))
new  10: where upper(sql_text) like upper(nvl(q'[]',sql_text))
Enter value for sql_id: abgqtq8zt90sb
old  14: and sql_id like nvl('&sql_id',sql_id)
new  14: and sql_id like nvl('abgqtq8zt90sb',sql_id)

SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
abgqtq8zt90sb      0 3570092908      1        .79      0 Yes          99.86 select * from test_objects where object_id='54'

Other queries to check the smart scan statistics

Use the below query to check statistic before/after the query

select b.name, a.value from v$mystat a, v$statname b
  where a.STATISTIC# = b.STATISTIC# and
        (b.name = 'cell session smart scan efficiency' or
         b.name = 'cell physical IO bytes saved by storage index' or
         b.name = 'cell physical IO bytes eligible for predicate offload' or
         b.name = 'cell physical IO interconnect bytes returned by smart scan'
         or
         b.name = 'cell IO uncompressed bytes'
         or
         b.name like '%cell blocks processed%');


select name, value from v$mystat s, v$statname n
 where n.statistic#=s.statistic#
 and name like 'cell scans';

Result of these queries before and after the query was run

SQL> select name, value from v$mystat s, v$statname n
 where n.statistic#=s.statistic#
 and name like 'cell scans';  2    3

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell scans                                                                1

SQL> select b.name, a.value from v$mystat a, v$statname b
  2    where a.STATISTIC# = b.STATISTIC# and
  3          (b.name = 'cell session smart scan efficiency' or
  4           b.name = 'cell physical IO bytes saved by storage index' or
  5           b.name = 'cell physical IO bytes eligible for predicate offload' or
  6           b.name = 'cell physical IO interconnect bytes returned by smart scan'
  7           or
  8           b.name = 'cell IO uncompressed bytes'
  9           or
 10           b.name like '%cell blocks processed%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload            1081237504
cell physical IO bytes saved by storage index                    1067909120
cell physical IO interconnect bytes returned by smart scan             6856
cell blocks processed by cache layer                                   1627
cell blocks processed by txn layer                                     1627
cell blocks processed by data layer                                    1627
cell blocks processed by index layer                                      0
cell IO uncompressed bytes                                         13328384

8 rows selected.



After the query was run:


SQL> select b.name, a.value from v$mystat a, v$statname b
  2    where a.STATISTIC# = b.STATISTIC# and
  3          (b.name = 'cell session smart scan efficiency' or
  4           b.name = 'cell physical IO bytes saved by storage index' or
  5           b.name = 'cell physical IO bytes eligible for predicate offload' or
  6           b.name = 'cell physical IO interconnect bytes returned by smart scan'
  7           or
  8           b.name = 'cell IO uncompressed bytes'
  9           or
 10           b.name like '%cell blocks processed%');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes eligible for predicate offload            2162475008
cell physical IO bytes saved by storage index                    2135818240
cell physical IO interconnect bytes returned by smart scan            13712
cell blocks processed by cache layer                                   3254
cell blocks processed by txn layer                                     3254
cell blocks processed by data layer                                    3254
cell blocks processed by index layer                                      0
cell IO uncompressed bytes                                         26656768

8 rows selected.

SQL> select name, value from v$mystat s, v$statname n
 where n.statistic#=s.statistic#
 and name like 'cell scans';  2    3

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell scans                                                                2