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

Chapter 15
Smart Scan and Cell Offload
Smart Scan is probably the most important performance component in Exadata's software offering in terms of
delivering extreme performance. Smart Scan is a cell offload feature whose goal is to offload processing to storage
cells instead of performing block I/O requests in the database tier. The goal of Smart Scan processing is to do all the
“heavy lifting”, I/O-wise, in the storage cell and only send back data to the compute nodes that is actually needed and
requested. This reduces interconnect traffic between the storage grid and database tier, reduces the clutter in the
database buffer cache, improves query performance, and improves concurrency for multiple full-scan types of queries
happening at once. With smart scan processing, row filtering, column filtering, some join processing, and other
functions are performed in the Exadata cells.
In this chapter we'll present several examples of Smart Scan, show how to control it, provide tips on how to
measure it and calculate its effectiveness, and discuss various qualifying conditions for Smart Scan to be used.
15-1. Identifying Cell Offload in Execution Plans
Problem
You wish to identify Smart Scan and cell offload behavior in SQL execution plans.
Solution
In this recipe, you will learn how to identify cell offload or Smart Scan behavior in SQL execution plans and in an
EXPLAIN PLAN output. There are a number of ways to display SQL plans, including using DBMS_XPLAN , extracting the
plans from Enterprise Manager, querying and formatting data from V$SQL_PLAN, and so forth, but in this recipe we
use AUTOTRACE from SQL*Plus for simplicity. Identify a table to conduct a full table scan query against, enter a hint if
necessary to bypass index access, and execute the following statements from a database running on Exadata:
SQL> set lines 200
SQL> set autotrace on
SQL> select /*+ full (f) */ count(*)
2 from fnd_user f;