8 Replies Latest reply: Dec 2, 2008 8:42 AM by OracleGuy777 RSS

    ASSM data structure question

    OracleGuy777
      hi guys,

      Can someone please clarify this for me? I am looking at the presentation "Freelists vs ASSM in Oracle9i" by Tanel Poder, and on slides 19 he talks about the ASSM segment and ASSM segment header. I am just a little confused. if we have ASSM, and we create a table, then does that mean that an ASSM segment is created for that table too? i.e. we'll have two segments, one for the table, and an ASSM segment for it?

      Or, is only one segment created (for the table), and the L1 BMB reside in segment header of that table, and the L3 BMB reside in the first data blocks of each extent of that segment? is this correct? if so, where do the L2 BMBs reside?

      thanks
        • 1. Re: ASSM data structure question
          181444
          There is only one segment for the table.

          When you create a table in a non-ASSM tablespace the first block of the segment is the table header (segment header) but in an ASSM tablespace the table header block is the third block in the segment as ASSM takes the first two blocks. You lose 3 blocks out of you initial extent that cannot hold data instead of the normal one block.

          HTH -- Mark D Powell --
          • 2. Re: ASSM data structure question
            Aman....
            Oracleguy,
            No there is only segment, table segment that is created.There are 2 initial bitmap blocks that are created for storing the info of the Bitmaps which would be used by Oracle to store the info about the blocks.These blocks are also a part of the initial extent itself.The first 2 blocks will be L1,L2 BMB blocks.
            SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 22 00:24:26 2008  
              
            Copyright (c) 1982, 2007, Oracle.  All rights reserved.  
              
            Enter password:  
              
            Connected to:  
            Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production  
            With the Partitioning, Oracle Label Security, OLAP, Data Mining,  
            Oracle Database Vault and Real Application Testing options  
              
            SQL> create table t (a number);  
              
            Table created.  
              
            SQL> select object_id ,object_name from user_objects where object_name='T';  
              
             OBJECT_ID  OBJECT_NAME  
            ----------  ----------  
              
                 72217  T  
            SQL> SELECT b.block#,b.class#,b.status, object_name, object_type, dirty "Dirty" FROM v$bh b,  
              2   dba_objects o WHERE b.objd = o.data_object_id AND o.owner = 'AMAN' and object_name='T';  
              
                BLOCK#     CLASS# STATUS     OBJECT_NAME          OBJECT_TYPE         D  
            ---------- ---------- ---------- -------------------- ------------------- -  
                  3746          9 xcur       T                    TABLE               Y  
                  3745          8 xcur       T                    TABLE               Y  
                  3747          4 xcur       T                    TABLE               Y  
              
            SQL>  
            The class of 8,9 bitmap blocks.4 represents segment header block.
            The bitmap blocks are also arranged in the tree structure. The L3 will be in the extent of the table which is used to find the info about the block's status.
            HTH
            Aman....
            • 3. Re: ASSM data structure question
              247514
              OracleGuy777 wrote:
              hi guys,

              Can someone please clarify this for me? I am looking at the presentation "Freelists vs ASSM in Oracle9i" by Tanel Poder, and on slides 19 he talks about the ASSM segment and ASSM segment header. I am just a little confused. if we have ASSM, and we create a table, then does that mean that an ASSM segment is created for that table too? i.e. we'll have two segments, one for the table, and an ASSM segment for it?

              Or, is only one segment created (for the table), and the L1 BMB reside in segment header of that table, and the L3 BMB reside in the first data blocks of each extent of that segment? is this correct? if so, where do the L2 BMBs reside?
              I think you are confused by the term 'ASSM Segment' used in the presentation. What it means is segment using ASSM vs segment using freelist. It's not a seperate Segment.
              No, L3 BMB is not in first data blocks of each extent. These Bitmap blocks are segment specific, per segment basis not extent. L3 BMB is in segment header.
              • 4. Re: ASSM data structure question
                Aman....
                Ying,
                A small correction,L3 also will be in the extent only. Segment header will havee a pointer to it.Segment header will not contain it.
                Regards
                Aman......
                • 5. Re: ASSM data structure question
                  OracleGuy777
                  No there is only segment, table segment that is created.There are 2 initial bitmap blocks that are created for storing the info of the Bitmaps which would be used by Oracle to store the info about the blocks.These blocks are also a part of the initial extent itself.The first 2 blocks will be L1,L2 BMB blocks.
                  I am still slightly confused. What I think I have understood is the following:

                  1) L1 will be in the first data block of the segment
                  2) L2 will be in the second data block of the segment
                  3) You say L3 will have a pointer to it in the segment header. But where is L3 stored? Plus, Poder's slides says that it can be stored in the segment header. So I am assuming that ying IS right, but you are also right that it does not have to be stored in the segment header, because Poder wrote can. Theerfore, if it is not in the segment header, then where is it stored?

                  thanks
                  • 6. Re: ASSM data structure question
                    OracleGuy777
                    One more point: In the thread does an extent have a header? Mark says:

                    in an ASSM managed tablespace the first block of every extent is an ASSM management block.

                    Can someone please explain this? is this to do with L1 BMBs?
                    • 7. Re: ASSM data structure question
                      Jonathan Lewis
                      OracleGuy777 wrote:
                      No there is only segment, table segment that is created.There are 2 initial bitmap blocks that are created for storing the info of the Bitmaps which would be used by Oracle to store the info about the blocks.These blocks are also a part of the initial extent itself.The first 2 blocks will be L1,L2 BMB blocks.
                      I am still slightly confused. What I think I have understood is the following:

                      1) L1 will be in the first data block of the segment
                      2) L2 will be in the second data block of the segment
                      3) You say L3 will have a pointer to it in the segment header. But where is L3 stored? Plus, Poder's slides says that it can be stored in the segment header. So I am assuming that ying IS right, but you are also right that it does not have to be stored in the segment header, because Poder wrote can. Theerfore, if it is not in the segment header, then where is it stored?
                      Apart from a special case involving small extents, every extent in a tablespace using ASSM will start with at least one L1 bitmap block. Large extents may have multiple consecutive L1 bitmap blocks. When Oracle adds a new extent and discovers it also needs a new L2 bitmap block - which means there are now too many L1 blocks to list in the existing L2 blocks, it will put that L2 block after the L1 blocks - if there are now too many L2 blocks to list in the existing L3 blocks, it will put a new L3 block after the L2 block.

                      The segment header block is not an L3 block, but part of the segment header block is used to play the part of an L3 block - and you would have to have a pretty big data segment (lots of blocks) before you needed a "genuine" L3 block. If you have a segment so large that it has multiple L3 blocks, then the segment header block holds a pointer to the first L3 block, and each L3 block (I assume) holds a pointer to the next L3 block).

                      (Note, this 'extra' use of the segment header block is similar to the way that the segment header block is not a 'freelist group' block when using traditional freelist management, but the (spare space in the) segment header block plays the role of a freelist group block when you haven't created multiple freelist groups.

                      The special case of an extent without an L1 block appears because Oracle is able to use a single L1 block to cover the data blocks of small adjacent extents - for exampl, when you are also using system allocated extent sizes the first 16 extents are usually 64KB, which usually means 8 blocks of 8KB each - in this case you can find that Oracle uses the L1 block of the first extent to map the first and second extents, the L1 block of the third extent to map the data blocks of the third and fourth extents, and so on.

                      Mark is using ASSM management block to be synonymous with any of L1 bitmap block, L2 bitmap block, L3 bitmap block.


                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk

                      "The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge."
                      Stephen Hawking
                      • 8. Re: ASSM data structure question
                        OracleGuy777
                        many thanks!