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

To determine how many of an object's blocks are cached in the database buffer cache, execute the script in
Listing 15-12. This script uses the X$KCBOQH fixed table and references its NUM_BUF column.
Listing 15-12. lst15-12-cached-blocks.sql
SQL> select co.object_name object_name,
nvl(co.subobject_name,'N/A') partition_name,
co.cachedblocks, 100*(co.cachedblocks/seg.blocks) cachepct
from (
select owner, object_name,
subobject_name, object_type, sum(num_buf) cachedblocks
from
dba_objects, x$kcboqh
where obj# = data_object_id
and upper(object_name) =upper('&&object_name')
and upper(owner)=upper('&&owner')
group by owner, object_name, subobject_name, object_type) co,
(select owner,segment_name,partition_name,blocks
from dba_segments
where upper(owner)=upper('&&owner') and upper(segment_name)=upper('&&object_name')) seg
where co.owner=seg.owner and co.object_name=seg.segment_name
and nvl(co.subobject_name,'ZZZ')=nvl(seg.partition_name,'ZZZ');
Enter value for object_name: MSC_RESOURCE_REQUIREMENTS
Enter value for owner: MSC
Object Partition CachedBlk Cached%
-------------------------- ----------------------------- ---------- ---------
MSC_RESOURCE_REQUIREMENTS RESOURCE_REQUIREMENTS__21 9 30.47
MSC_RESOURCE_REQUIREMENTS RESOURCE_REQUIREMENTS_0 1 6.25
MSC_RESOURCE_REQUIREMENTS RESOURCE_REQUIREMENTS_999999 7767 23.95
MSC_RESOURCE_REQUIREMENTS RESOURCE_REQUIREMENTS_1 1 6.25
SQL>
From this output, we can see the number of cached blocks for our table and its partitions from the
X$KCBOQH.NUM_BUF column. If the value in this column exceeds 50% of the number of blocks in the object, direct reads
will not take place for full scans.
Adaptive Direct Reads and the Dirty Block Threshold
Similar to the descriptions and example in the previous section, Oracle employs an adaptive direct read mechanism
that assesses whether the number of dirty blocks in the database buffer cache exceeds a threshold, prior to making a
decision to use buffered reads or direct reads. This dirty block threshold, or the “stop dirty” threshold, is defined as
25% of the number of an object's blocks for Oracle 11gR2; in other words, if more than 25% of an objects blocks are
dirty in the buffer cache, subsequent queries that perform full table scans will use buffered reads.
To determine how many of an object's blocks are dirty in the database buffer cache, execute the script in
Listing 15-13. This script counts the number of blocks from V$BH where the DIRTY flag is set to Y .