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

15-7. Qualifying for Direct Path Reads
Problem
You wish to determine whether a SQL statement or workload will qualify for direct path reads in order to assess
whether Exadata Smart Scan will be leverage for full scans against the tables in your query.

Solution
In this recipe, you will learn how to determine the conditions and thresholds upon which direct path reads are
invoked for your SQL workload. Smart scans require full scans and either serial or parallel direct path read operations,
so it is important to be able to predict whether a query will execute with direct path reads to qualify for Smart Scan.

Measuring Your Direct Read Threshold
In 11gR2, when a SQL statement executes with a full table scan or fast full index scan operation, Oracle will attempt
to use direct path reads instead of buffered reads when the number of blocks in the table or index exceeds the
_small_table_threshold initialization parameter. This parameter defaults to 2% of the size of your database buffer
cache. Use the following script to determine your small table threshold and database buffer cache size:

SYS @ visx1> select a.ksppinm name, b.ksppstvl value, b.ksppstdf isdefault
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
and (a.ksppinm ='_small_table_threshold'
or a.ksppinm='__db_cache_size')
order by 1,2
/
__db_cache_size 14294188032 FALSE
_small_table_threshold 32389 TRUE
2 rows selected.
SYS @ visx1>
SYS @ visx1> show parameter db_block_size
db_block_size integer 8192
SYS @ visx1>

In this output, our database buffer cache size is approximately 13.3 GB and, with an 8 KB block size, we can see
that our _small_table_threshold value is roughly 2% of the size of the database buffer cache. For our database, we
have sga_target set and db_cache_size unset, so Oracle has dynamically adjusted the buffer cache size based on its
Automatic Shared Memory Management (ASMM) calculations based on the database's workload:

_small_table_threshold = 2% * (14294188032 / 8192 ) = 34898 =~ 32389

What this means for our database is that for objects access via full-scan operations, Oracle will attempt to use
direct path reads if the number of blocks exceeds 32,389.

■ In Oracle 11gr1, direct reads were invoked when the size of the object exceeded five times the small table
threshold value. With 11gr2, Oracle is more aggressive with direct reads and uses the _small_table_threshold setting
as the threshold.

Note