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>
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'));
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
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
/
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'
/
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
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.
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy