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

Measuring Smart Scan Statistics from AWR
To measure your Smart Scan statistics per AWR interval over a period of snapshots, execute the script in Listing 15-4.
Listing 15-4. lst15-04-exass-awr.sql
select stime,icgb,eliggb,ssgb from (
select distinct
to_char(snap.begin_interval_time,'DD-MON-RR HH24:MI') stime,
snaps.icbytes/1024/1024/1024 icgb,
snaps.eligbytes/1024/1024/1024 eliggb,
snaps.ssbytes/1024/1024/1024 ssgb,
myrank
from (
select ss1.snap_id,
(sum(ss1.value) - lag(sum(ss1.value),1,0) over (order by ss1.snap_id)) icbytes,
(sum(ss2.value) - lag(sum(ss2.value),1,0) over (order by ss2.snap_id))
eligbytes,
(sum(ss3.value) - lag(sum(ss3.value),1,0) over (order by ss3.snap_id)) ssbytes,
rank() over (order by ss1.snap_id) myrank
from
dba_hist_sysstat ss1,
dba_hist_sysstat ss2,
dba_hist_sysstat ss3
where ss1.snap_id=ss2.snap_id
and ss2.snap_id=ss3.snap_id
and ss1.snap_id between &&snap_low-1 and &&snap_hi
and ss2.dbid=ss1.dbid
and ss3.dbid=ss2.dbid
and ss1.stat_name='cell physical IO interconnect bytes'
and ss2.stat_name='cell physical IO bytes eligible for predicate offload'
and ss3.stat_name='cell physical IO interconnect bytes returned by Smart Scan'
group by ss1.snap_id,ss2.snap_id,ss3.snap_id
order by ss1.snap_id) snaps,
dba_hist_snapshot snap
where snap.snap_id=snaps.snap_id
order by 1)
where myrank>1;
SnapStart InterconnectGB SmartScanEligibleGB SmartScanReturnedBytes
--------------- -------------- ------------------- ----------------------
10-NOV-12 17:00 3 0 0
10-NOV-12 18:00 1887 6660 1884
10-NOV-12 19:00 2496 7463 2495