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
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
where ic_bytes.sid=offload_elig.sid
and ic_bytes.sid=totbytes.sid;
---Nikhil Tatineni---
---Exadata---
