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

Gathering System Statistics for Exadata
Starting with Exadata patch bundle 18 for 11.2.0.2 and patch bundle 8 with 11.2.0.3, Oracle introduced a new option to
DBMS_STATS.GATHER_SYSTEM_STATS to account for the better performance of full table scans. If you are patched up to
or beyond the versions above, gather your system statistics with the following option:

SYS @ visx1> exec dbms_stats.gather_system_stats(' EXADATA ');
PL/SQL procedure successfully completed.
SYS @ visx1>

This will cause the optimizer to effectively reduce the cost of full table scans when evaluating potential access
paths, favoring full table scans over index scan options.

How It Works
Recall from Oracle's documentation that smart scans happen in the following instances:

• Your query performs a full table scan or fast full index scan.

• The object or objects that you are scanning qualify for direct path reads. Please see

Recipe 15-7 to learn about direct path reads and smart scans.

• Your query does not contain any functions that are not offloadable as is visible in

V$SQLFN_METADATA.OFFLOADABLE .

• Your database storage resides on Exadata cells.

At times, you may be faced with situations when you want Exadata to perform smart scans when it is not or
not perform smart scans when it is. Some of the techniques in this recipe are relatively common for the Exadata
DMA, such as the process of making indexes invisible. Before Exadata's smart I/O and cell offload capabilities, many
Oracle databases were plagued by I/O problems and DBAs and developers attempted to address these issues by
over-indexing their tables in hopes of discouraging full table scans. By testing with invisible indexes, you can confirm
whether your indexes are actually needed for your current workload or SQL statements.
Other tips in this solution are less common and certainly discouraged without extensive testing as they could
impose performance risk to your application and database. Like all Oracle performance advice, there is rarely a “silver
bullet” recommendation that meets the needs of all databases and applications.

15-9. Identifying Partial Cell Offload
Problem
You have a query that you believe should be benefiting significantly from Smart Scan but measures lower than
expected offload efficiencies and evidence of block I/O access methods.

Solution
Partial cell offload takes place when Oracle is able to access some of its requested data via Smart Scan and is required
to access other rows via block shipping. In this recipe, you will learn how to identify whether partial cell offload is
taking place under a variety of conditions.