Databases Reference
In-Depth Information
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';
NAME VALUE
-------------------------- ----------
physical reads direct 0
1 row selected.
APPS @ visx1>
If you gather or seed object statistics with accurate block count numbers and if the block count is larger than the
small table threshold, full scans will be performed with direct path reads assuming that the cached block and dirty
block thresholds discussed in the previous sections are not surpassed.
How It Works
On Exadata, smart scans require direct path reads—this logic resides in Oracle's I/O code path. Direct path reads
come in two flavors: parallel direct reads and serial direct reads. Parallel query operations perform parallel direct
reads unless In Memory Parallel Execution buffers parallel read blocks, as discussed in Recipe 21-8. Serial direct reads
occur when an object is accessed via full scan in serial and is subject to the small table threshold and adaptive direct
read algorithms. Direct reads work by fetching the required data into the foreground session's PGA. With buffered
reads, blocks are loaded into the database buffer cache.
In 11g, Oracle introduced the serial direct read functionality to improve performance for full table scan
operations and mitigate the impact of polluting the database buffer cache with blocks accessed via full scans. This
direct read functionality for full scans is generic to Oracle 11g, but Exadata's Smart Scan code exploits this generic
performance feature.
The choice of whether to perform a direct path read is made at runtime by Oracle. The optimizer itself has no
influence on whether or not direct reads are performed, but rather, the number of blocks in the object combined with
the number of cached or dirty blocks is used to determine whether to perform direct reads. This choice is made on a
per SQL statement basis for each object upon which the optimizer elects to perform a full scan.
Why are direct path reads so important on Exadata? The answer is that smart scans depend on direct path reads,
and Smart Scan is probably the most important performance feature delivered by Exadata. So, for smart scans to take
place, the following steps need to be taken:
1.
The optimizer must elect to perform a full table scan or fast full index scan. This choice
is dependent on a number of factors including the existence of indexes on a table, query
predicates, and recently, Exadata-specific system statistics that effectively reduce the cost
of a full table scan (see Recipe 21-8 for additional details).
2.
The size of the object being scanned must be larger than 2% of the size of the database
buffer cache, and fewer than 50% of the blocks are cached in the buffer cache and fewer
than 25% of the blocks are dirty in the buffer cache.
3.
If the previous conditions are met, the object can be accessed with direct path reads when
accessed in serial. When using parallel query, direct reads are used unless In Memory
Parallel Execution is used.
Search WWH ::
Custom Search