1 2 Previous Next 21 Replies Latest reply: Nov 15, 2012 2:04 PM by user12229699 RSS

    Locally Managed Tablespaces vrs ASSM

    Jimbo
      Anyone know the distinction / difference between Locally Managed Tablespaces and Automatic Segment Space Management ( ASSM ) ?

      Jim
        • 1. Re: Locally Managed Tablespaces vrs ASSM
          John Stegeman
          Google is great for finding information

          http://www.orafaq.com/wiki/ASSM
          http://www.orafaq.com/wiki/Locally_managed_tablespaces
          • 2. Re: Locally Managed Tablespaces vrs ASSM
            Osama_Mustafa
            LMT : one of the key features in Oracle database. These have been made available since Oracle 8i.

            Benefits :
            Dictionary contention is reduced.
            Space wastage removed.
            No Rollback generated.

            and other benefits you can Read this
            http://docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm

            ASSM :
            only available in locally managed tablespaces. It removes the need for managing freelists and freelist groups by using bitmaps to describe the space usage of each block is within a segment. The bitmap is stored in separate blocks known as bitmapped blocks (BMBS). This relieves the contention on the segment header that occurs with freelists.

            you can read this
            http://www.orafaq.com/wiki/ASSM
            • 3. Re: Locally Managed Tablespaces vrs ASSM
              John Stegeman
              No Rollback generated.
              WHAAAAAAT?

              sounds like you have read something out of context
              • 4. Re: Locally Managed Tablespaces vrs ASSM
                Osama_Mustafa
                Check this article John
                http://www.databasejournal.com/features/oracle/article.php/2223631/Understanding-Oracles-Locally-Managed-Tablespaces.htm

                I red this Once so i Bookmark it :)
                • 5. Re: Locally Managed Tablespaces vrs ASSM
                  John Stegeman
                  The author should not have made "No Rollback Generated" the title for the section - it is misleading and wrong.

                  The statement that is further down the page:
                  In LMTs, no rollback is generated for space allocation and deallocation activities.
                  Is true

                  You should have just pasted the link so people can read in context.
                  • 6. Re: Locally Managed Tablespaces vrs ASSM
                    Osama_Mustafa
                    also this will work :)
                    • 7. Re: Locally Managed Tablespaces vrs ASSM
                      Jimbo
                      So when I am specifying Extent Management as Uniform or Auto Allocate - this is local tablespace management. The management of the extents of that tablespace is from within the header of the data file itself rather than from the dictionary ?

                      However ASSM is concerned with space management within a data block ? Do we say the datablock of an extent or do we generally just say the data block of a segment ?
                      • 8. Re: Locally Managed Tablespaces vrs ASSM
                        Girish Sharma
                        So when I am specifying Extent Management as Uniform or Auto Allocate - this is local tablespace management.
                        No, AUTOALLOCATE and UNIFORM clause are for size of extents only. Locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement.
                        The management of the extents of that tablespace is from within the header of the data file itself rather than from the dictionary ?
                        Correct.
                        However ASSM is concerned with space management within a data block ?
                        No, it specify how free and used space within a segment not data block, because there is hierarchy i.e. :

                        data block
                        extents
                        segments
                        Do we say the datablock of an extent or do we generally just say the data block of a segment ?
                        data block of extents. Extents are group/collection of data blocks, segment is the group/collection of extents.

                        http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#i1013496

                        Regards
                        Girish Sharma
                        • 9. Re: Locally Managed Tablespaces vrs ASSM
                          Jonathan Lewis
                          John Stegeman wrote:
                          The author should not have made "No Rollback Generated" the title for the section - it is misleading and wrong.

                          The statement that is further down the page:
                          In LMTs, no rollback is generated for space allocation and deallocation activities.
                          Is true
                          Actually, it's wrong.
                          The session still has to update seg$ (segment definition) and tsq$ (tablespace quota for user), but there is a reduction in undo because there is no change to make to uet$ (used extents) and fet$ (free extents). Of course, there's still various bits of redo to consider - including the (new) update to the space bitmap in file header, and the upate to the segment header block.

                          Regards
                          Jonathan Lewis
                          • 10. Re: Locally Managed Tablespaces vrs ASSM
                            Mark D Powell
                            I disagree with Girish's comment that ASSM does not control how space is used within a block because ASSM which is only available with a locally managed tablespace does control when a block is reused. The table pctused parameter no longer applies when ASSM is used as the ASSM logic determines when blocks are available to hold an insert. So ASSM does indirectly at least control block space usage.

                            IMHO -- Mark D Powell --
                            • 11. Re: Locally Managed Tablespaces vrs ASSM
                              Osama_Mustafa
                              But there's always Performance issue with ASSM .

                              I red article talking this issue could be because Slow for full-table scans , Also its slower for high-volume concurrent insertion . right
                              • 12. Re: Locally Managed Tablespaces vrs ASSM
                                Mark D Powell
                                Yes, it would appear ASSM may have some performance related issues. I say "may" have because I have not read too many articles on the issue plus most of the articles I found are older which could mean the results may vary on a newer version. We do not generally use ASSM with our locally managed tablespaces since at the time it was introduced we did not trust the feature. Then because you could only turn on ASSM at tablespace creation which would be a lot of work and take significant windows we have not converted. My only experience with using the feature in on low to moderate activity databases so I have not seem any real problems that would cause me to look at ASSM as a problem. Now I will have to add researching this to our list of things to do before the next platform migration.


                                http://hemantoracledba.blogspot.com/2008/08/assm-or-mssm-impact-on-inserts.html

                                http://www.akadia.com/services/ora_freelists.html

                                http://hoopercharles.wordpress.com/2010/01/28/database-using-assm-tablespace-exhibits-slow-insert-performance-after-an-uncommitted-delete/


                                HTH -- Mark D Powell --
                                • 13. Re: Locally Managed Tablespaces vrs ASSM
                                  Mark D Powell
                                  Yes, it would appear ASSM may have some performance related issues. I say "may" have because I have not read too many articles on the issue plus most of the articles I found are older which could mean the results may vary on a newer version. We do not generally use ASSM with our locally managed tablespaces since at the time it was introduced we did not trust the feature. Then because you could only turn on ASSM at tablespace creation which would be a lot of work and take significant windows we have not converted. My only experience with using the feature in on low to moderate activity databases so I have not seem any real problems that would cause me to look at ASSM as a problem. Now I will have to add researching this to our list of things to do before the next platform migration.


                                  http://hemantoracledba.blogspot.com/2008/08/assm-or-mssm-impact-on-inserts.html

                                  http://www.akadia.com/services/ora_freelists.html

                                  http://hoopercharles.wordpress.com/2010/01/28/database-using-assm-tablespace-exhibits-slow-insert-performance-after-an-uncommitted-delete/


                                  HTH -- Mark D Powell --
                                  • 14. Re: Locally Managed Tablespaces vrs ASSM
                                    Mark D Powell
                                    Remove duplicated post.

                                    HTH -- Mark D Powell --

                                    Edited by: Mark D Powell on Oct 5, 2012 1:26 PM
                                    1 2 Previous Next