First page Back Continue Last page Graphics
Full Table Scans…
Sequential read of blocks in segment
Reads all blocks that contain, or used to contain, data
- Up to the “high water mark”
Uses multi-block reads
- DB_FILE_MULTIBLOCK_READ_COUNT
- How many blocks are read per I/O operation
CBO cost calc
Notes:
A full scan reads blocks in the segment that contain or once contained data.
Full table scans use multi-block reads – multiple Oracle blocks are read in one disk I/O operation. The INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT specifies how many blocks are read per I/O operation. Setting this parameter to a large value will decrease I/O’s for full table scans and thus increase the likelihood of using a full table scan.
CBO cost calculation for a full tablescan is something* like: full scan cost = mreadtim * (HWM / MBRC ) / sreadtim
Notes:
The cost calculation is not documented by Oracle; this is a reasonable estimation by Jonathan Lewis (www.jlcomp.demon.co.uk).
HWM is high-water mark and is discussed later in this lesson.
MBRC is the multi-block read count value for your session or server.
mreadtim and sreadtim are the multi-block read time and single-block read time respectively from SYS.AUX_STATS$.
This calculation assumes you’ve collected system statistics for multi-block read time and single-block read time. Without system statistics the cost formula is more or less unknown, though some people claim to have deciphered it.
The divide by sreadtim is just to normalize the cost to single block cost.