Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Segment header

rcc50886Aug 4 2012 — edited Aug 5 2012
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)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 2 2012
Added on Aug 4 2012
8 comments
961 views