This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 10, 2010 1:02 PM by Jonathan Lewis RSS

Auto Vs Manaul segment space management

user12050217 Explorer
Currently Being Moderated
Hi,

I am reading below link :
http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/tspaces002.htm#ADMIN10065

And it has one sentence "Automatic segment space management delivers better space utilization than manual segment space management."

My question is : Then in which cases Manual segment space management is good choice in compare to auto. If Automatic segment space management delivers better space utilization, then why oracle has also introduced Manual segment space management? I mean, what special advantages can be obtained with only manual segment space management, which are not available in automatic segment space management.

Any example will also help me to understand it please.

Thanks,
  • 1. Re: Auto Vs Manaul segment space management
    Aman.... Oracle ACE
    Currently Being Moderated
    MSSM is the old behavior using the Free Lists. Its not suggested anymore with the introduction of the ASSM. Using Free Lists, there can be severe issues in a highly concurrent environment related to buffers being busy and thus causing waits. ASSM uses Bitmap Blocks to manage the space utilization tracking of the blocks and doesn't offer waits like Free List does. So that's why its suggested.

    HTH
    Aman....
  • 2. Re: Auto Vs Manaul segment space management
    user12050217 Explorer
    Currently Being Moderated
    Thanks for your reply. Your reply says that ASSM is the best in compare to MSSM, i am 100% agree and it is suggested in high production environment. But still my question is, if ASSM is the best, then why there is possibility to use MSSM too; something like; "Eat an apple (ASSM) everyday keep doctor away, then what is uses of other fruits (MSSM) too". Why oracle is not brining back MSSM in its new versions, if ASSM has been proved the best.

    Thanks,
  • 3. Re: Auto Vs Manaul segment space management
    AliD Expert
    Currently Being Moderated
    In a data warehouse using MSSM may make sense. There's minimum update in DW and locking is not an issue.
  • 4. Re: Auto Vs Manaul segment space management
    user12050217 Explorer
    Currently Being Moderated
    AliD wrote:
    In a data warehouse using MSSM may make sense. There's minimum update in DW and locking is not an issue.
    May make sense, means you are not sure? If sure, then any example or any doc link which supports your saying. I just want to see in docs that mention the uses of MSSM in compare to ASSM please.

    Thanks,
  • 5. Re: Auto Vs Manaul segment space management
    Aman.... Oracle ACE
    Currently Being Moderated
    Why oracle is not brining back MSSM in its new versions, if ASSM has been proved the best.
    I am not sure I am getting where you are coming from. MSSM was never ruled out. Its usage was not suggested by Oracle with the introduction of ASSM which is obvious because ASSM is a better option than MSSM.

    That said, if you mean to ask why people still use MSSM even when ASSM is around, that's a more relevant question IMO. And that's actually true since there are couple of issues you may run into when you use ASSM without proper care , for example, using a large blocksize with ASSM can cause issues. So at times, still shops use MSSM but in general , ASSM is the way to go. Also some features are limited only to ASSM like Bigfile tablespace etc. So you have to use it anyways if you want to enjoy such features.

    HTH
    Aman....
  • 6. Re: Auto Vs Manaul segment space management
    AliD Expert
    Currently Being Moderated
    May make sense, means you are not sure?
    May means you have to try it in your own environment as there is no "rule of thumb" that applies every where.

    In our environment, for example, the data warehouse is using MSSM. Also we found some buggy index behavior when the index resides on ASSM in one of our databases.

    Having said that, unless you run benchmark within your application and the results favor MSSM, go for ASSM.
  • 7. Re: Auto Vs Manaul segment space management
    user12050217 Explorer
    Currently Being Moderated
    I am again thankful to you for your reply.
    Its usage was not suggested by Oracle
    Yes, and this makes me in a fix; that if it is not recommended by oracle, experts; then why it is still there? Can i say it as an "Not suggested but existing feature", something like "no use"?

    I never met a person, who can satisfy me that i am using MSSM just because of this and that, and this is the main question here.
    there are couple of issues you may run into when you use ASSM
    I shall be thankful to you, if you please mention more those issues like you mentioned with issue of large blocksize.

    Probably, i am missing some really good advantages over ASSM; otherwise oracle could have withdrawal this feature.

    Thanks and Regards,
  • 8. Re: Auto Vs Manaul segment space management
    Aman.... Oracle ACE
    Currently Being Moderated
    Yes, and this makes me in a fix; that if it is not recommended by oracle, experts; then why it is still there? C
    I am not sure that why you are struck on this that its there? Its a feature, its usage is based on your choice. It was the default way to manage the segments till 10g if I remember correctly even when the ASSM was around since 9i. Only in 11g(if I am correctly recalling) , the management behavior now is using ASSM by default.
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28282/configbp002.htm#sthref275

    So its not correct to argue that why MSSM is there. Its there just like the way the option to create Dictionary Managed Tablespace is still shown as 2nd radio button option in Enterprise Manager even when Oracle is not suggesting to use it at all and is recommending to use Locally Managed tablespace only. Its the same like the issues with the latches , for which Mutex concept was introduced in 10g but still latches are not thrown out. So its on oracle's will that when they would complete remove this feature.
    Can i say it as an "Not suggested but existing feature", something like "no use"?
    Yes, that's what I tried to explain with the above example of DMT and Latches.
    Probably, i am missing some really good advantages over ASSM; otherwise oracle could have withdrawal this feature.
    I suggest not to bother that when it would be withdrawn. Just remember that its better to use ASSM and this is the default too if you won't play with the tablespace level attributes and let them be default.

    HTH
    Aman....
  • 9. Re: Auto Vs Manaul segment space management
    user12050217 Explorer
    Currently Being Moderated
    For more answers, i am searching the forum and found below link :
    Advice on Automatic vs Manual segment-space management

    Which is almost similar to my question, but in that thread the link provided by John Bolton is not opening at my end, can you/anyone please confirm me, are it is opening at yours end ?

    And again when i check in my database, i found that SYSTEM, UNDO and TEMP tablespace are having MSSM, and this is my test db which i created by DBCA, it means there is something really special in MSSM; otherwise it could be ASSM.... and i found below text too from below link :

    ------------------
    If I had to make a guess, I would suggest that since the system tablespace contains rollback (undo) segments, ASSM cannot be used. With ASSM, the extent size starts small (64KB) and grows to 1MB, 8MB, etc. as additional extents are added to segments.

    Since regular users should not be using the SYSTEM tablespace for inserts/updates/undo, there should be minimal burden for the DBA in managing that tablespace.

    Charles Hooper (segment space management
    ------------------
  • 10. Re: Auto Vs Manaul segment space management
    AliD Expert
    Currently Being Moderated
    I assume this is new address:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2929412562998

    The way system objects work should not be a guideline for users. For instance, most queries use Rule-Based Optimization.
  • 11. Re: Auto Vs Manaul segment space management
    729036 Explorer
    Currently Being Moderated
    have a look on this
    http://hemantoracledba.blogspot.com/2008/08/assm-or-mssm-impact-on-inserts.html
  • 12. Re: Auto Vs Manaul segment space management
    EdStevens Guru
    Currently Being Moderated
    user12050217 wrote:
    <snip>
    Probably, i am missing some really good advantages over ASSM; otherwise oracle could have withdrawal this feature.
    And they probably will at some point in the future. Most software vendors take several major releases to phase out old features in favor of new, and during the transition, both are documented and supported. This allows customers an easier upgrade path. Can you imagine what it would be like to upgrade from 9 to 10 if every "replaced" feature were totally eliminated at once? You're spinning your wheels looking for some deep technical justification when the real answer is probably far simpler and more mundane.
    Thanks and Regards,
  • 13. Re: Auto Vs Manaul segment space management
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    With ASSM, the extent size starts small (64KB) and grows to 1MB, 8MB, etc. as additional extents are added to segments
    Wrong. Extent Sizes are defined by the Allocation Policy which you would specify with EXTENT MANAGEMENT LOCAL AUTOALLOCATE.
    ASSM doesn't relate to Extents but to Blocks.

    Thus, you can have EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL (which, by the way, I do use in some environments, because it still makes eminent sense given the circumstances).

    BTW, I have updated that other thread and responded to Charles Hooper likewise.

    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 14. Re: Auto Vs Manaul segment space management
    UweHesse Expert
    Currently Being Moderated
    I would like to add another reason why Manual Segment Space Management (MSSM) is still possible: Think about Database Upgrades. Some sites may have upgraded all the way from version 8 (only MSSM available there) to 11g without ever really recreating the database. They still need that option. Same is true for Dictionary Managed Tablespaces, although we invented a much better solution in 8i already.

    In general, you should go with ASSM unless you have a very good reason not to do so :-)

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
1 2 Previous Next

Legend

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