Databases Reference
In-Depth Information
Measuring Partial Cell Offload Due to Consistent Reads for a Session
Check for the following conditions for a specific session:
cell single block physical read
or
cell multiblock physical read
wait
events from
V$SESSION_EVENT
or other wait interface views and compare to the
cell smart
table scan
or
cell smart index scan
wait events
•
Look for
consistent gets
and
session logical reads
from
V$SESSTAT
•
Look for
Measuring Partial Cell Offload Due to Consistent Reads for a SQL Statement
To determine whether you are experiencing partial cell offload for a specific SQL statement, examine
V$SQL
and
V$SQLSTAT
for non-zero values for
io_cell_offload_eligible_bytes
combined with large non-zero values for
buffer_gets
.
Measuring Partial Cell Offload Due to Chained Rows
Check for the following conditions for a specific session:
cell single block physical read
or
cell multiblock physical read
wait
events from
V$SESSION_EVENT
or other wait interface views and compare to the
cell smart
table scan
or
cell smart index scan
wait events
•
Look for
consistent gets
and
session logical reads
from
V$SESSTAT
•
Look for
•
Look for non-zero values for the table fetch continued row statistic
Measuring Partial Cell Offload Due to Overloaded Storage Cells
When Oracle determines that the storage cells are too busy to perform smart scans, the
cell num smart IO sessions
in rdbms block IO due to no cell mem
or
cell num smart IO sessions in rdbms block IO due to big
payload
system or session counters will be incremented. You can check
V$SYSSTAT
or
V$SESSTAT
to confirm if this is
the case.
How It Works
Partial cell offload happens when a portion of the data requested is serviced via Smart Scan and a portion is fetched
with traditional block I/O methods. At a very simplistic level, the following two statements are true:
•
Exadata is a row and column server when data is accessed via Smart Scan.
•
Exadata is a block server when data is accessed without Smart Scan.
There are a number of situations in which Exadata reverts to block shipping mode, even for situations in which
smart scans should be performed. Exadata can choose or be forced to revert to block shipping mode any time a
situation occurs in which Oracle has to read another block to complete its unit of work. One of the more common
events is when a chained row is accessed; when Oracle reads a block that contains a chained row, it will follow the
pointer to the block containing the row piece to complete the operation. Since this row piece could physically reside
on a different storage cell, Oracle must perform a block I/O request and load the blocks into the database buffer
cache. Individual storage cells do not communicate with each other so, in this case, Oracle simply ships the entire
block back to the database tier and allows the database instance to handle the I/O processing.
Search WWH ::
Custom Search