First page Back Continue Last page Graphics

Access Paths Workshop: Full Table Scans

Full Table Scans

Notes:

Workshop – Full Table Scan
Review, modify where necessary and execute the supplied script workshop_full_scan.sql. This script will help determine the best multiblock read count for your server. It changes the multiblock read count, then executes a full table scan on the big table.
After modifying and executing the script, analyze the TKPROF traces to determine the MBRC with the lowest WAIT count (waiting for disk IO) and best elapsed time. Specifically, open the trace file and search for “mbrc” to find the stats for the query:
select /*+ full(big) mbrc=0 */ count(*) into v1
from big
When you find the statistics for the query, compare the following statistics:
db file sequential read (single block IO), Times Waited and Total Waited
db file scattered read (multi-block IO ), Times Waited and Total Waited
elapsed time
Can you determine the best multiblock read count for your server?