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

io_interconnect_bytes,
(elapsed_time/1000000)/
executions/
decode(px_servers_executions,0,1,px_servers_executions) avgelapsed
from v$sql
where executions > 0
order by avgelapsed desc)
where rownum < &&rows_to_display + 1;
Enter value for rows_to_display: 6
SqlID AvgElaspedSecs EstOffloadEfficiency% TrueOffloadEfficiency%
------------- --------------- --------------------- --------------------------
6tdgz7h966hnv 159.71 60.86 60.85
3qmc7472q5h91 121.75 32.41 32.15
3qmc7472q5h91 83.62 29.33 29.03
cnwarupsx3z3k 26.49 71.69 71.69
b6usrg82hwsa3 24.06 .00 -2.62
b6jzrwqggzt1u 22.90 60.85 60.84
The fifth row in the output above illustrates an interesting offload efficiency case. In this query, our offload
efficiency as measured by the io_cell_offload_eligible_bytes column is zero, indicating that it was not a
candidate for Smart Scan. However, the true offload efficiency, which is a reflection of the interconnect bytes divided
by physical read plus physical writes, shows a negative number. How can this be? The answer lies in the fact that our
script is not accounting correctly for mirrored writes to Oracle ASM disk groups. On our Exadata Database Machine,
each of our ASM disk groups is configured with normal ASM redundancy, so when write I/Os take place, they are
mirrored. With this knowledge, it is safe to say that SQL ID b6usrg82hwsa3 likely performed direct path writes as a
result of a large sort operation. You can confirm this by running the following query:
SQL> select sql_id,direct_writes,physical_write_bytes,physical_read_bytes,io_interconnect_bytes
from v$sql where sql_id='b6usrg82hwsa3'
/
SqlID DirectWrites WriteBytes ReadBytes InterconnectBytes
------------- ------------ ---------------- ---------------- -----------------
b6usrg82hwsa3 15,728 128,843,776 4,782,235,648 5,039,923,200
SQL>
Once you've confirmed that you have performed direct writes and you know your ASM disk group redundancy
level, you can multiply the physical_write_bytes by the redundancy level to calculate the true number of write
bytes. The following script multiplies the write bytes by two and, as you can see, the statistics match:
SQL> select sql_id,(physical_read_bytes + physical_write_bytes*2) totbytes, io_interconnect_bytes
from v$sql where sql_id='b6usrg82hwsa3'
/
SqlID Total Bytes InterconnectBytes
------------- ---------------- -----------------
b6usrg82hwsa3 5,039,923,200 5,039,923,200
SQL>