-- Purpose:     Find SQL in AWR and report whether it was Offloaded and % of I/O saved.
/*
set verify off
set pagesize 999
set lines 190
col inst format 9999 
col sid format 999
col sql_text format a70 trunc
col sid format 999
col child_number format 99999 heading CHILD
col execs format 9,999,999
col avg_etime format 9,999,999.99
col avg_cpu  format 9,999,999.99
col avg_lio format 999,999,999
col avg_pio format 999,999,999
col "IO_SAVED_%" format 999.99
col avg_px format 999
col offloadable for a11

accept sqltext -
       prompt 'Enter value for sql_text: ' 
accept sqlid  -
       prompt 'Enter value for sql_id: ' 
accept instid  -
       prompt 'Enter value for inst_id: ' 

set feedback off
variable sql_id  varchar2(30)
variable sql_text  varchar2(280)
variable inst_id  varchar2(10)
exec :sql_id := '&&sqlid';
exec :sql_text := '&&sqltext';
exec :inst_id := '&&instid';
set feedback on

select sql_id, child_number, plan_hash_value plan_hash, executions execs, 
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, 
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offloadable,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,0,
100*(IO_CELL_OFFLOAD_ELIGIBLE_BYTES-IO_INTERCONNECT_BYTES)
/decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,1,IO_CELL_OFFLOAD_ELIGIBLE_BYTES)) "IO_SAVED_%",
sql_text
from DBA_HIST_SQLSTAT 
where upper(sql_text) like upper(nvl(:sql_text,sql_text))
and sql_text not like 'BEGIN :sql_text := %'
and sql_id like nvl(:sql_id,sql_id)
order by 1, 2, 3
*/

undef sqlid
undef sqltext
undef instid
undef sql_id
undef sql_text
undef inst_id

set long 32000
set lines 155
col sql_text format a40 
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col lio for 999,999,999,999
col avg_lio for 999,999,999,999
col avg_pio for 999,999,999,999
col rows_proc for 999,999,999,999 head rows
col begin_interval_time for a30
col node for 99999
col versions for 99999
col percent_of_total for 999.99
break on report
compute sum of percent_of_total on report
select sql_id, sql_text, avg_pio, avg_lio, avg_etime, execs, rows_proc, offloadable
from (
select dbms_lob.substr(sql_text,3999,1) sql_text, b.*
from dba_hist_sqltext a, (
select sql_id, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(pio)/sum(execs) avg_pio, 
sum(lio)/sum(execs) avg_lio, sum(rows_proc) rows_proc, offloadable
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, 
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
rows_processed_delta rows_proc,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
decode(IO_OFFLOAD_ELIG_BYTES_DELTA,0,'No','Yes') Offloadable,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 
ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and ss.snap_id between nvl('&starting_snap_id',0) and nvl('&ending_snap_id',999999999)
and executions_delta > 0
)
group by sql_id,offloadable
order by 5 desc
) b
where a.sql_id = b.sql_id
and execs > 1
)
where rownum <31
and sql_text like nvl('&sql_text',sql_text)
and sql_id like nvl('&sql_id',sql_id)
-- group by sql_id, sql_text
order by etime desc
/