Using smart Scan is a cell offload feature, 100 gg’s of data has to be transferred to the database servers. when query’s runs on large table ( consists of million of rows) i.e alot of I/0 between database and storage servers. Here storage index’s are crated depend on storage servers. and we can deliver 50gg per /sec using flash cache and data has to move from storage layer to database layer through network. With the help of smartscan only the relevant bytes will be loaded into the database memory from storage layer to database layer
When database server send query details to the storage cells via protocol known ad IDB(intelligent database). Oracle Exadata storage cells can search storage disks with added intelligence about the query and send only the relevant bytes, not all the database blocks, to the database nodes—hence the term smart scan. Full table scans with selected functions and operators such as =, >, and so on in predicates and index fast full scans can use smart scans.
How to enable smartscan?
There are different techniques to enable smart scan on Exadata machine by setting parameter “cell_off_load_processing” parameter at database level
Controlling offload behavior on machine at database level
Disable and enable cell offload for all sessions on database
alter system set cell_offload_processing=false;
alter system set cell_offload_processing=true;
At session level,
alter session set cell_offload_processing=false;
alter session set cell_offload_processing=true;
we can measure the amount of I/O saved at system wide/ database level using following query
SQL> select inst.instance_name,
b.name,
a.value/1024/1024/1024 value
from gv$sysstat a, gv$statname b, gv$instance inst
where a.statistic# = b.statistic#
and b.name in
('cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by Smart Scan')
and inst.inst_id=a.inst_id
and inst.inst_id=b.inst_id
order by 1,2;
At session level
select stat.name, round(sess.value/1024/1024/1024,2) value from v$mystat sess,v$statname stat where stat.statistic# = sess.statistic# and stat.name in ('cell physical IO bytes eligible for predicate offload', 'cell physical IO interconnect bytes', 'cell physical IO interconnect bytes returned by Smart Scan') order by 1;
cell physical IO bytes eligible for predicate offload -indicates data transfered over the storage interconnect to database layer .
cell physical IO interconnect bytes' -Number of bytes eligible for smart scan i.e I/0 saved during cell offload processing.
cell physical IO interconnect bytes returned by Smart Scan -cell I/0 returned by smartscan
--Nikhil Tatineni--
--Exadata--
--12c--
