Monitoring Exadata Smart Scan

Exadata Smart Scan processing is one of the key components of Exadata’s “secret sauce”. It enables certain operations to be offloaded to the storage cell, reducing the amount of work required on the database tier nodes and reducing the impact of shipping blocks over the storage interconnect. Scripts are provided in this that can be used to monitor your own Exadata environment.

In order to take advantage of Smart Scan features of Exadata, the initialization parameter cell_offload_processing must be set to TRUE.

SQL> conn / as sysdba
Connected.
SQL> show parameter cell_offload_pr

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
cell_offload_processing boolean TRUE
SQL>

For EXPLAIN PLAN/SQL Plan output to show details about Smart Scan processing, cell_offload_plan_display must be set to AUTO or ALWAYS; when set to AUTO, dbms_xplan or EXPLAIN PLAN will show offload processing information if the optimizer found the SQL statement was available for Smart Scan processing. When set to ALWAYS, Oracle will show cell offload processing details regardless of whether the database runs on Exadata – so with this, you can take non-Exadata 11gR2 EXPLAIN PLANS and display offload processing statistics under the assumption it was running on Exadata. This is useful when analyzing current, pre-Exadata execution plans and is in fact how the Exadata Simulation components of SQL Performance Analyzer functions in Oracle Enterprise Manager post 10.2.0.4

Below are some examples of these two initialization parameters and how they impact execution plan details. In this example, all scripts are contained in /home/oracle/sqlmon directory on cm01dbm01.centroid.com.

SQL> show parameter cell_offload_p
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
SQL> @exp
SQL> set lines 150
SQL> set echo on
SQL> explain plan for
2 select *
3 FROM
4 soe.orders;
Explained.

SQL> select * from
table(dbms_xplan.display(null, null,'basic'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 70492019
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION HASH ALL | |
| 2 | TABLE ACCESS STORAGE FULL| ORDERS |ям яз
---------------------------------------------

9 rows selected.

SQL>
Now we'll set cell_offload_plan_display to
NEVER:
SQL> alter session set
cell_offload_plan_display=NEVER;

Session altered.

SQL> @exp

Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 70492019

-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION HASH ALL| |
| 2 | TABLE ACCESS FULL| ORDERS |ям яз
-------------------------------------

9 rows selected.

SQL>
Next, let's set cell_offload_processing=FALSE.
As you can see below, the plan display still shows "TABLE ACCESS
STORAGE FULL":
SQL> show parameter cell_offload_p
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
cell_offload_parameters string
cell_offload_plan_display string AUTO
cell_offload_processing boolean TRUE
SQL> alter session set
cell_offload_processing=FALSE;
Session altered.
SQL> @exp
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 70492019
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION HASH ALL | |
| 2 | TABLE ACCESS STORAGE FULL| ORDERS |ям яз
---------------------------------------------
9 rows selected.
SQL>

This begs this question – with cell_offload_processing=FALSE, would Oracle still do smart scan processing at run-time, or are we seeing this just because cell_offload_plan_display=AUTO? Let’s prepare the script below so we can grab the actual run-time execution plan.

In this test, we’ll run a series of “select count(*) from soe.orders” with cell_offload_processing=FALSE in one window, grab the session ID in another window while running, and run gp.sql against the SID to retrieve the execution plan from the library cache. From the session running the query:

SQL> show parameter cell_offload_proce
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
cell_offload_processing boolean FALSE
SQL> declare
2 x number;
3 begin
4 for i in 1..100 loop
5 select /*+ full (X) */ count(*) into x from
soe.orders X;
6 end loop;
7* end;
SQL> /
From the session running gp.sql to get execution
plan:
SQL_ID aznukcm9u6ndd, child number 0
-------------------------------------
SELECT /*+ full (X) */ COUNT(*) FROM SOE.ORDERS
X

Plan hash value: 2424864786

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 63265 (100)| | |
|
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION HASH ALL | | 51M| 63265 (1)|
00:12:40 | 1 | 16 |
| 3 | TABLE ACCESS STORAGE FULL| ORDERS | 51M|
63265 (1)| 00:12:40 | 1 | 16 |

 

Interestingly, it still appears to be doing smart scan processing by nature of doing a TABLE ACCESS STORAGE FULL step. The reason is simple – with cell_offload_plan_display set to AUTO and the table’s contents being stored on Exadata cells, the explain plan display will always show a “TABLE ACCESS STORAGE FULL” output whether it’s using Smart Scan processing or not. In order to determine whether it actually did smart scan processing or not, we need to check V$SQL using the script below:

If we run the tests below and use
sql_offload.sql above, we see these results:
SQL> alter system flush buffer_cache
2 /
System altered.

Elapsed: 00:00:02.03
SQL> select /* jc_test */ /*+ full (X)
opt_param('cell_offload_processing' 'false') */ count(*)
2 from soe.orders X
3 /

COUNT(*)
----------
51169934

Elapsed: 00:00:10.79
SQL> alter system flush buffer_cache
2 /
System altered.

Elapsed: 00:00:00.24
SQL> select /* jc_test */ /*+ full (X)
opt_param('cell_offload_processing' 'true') */ count(*)
2 from soe.orders X
3 /

COUNT(*)
----------
51169934
Elapsed: 00:00:02.78
SQL>
SQL>

SQL> @sqlid_offload
Enter value for sql_text: jc_test
old 8: where upper(sql_text) like
'%'||upper('&sql_text')||'%'
new 8: where upper(sql_text) like
'%'||upper('jc_test')||'%'
SQL_ID PHV EXECS AVG_ELAP OFF IO Saved %
SQL_TEXT
------------- ---------- ------ ---------- ---
----------
--------------------------------------------------------------------------------
4qfdfkfbuz0xg 2424864786 1 10.79 No .00 select
/* jc_test */ /*+ full (X) opt_param('cell_offload_processing'
'false')
991hr2nbnqxcm 2424864786 1 2.78 Yes 68.58 select
/* jc_test */ /*+ full (X) opt_param('cell_offload_processing'
'true') *

SQL>

Script: exp.sql

exp.sql shows the explain plan of a SQL statement entered.

set lines 150
set echo off
explain plan for
select *
FROM
soe.orders;
select * from table(dbms_xplan.display(null,
null,'all'));

 

Script: sql_offload.sql

sql_offload.sql shows the offload processing
details for a given SQL statement or SQL_ID.
set pagesize 999
set lines 190
col sql_text format a80 trunc
col execs format 9,999
col avg_elap format 99,999.99
col io_save_pct format 999.99 head "IO Saved
%"
col offload for a7

 

select sql_id, plan_hash_value phv, executions
execs,
(elapsed_time/1000000)/executions avg_elap,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') offl,
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_save_pct,
sql_text
from v$sql s
where executions > 0
and upper(sql_text) like
'%'||upper('&sql_text')||'%'
and sql_text not like 'select sql_id%'
and sql_text not like 'BEGIN :sql_text := %'
and sql_text not like
'%IO_CELL_OFFLOAD_ELIGIBLE_BYTES%'
and sql_id=nvl(&sql_id,sql_id)
order by 1, 2, 3
/
undefine sql_text
undefine sql_id

Script: gp.sql

gp.sql shows the active execution plan from the
library cache for an entered session ID.
set lines 200
set pagesize 999
col sql_text format a80 trunc
col execs format 9,999
col avg_elap format 99,999.99
col elapsed format 999,999,999
col io_save_pct format 999.99 head "IO Saved
%"
col offload for a7

select expl.*
from
gv$sql sql, v$session ses,
TABLE(dbms_xplan.display_cursor(sql.sql_id,
sql.child_number,format=>'typical +predicate')) expl
where ses.sql_address = sql.address and ses.sid
= &&1
and sql.optimizer_cost is not null
/

select s.sql_id, s.plan_hash_value phv,
s.executions execs,
s.elapsed_time/1000000 elapsed,
decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') offl,
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_save_pct,
sql_text
from v$sql s, v$session sess
where 1=1
and sess.sql_address=s.address
and sess.sid=&&1
order by 1, 2, 3
/

Script: exa_ss_sysstat.sql

exa_ss_sysstat.sql shows system-wide statistics for Smart Scan.

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select inst.instance_name,
b.name,
value
from gv$sysstat a,
gv$statname b, gv$instance inst
where a.statistic# = b.statistic#
and b.name in ('cell physical IO interconnect
bytes returned by smart scan',
'cell physical IO interconnect bytes',
'cell physical IO bytes eligible for predicate
offload')
and a.value > 0
and inst.inst_id=a.inst_id
and inst.inst_id=b.inst_id
order by 2 desc
/
col val1 format 999,999,999,999,999 head
'Eligible MB'
col val2 format 999,999,999,999,999 head 'SS
Returned MB'
col pctsaved format 999.99 head 'Pct Saved'
select inst.instance_name,
eli.value/1024 val1, ret.value/1024 val2,
100*(1-((ret.value/1024)/((eli.value/1024))))
pctsaved
from gv$sysstat eli,
gv$sysstat ret,
gv$statname a, gv$statname b,
gv$instance inst
where eli.statistic#=a.statistic#
and ret.statistic#=b.statistic#
and inst.inst_id=eli.inst_id
and inst.inst_id=ret.inst_id
and inst.inst_id=a.inst_id
and inst.inst_id=b.inst_id
and a.name='cell physical IO bytes eligible for
predicate offload'
and b.name='cell physical IO interconnect
bytes'
/

 

Script: exa_ss_sesstat.sql

exa_ss_sesstat.sql shows session-level statistics for Smart Scan.


set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from v$sesstat a,
v$statname b
where a.statistic# = b.statistic#
and a.sid = &&sid
and b.name in (
'cell physical IO interconnect bytes returned by
smart scan',
'cell physical IO interconnect bytes',
'cell physical IO bytes eligible for predicate
offload'
)
and a.value > 0
order by 2 desc
/
col val1 format 999,999,999,999,999 head
'Eligible MB'
col val2 format 999,999,999,999,999 head 'SS
Returned MB'
col pctsaved format 999.99 head 'Pct Saved'
select
eli.value/1024 val1, ret.value/1024 val2,
100*(1-((ret.value/1024)/((eli.value/1024))))
pctsaved
from v$sesstat eli,
v$sesstat ret,
v$statname a, v$statname b
where eli.value > 0
and eli.statistic#=a.statistic#
and ret.statistic#=b.statistic#
and a.name='cell physical IO bytes eligible for
predicate offload'
and b.name='cell physical IO interconnect
bytes'
and eli.sid=&&sid
and ret.sid=&&sid
/
undefine sid

Script: exa_ss_mystat.sql

exa_ss_mystat.sql shows session-level statistics for the current session, most often used when testing.

 

set lines 200
col name format a70
col value format 999,999,999,999,999
col stat format a20
select b.name,
value
from v$mystat a,
v$statname b
where a.statistic# = b.statistic#
and b.name in (
'cell physical IO interconnect bytes returned by
smart scan',
'cell physical IO interconnect bytes',
'cell physical IO bytes eligible for predicate
offload'
)
and a.value > 0
order by 2 desc
/
col val1 format 999,999,999,999,999 head
'Eligible MB'
col val2 format 999,999,999,999,999 head 'SS
Returned MB'
col pctsaved format 999.99 head 'Pct Saved'
select
eli.value/1024 val1, ret.value/1024 val2,
100*(1-((ret.value/1024)/((eli.value/1024))))
pctsaved
from v$mystat eli,
v$mystat ret,
v$statname a, v$statname b
where eli.value > 0
and eli.statistic#=a.statistic#
and ret.statistic#=b.statistic#
and a.name='cell physical IO bytes eligible for
predicate offload'
and b.name='cell physical IO interconnect
bytes'
/
undefine sid

 

Summary

Measuring impact of Exadata Smart Scan and cell offload processing is vital to understanding how well your Exadata Database Machine is working in relation to non-Exadata solutions.