Smart Scan and Cell Offload - Oracle Exadata Recipes: A Problem-Solution Approach - page 448

Calculating Offload Efficiency for a SQL Statement
To measure the offload efficiency for an individual SQL statement, execute the script in Listing 15-8. In this script,
we are reporting the offload efficiency in two ways: first, by dividing the number of bytes sent over the storage
interconnect by the number of bytes eligible for predicate offload and, second, but dividing the number of bytes set
over the interconnect to the total read and write bytes for the SQL cursor. This is done to not only account for the fact
that not all SQL statements will be offloadable and additionally, but also recognize that there are times when SQL
statements are only partially offloadable or may incur write I/O as a result of sort operations.

Listing 15-8. lst15-08-offloadeff-sqlid.sql

SQL> 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');

Enter value for sqlid: 9ygh669cakd8c
SqlID EstOffloadEfficiency% TrueOffloadEfficiency%
------------- --------------------- ----------------------
9ygh669cakd8c 95.62 95.62

Calculating Offload Efficiencies for Multiple SQL Statements
To generate a report of all of your cached SQL statements and their corresponding offload efficiencies, execute the
script in Listing 15-9. In this script, we are only displaying the “Top N” queries by elapsed time, which is provided as
an input prompt to the script.

Listing 15-9. lst15-09-offloadeff-sqls.sql

SQL> select sql_id, avgelapsed,
(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,