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

Listing 15-5. lst15-05-exass-vsql.sql
SQL> select sql_id,
(elapsed_time/1000000)/executions avgelapsed,
decode(io_cell_offload_eligible_bytes,0,'No','Yes') eligible,
io_interconnect_bytes/1024/1024/1024 icgb,
io_cell_offload_returned_bytes/1024/1024/1024 ssgb,
sql_text
from gv$sql s
where sql_id='&&sql_id'
order by 1, 2, 3;
/
SqlID ChildN AvgSecs Offloaded IO(GB) SS(GB) SqlText
------------- ------ ------- --------- ------ ------ ----------------------------------------
1xxbw6b88uc77 0 2.46 Yes .14 .14 select /* jc1 */ /*+ full (mmt) */ count
In this output, we are displaying the SQL ID, child cursor number, the average elapsed seconds, a Yes or
No indication of whether the SQL statement was offloaded, the number of I/O gigabytes transmitted over the
interconnect, the number of gigabytes transmitted via Smart Scan, and a bit of the SQL statement text. For example,
if you wish to report cell I/O and offload statistics for a given SQL text, use the V$SQL.SQL_TEXT column in your
query predicate. To list the top ten SQL statements by average elapsed time and report on their cell I/O and offload
eligibility, execute the script in Listing 15-6. This type of analysis is helpful in determining what your top time-
consuming SQL statements are (in cache) but also identify whether they were offloaded.
Listing 15-6. lst15-06-exass-allvsql.sql
SQL> select sql_id, child_number,
(elapsed_time/1000000)/
executions/
decode(px_servers_executions,0,1,px_servers_executions) avgelapsed,
decode(io_cell_offload_eligible_bytes,0,'No','Yes') eligible,
io_interconnect_bytes/1024/1024/1024 icgb,
io_cell_offload_returned_bytes/1024/1024/1024 ssgb,
sql_text
from gv$sql s
where executions > 0
order by 3 desc
/
SqlID ChildN AvgSecs Offloaded IO(GB) SS(GB) SqlText
------------ ------ ------- --------- -------- -------- ----------------------------------------
6tdgz7h966hnv 1 159.71 Yes 173.15 173.25 select count(*) from d14.DWB_
3qmc7472q5h91 2 121.75 Yes 1,485.66 1,494.09 select sum(qty) from dwb_rtl_sls_retrn_l
3qmc7472q5h91 1 83.62 Yes 2,178.07 2,191.16 select sum(qty) from dwb_rtl_sls_retrn_l
3qmc7472q5h91 2 43.82 Yes 3,529.50 3,543.58 select sum(qty) from dwb_rtl_sls_retrn_l
... Output omitted for brevity