This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Nov 15, 2012 12:04 PM by user12229699 RSS

Locally Managed Tablespaces vrs ASSM

Jimbo Explorer
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    No Rollback generated.
    WHAAAAAAT?

    sounds like you have read something out of context
  • 4. Re: Locally Managed Tablespaces vrs ASSM
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    also this will work :)
  • 7. Re: Locally Managed Tablespaces vrs ASSM
    Jimbo Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Remove duplicated post.

    HTH -- Mark D Powell --

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points