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

Testing Direct Path Reads Based on Your _small_table_threshold Setting
For testing purposes, select a few tables whose number of blocks is a little bit below and a little bit above your
_small_table_threshold setting:

SYS @ visx1> select owner,table_name,
blocks from dba_tables where blocks between 31000 and 33000;

OWNER TABLE_NAME BLOCKS
-------- ---------------------------- ----------
MSC MSC_RESOURCE_REQUIREMENTS 32560
FEM FEM_BALANCES 31328

In our test cases, we will use FEM.FEM_BALANCES to confirm buffer read access and
MSC.MSC_RESOURCE_REQUIREMENTS to validate direct reads. To validate, query the physical reads direct statistic before
and after running a full table scan on the segment. An example is provided:

APPS @ visx1> alter system flush buffer_cache;
System altered.
APPS @ visx1> select /*+ full (t) */ count(*) from fem.fem_balances t;

COUNT(*)
----------
1217591
1 row selected.
APPS @ visx1> select

stat.name,sess.value

2 from v$statname stat, v$mystat sess

3 where stat.statistic#=sess.statistic#

4 and stat.name='physical reads direct';

NAME VALUE
----------------------------- ----------
physical reads direct 0

1 row selected.
APPS @ visx1>

APPS @ visx1> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.14
APPS @ visx1> select /*+ full (t) */ count(*) from msc.msc_resource_requirements t;

COUNT(*)
----------
1057766
1 row selected.
APPS @ visx1> select stat.name,sess.value

2 from v$statname stat, v$mystat sess

3 where stat.statistic#=sess.statistic#

4 and stat.name='physical reads direct';