8 Replies Latest reply: Aug 5, 2012 11:20 AM by rcc50886 RSS

    Segment header

    rcc50886
      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)
        • 1. Re: Segment header
          Seberg
          The dbms_space package might provide some information :

          http://psoug.org/reference/dbms_space.html


          What are you going to this information once you have it?
          • 2. Re: Segment header
            mbobak
            I could speculate, but, in honor of "Battle Against Any Guess", why should we speculate, when we can know?

            Try dumping the blocks, to be sure what they are:
            alter system dump datafile 4 block min 296 block max 297;
            Hope that helps,

            -Mark
            • 3. Re: Segment header
              rp0428
              >
              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:
              . . .
              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)
              >
              Oracle ACE and noted author Jonathan Lewis suggests the answer you seek in his book Oracle Core Essential Internals for DBAs and Developers.

              If you check the Glossary on p.251 for 'segment header' you will find this:
              >
              segment header: A special block (once guaranteed to be the first block in a segment) holding metadata about the segment. In an ASSM tablespace there will be a few segment space management blocks before the segment header block.
              >
              Are you using ASSM? You didnt' provide your 4 digit Oracle version.

              Page 27 also has a diagram (Figure 3-1) titled 'Schematic comparing key content of different types of setment headers.
              • 4. Re: Segment header
                Aman....
                rcc50886 wrote:
                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)
                What's the db version? Can you do something similar like I have done and show us,
                SQL>
                
                SQL> create table newt (a number);
                
                Table created.
                
                SQL> select object_id ,object_name from user_objects where object_name='NEWT';
                
                 OBJECT_ID
                ----------
                OBJECT_NAME
                --------------------------------------------------------------------------------
                     77545
                NEWT
                
                
                SQL> SELECT b.block#,b.class#,b.status, object_name, object_type FROM v$bh b,
                  2  dba_objects o WHERE b.objd = o.data_object_id AND o.owner ='SYS' and object_name='NEWT';
                
                    BLOCK#     CLASS# STATUS
                ---------- ---------- ----------
                OBJECT_NAME
                --------------------------------------------------------------------------------
                OBJECT_TYPE
                -------------------
                     93248          4 xcur
                NEWT
                TABLE
                
                
                SQL> insert into newt values(1);
                
                1 row created.
                
                SQL> commit;
                
                Commit complete.
                
                SQL> SELECT b.block#,b.class#,b.status, object_name, object_type FROM v$bh b,
                  2  dba_objects o WHERE b.objd = o.data_object_id AND o.owner ='SYS' and object_name='NEWT';
                
                    BLOCK#     CLASS# STATUS
                ---------- ---------- ----------
                OBJECT_NAME
                --------------------------------------------------------------------------------
                OBJECT_TYPE
                -------------------
                     93249          1 xcur
                NEWT
                TABLE
                
                     93248          4 xcur
                NEWT
                TABLE
                
                    BLOCK#     CLASS# STATUS
                ---------- ---------- ----------
                OBJECT_NAME
                --------------------------------------------------------------------------------
                OBJECT_TYPE
                -------------------
                
                SQL> select * from V$version;
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE    11.2.0.1.0      Production
                TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production
                From the Julian Dyke's site, http://www.juliandyke.com/Internals/BlockClasses.html

                The class 4 is Segment Header and Class 1 is a Data Block. But if you are seeing two blocks above the segment header, they should be L1 and L2 blocks used as BMB for ASSM, as Rp has mentioned.

                HTH
                Aman....
                • 5. Re: Segment header
                  rcc50886
                  I could speculate, but, in honor of "Battle Against Any Guess", why should we speculate, when we can *know*?
                  
                  Try dumping the blocks, to be sure what they are:
                  alter system dump datafile 4 block min 296 block max 297;
                  
                  
                  Hope that helps,
                  Mark,

                  I will upload the dump contenet soon....Thanks.
                  • 6. Re: Segment header
                    rcc50886
                    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:
                    . . .
                    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) 
                    Oracle ACE and noted author Jonathan Lewis suggests the answer you seek in his book Oracle Core Essential Internals for DBAs and Developers.
                    
                    If you check the Glossary on p.251 for 'segment header' you will find this:
                    segment header: A special block (once guaranteed to be the first block in a segment) holding metadata about the segment. In an ASSM tablespace there will be a few segment space management blocks before the segment header block.
                    Are you using ASSM? You didnt' provide your 4 digit Oracle version.
                    
                    Page 27 also has a diagram (Figure 3-1) titled 'Schematic comparing key content of different types of setment headers.
                    I didn't have the book with me...but will get one soon.

                    I am using ASSM and iits 11.2.0.1 vesrion, So blocks before segment header will be the ASSM realted blocks ??

                    What is the different between segment header block and ASSM maintaince blocks ? did they both have same information??
                    • 7. Re: Segment header
                      Aman....
                      This should help in getting the concept.
                      www.perfvision.com/docs/Poder_Freelists_vs_ASSM.ppt

                      Aman....
                      • 8. Re: Segment header
                        rcc50886
                        This should help in getting the concept.
                        www.perfvision.com/docs/Poder_Freelists_vs_ASSM.ppt
                        
                        Aman....
                        Thanks Aman !! It helps me a lot .