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

select −1,
stat.name,
round(sum(sess.value)/1024/1024/1024,2) value
from v$sesstat sess,
v$statname stat
where stat.statistic# = sess.statistic#
and sess.sid in (select sid from v$px_session where qcsid='&&sid')
and stat.name in
('cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by Smart Scan')
group by stat.name
order by 1 desc,2;
SID Statistic Value (GB)
----- ------------------------------------------------------------ -------------
914 cell physical IO bytes eligible for predicate offload 2,648
914 cell physical IO interconnect bytes 749
914 cell physical IO interconnect bytes returned by Smart Scan 749
The script accepts a &&sid input parameter and uses V$SESSTAT to report on Smart Scan statistics. In addition to
listing statistics from V$SESSTAT for the specific session ID, the script will report summarized Smart Scan statistics for
all parallel query slaves in the event that the session is a parallel query coordinator and is actively running a parallel
query operation. Oracle increments system statistics for the parallel query coordinator at the completion of the
parallel query, while the counters for each parallel query slave are incremented in real time.
Measuring Smart Scan Statistics for Your Session
To collect Smart Scan statistics from your own session, use the V$ MYSTAT view. While logged in to to SQL*Plus and
ideally, after running a query or two, execute the script in Listing 15-3.
Listing 15-3. lst15-03-exass-mysess.sql
SQL> select stat.name,
round(sess.value/1024/1024/1024,2) value
from v$mystat sess,
v$statname stat
where stat.statistic# = sess.statistic#
and stat.name in
('cell physical IO bytes eligible for predicate offload',
'cell physical IO interconnect bytes',
'cell physical IO interconnect bytes returned by Smart Scan')
order by 1;
Statistic Value (GB)
------------------------------------------------------------ -------------
cell physical IO bytes eligible for predicate offload 883
cell physical IO interconnect bytes 346
cell physical IO interconnect bytes returned by Smart Scan 346