How small should the table be for reading it to change from db file scattered read to sequential read? Answer: Not surprisingly, when the table is only 1 block (excluding header blocks) I did a test on Oracle 9.2.0.1.0 (Windows XP). Create a file named test.sql which has these two lines (set termout off so you won't see all rows on screen): set term off select * from t; Start sqlplus from this directory. sqlplus -- Repeat this block but when repeated, -- change the two steps drop and recreate table to: -- delete from t where a > [some number] -- alter table t move; -- to rebuild the table lowering HWM conn yong/yong drop table t; create table t as select rownum a from dba_objects; exec dbms_stats.gather_table_stats('YONG', 'T') select num_rows, blocks, empty_blocks from user_tables where table_name = 'T'; alter tablespace users offline; -- T's tablespace is USERS alter tablespace users online; alter session set events '10046 trace name context forever, level 8'; @test alter session set events '10046 trace name context off'; -- End Repeat block Run grep 'db file ' [trace file] Observation: When user_tables.blocks = 7, then 5 blocks are read (add up p3): WAIT #1: nam='db file sequential read' ela= 10791 p1=8 p2=1331 p3=1 WAIT #1: nam='db file scattered read' ela= 328 p1=8 p2=1332 p3=4 When it's 5, 3 are read: WAIT #1: nam='db file sequential read' ela= 18544 p1=8 p2=1347 p3=1 WAIT #1: nam='db file scattered read' ela= 231 p1=8 p2=1348 p3=2 When it's 4, 2 are read and the wait event changes: WAIT #1: nam='db file sequential read' ela= 16382 p1=8 p2=1347 p3=1 WAIT #1: nam='db file sequential read' ela= 203 p1=8 p2=1348 p3=1 The first block of the segment as shown in dba_extents where segment_name='T' is FIRST LEVEL BITMAP BLOCK (the tablespace is ASSM). The second is SECOND LEVEL BITMAP BLOCK. The third is PAGETABLE SEGMENT HEADER. It's always (user_tables.blocks - 2) blocks that are actually read, i.e. PAGETABLE SEGMENT HEADER and the real data blocks are read. The first two header blocks are skipped as if their information is already in dictionary cache. Pagetable header block needs to be read separately and is only 1 block, so it's always db file sequential read. The rest are data blocks. So when the number of data blocks = 1, it obviously needs a db file sequential read. When it's more than 1, it's scattered read. End of test ____________ Unrelated. It seems that in 9i, the real data is not in the first block right after pagetable segment header block. Instead, it's around the middle of the second half of the extent. E.g. if dba_extents says block_id is 1234, blocks is 16, then the data (suppose the table has only one short row) may be in block 1241 or 1242, not 1237. In 10g, if I remember right, it's in the block right after pagetable segment header block. All this is checked by dumping data blocks.