until now i was under impression that segment header is the first block of the first extent of a segment. I am doing some tests to under stand the oracle, interestingly
first block of the first extent is not the segment header, please see following:
SQL> select header_file, header_block, extents, blocks from dba_segments where
segment_name='EMP_TABLE' and owner='ROC';
HEADER_FILE HEADER_BLOCK EXTENTS BLOCKS
----------- ------------ ---------- ----------
4 298 4 32
SQL> select extent_id, block_id, blocks from dba_extents where segment_name='EMP_TABLE' and owner='ROC';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 296 8
1 304 8
2 312 8
3 320 8
dba_segments showing that segment header is the blkk # 298 but first extent starts with blk# 296
Can some one explain me ??
I also traced the full table scan on that table and found the following wait evets associated with the full table scan
WAIT #6: nam='db file sequential read' ela= 723 file#=4 block#=298 blocks=1 obj#=73421 tim=1344118323910648
WAIT #6: nam='db file scattered read' ela= 2031 file#=4 block#=299 blocks=5 obj#=73421 tim=1344118323919077
WAIT #6: nam='db file scattered read' ela= 384 file#=4 block#=304 blocks=8 obj#=73421 tim=1344118323928633
WAIT #6: nam='db file scattered read' ela= 71 file#=4 block#=313 blocks=7 obj#=73421 tim=1344118323947156
WAIT #6: nam='db file scattered read' ela= 1942 file#=4 block#=320 blocks=8 obj#=73421 tim=1344118323965979
1. segment header will be read using sequential read, so we are seeing following
WAIT #6: nam='db file sequential read' ela= 723 file#=4 block#=298 blocks=1 obj#=73421 tim=1344118323910648
2.segment consists of four extents and each extent has 8 blocks (and my db_file_multiblock_read_count =16 )
so oracle reads the each extent at a time using multiblock read i/o i.e db file scattered read
Is oracle can't read two extents at a time ?? (each extent has 8 blks and db_file_multiblock_read_count =16 )
So my question was what are the first two blocks (blk 296, 297) of the first extent ??
How come oracle knows the blk# 298 is the segment header ?? (is it supposed to be first block of the first extent)