My Oracle Support Banner

Full Table Scans on a Table Reading One Block at a Time. (Due To Chained / Migrated Rows) (Doc ID 554366.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.1.0.6 [Release 8.1.7 to 11.1]
Information in this document applies to any platform.

Symptoms

Parameter DB_FILE_MULTIBLOCK_READ_COUNT is set to  16.

Here is an example:

select *  from table;



This is the raw 10046 trace for the sql performing full table scan on the problem table.

The way the block#  reads jumps around all over the place, and  one block read at a time  “blocks=1”. Inspite of the parameter DB_FILE_MULTIBLOCK_READ_COUNT is set to 16.

It means that Oracle will have to do a lot of single, non sequential block reads to completely read this table, as the 10046 shows:

WAIT #4: nam='db file sequential read' ela= 25647 file#=77 block#=97306 blocks=1 obj#=6196335
tim=12301397488985
WAIT #4: nam='db file sequential read' ela= 51952 file#=77 block#=136116 blocks=1 obj#=6196335
tim=12301397541429
WAIT #4: nam='db file sequential read' ela= 31040 file#=77 block#=115655 blocks=1 obj#=6196335
tim=12301397572790
WAIT #4: nam='db file sequential read' ela= 45165 file#=77 block#=115739 blocks=1 obj#=6196335
tim=12301397618396
WAIT #4: nam='db file sequential read' ela= 23976 file#=77 block#=119185 blocks=1 obj#=6196335
tim=12301397642727




Compare this trace to a full table scan of another object in the same schema. It reads sequentially, and reads 16 blocks at a time ( “blocks=16”).

WAIT #2: nam='db file scattered read' ela= 3179 file#=22 block#=57959 blocks=16 obj#=7535
tim=6260909375147
WAIT #2: nam='db file scattered read' ela= 3152 file#=22 block#=57975 blocks=16 obj#=7535
tim=6260909381760
WAIT #2: nam='db file scattered read' ela= 3651 file#=22 block#=57991 blocks=16 obj#=7535
tim=6260909388680
WAIT #2: nam='db file scattered read' ela= 3291 file#=22 block#=58007 blocks=16 obj#=7535
tim=6260909395512
WAIT #2: nam='db file scattered read' ela= 3040 file#=22 block#=58023 blocks=16 obj#=7535
tim=6260909401859

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.