Exadata: Calculating Offload Efficiency

Calculating offload efficiency for Sql statement
Querying cell I/O statistics help us to understand the smart scan behavior for individual sql statements.
Find out the cursor value ( sql_id) for the sql we want to calculate offload efficiency from v$sql


Use following Query to calculate offload efficiency for each cursor( sql statement )

select    

sql_id,

 (case when io_cell_offload_eligible_bytes = 0 then 0

    else 100*(1-(io_interconnect_bytes/io_cell_offload_eligible_bytes))

 end) offload1,

 (case when phybytes = 0 then 0

    else 100*(1-(io_interconnect_bytes/phybytes))

 end) offload2

from (

 select  sql_id,

         physical_read_bytes+physical_write_bytes phybytes,

         io_cell_offload_eligible_bytes,

         io_interconnect_bytes

    from v$sql

    where sql_id='&&sqlid');

EstOffloadEfficiency%= The number of bytes sent over the storage interconnect / The number of bytes eligible for predicate offload
TrueOffloadEfficiency%=dividing the number of bytes set over the interconnect to the total read and write bytes for the SQL cursor 

Calculating offload efficiency for session:

select ic_bytes.sid,

            (case when offload_elig.value = 0 then 0

               else 100*(1-(ic_bytes.value/offload_elig.value))

            end) offload1,

            (case when totbytes.value = 0 then 0

               else 100*(1-(ic_bytes.value/totbytes.value))

            end) offload2

              from

               (select sess.sid,stat.name,sess.value

                from   v$sesstat sess, v$statname stat

                where  sess.sid='&&sid' and sess.statistic#=stat.statistic#

                and    stat.name='cell physical IO interconnect bytes') ic_bytes,

               (select sess.sid,stat.name,sess.value

                from v$sesstat sess, v$statname stat

                where  sess.sid='&&sid' and sess.statistic#=stat.statistic#

                and    stat.name='cell physical IO bytes eligible for predicate offload')

                    offload_elig,

               (select sess.sid,sum(sess.value) value

                from v$sesstat sess, v$statname stat

                where  sess.sid='&&sid' and sess.statistic#=stat.statistic#

                and    stat.name in ('physical read bytes','physical write bytes')

                group by sess.sid) totbytes
where ic_bytes.sid=offload_elig.sid

and ic_bytes.sid=totbytes.sid; 

---Nikhil Tatineni---
---Exadata---

Exadata: Smartscan

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--

12C: parameter: temp_undo_enabled

In 11g, when ever temporary tables are updated, undo and redo will be generated.undo tablespace holds the rollback data and online redo stores changed vector.  Here data is logged into online redo and undo 

In 12c, when ever parameter “ temp_undo_enabled”, Undo generated by temporary objects are written to Temp tablespace rather than using undo and redo which helps in performance of database 

"temp_undo_enabled" is dynamic parameter, can be enabled  and disabled  as follows at database level or at session level

sql> alter session set temp_undo_enabled=true;

Session altered.

SQL> alter system set temp_undo_enabled=false;

System altered.

-- when we enable “temp_undo_enabled" parameter and application or user loading into the temporary tables or updating here is no redo generated by DML performed by global temporary tables 

-- when ever you enable this parameter, increase the size of the temporary tablespace 

—NOTE: To implement this parameter,database compatibility should be greater than equal to 12.0.0 

we can monitor the tempundo usage by views "v$tempundostat".

stats for this particular view is updated for every 10 minutes 

To avoid - snapshot too old errors, using "v$undostat" tune the parameter "undo_rentention" on the database 

--Thank you--

--Nikhil Tatineni--

--12c-