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.