1 2 Previous Next 15 Replies Latest reply: Jul 17, 2010 5:29 AM by Aman.... RSS

    Segment space management to auto

    CKPT
      Hi,
      Db version: oracle enterprise edition 10.2.4.0 64 bit
      Os version: windows 2003

      The tablespaces of my database is in segement space management "manual" . so if i want to conver the segment space management to "auto".

      Automatic :- Objects in the tablespace automatically manage their free space. It offers high performance for free space management.
      Manual :- Objects in the tablespace will manage their free space using free lists. It is provided for backward compatibility.

      1) so how can i find out whether database is in backward compatibilty?
      2) is there any issues with performance after converting
      3) or is there any other severe issues other than performance.
      4) how can we identifiy the difference interms of performance after migrating from "manual" to "auto".

      Thanks.
        • 1. Re: Segment space management to auto
          sb92075
          Handle:      CKPT
          Status Level:      Newbie (90)
          Registered:      Aug 13, 2008
          Total Posts:      357
          Total Questions:      71 (50 unresolved)
          Name      user5420

          So many questions & so few answers
          :-(
          1) so how can i find out whether database is in backward compatibilty?
          Backwards compatible between what & what?
          2) is there any issues with performance after converting
          What do your regression & benchmark test show
          3) or is there any other severe issues other than performance.
          What do your regression & benchmark test show
          4) how can we identifiy the difference interms of performance after migrating from "manual" to "auto".
          What do your regression & benchmark test show

          If we tell you no problem & then your DB stops performing, what will you tell your boss?
          • 2. Re: Segment space management to auto
            CKPT
            Hi,

            I have not expected like these answers from like you guru's.
            What do your regression & benchmark test show
            I have no idea how to conduct these test. give me some idea on this
            If we tell you no problem & then your DB stops performing, what will you tell your boss?
            then no use to post in forum, is it? :) expecting good and better suggetion from experts/gurus.

            Thanks
            • 3. Re: Segment space management to auto
              sb92075
              I have no idea how to conduct these test. give me some idea on this
              Run example SQL against DB & measure performance & response time.

              Make a change; say change TS to AUTO

              Run same example SQL against DB & measure performance & response time.

              Did DB perform same, better or worse?
              Decide if change did nothing, helped or hurt.
              Decide to keep change or return DB to original configuration
              • 4. Re: Segment space management to auto
                CKPT
                Hi,
                That sounds Good,
                Its a production database, if i did changes i have to convert both data tablespace and index tablespace.
                So for that i need maintanance window, this database size is around 80GB so what is your view did this coversion make sense?
                Thanks
                • 5. Re: Segment space management to auto
                  sb92075
                  so what is your view did this coversion make sense?
                  This is why folks have & use TEST DB
                  Changes are tested before released to Production.

                  Rhetorical question - if AUTO always did better than Manual, why is MANUAL now in your DB?
                  • 6. Re: Segment space management to auto
                    Aman....
                    1) Its not your db which is in backward time. You are using an old feature, thats what the term means.
                    2) The assm is actually more problematic than free lists when you change your block size. I recall Jonathan mentioned in some thre so you need to be careful when planning to switch to it. Howard Rodgers also wrote a post some time back with title, assm is evil. So a careful thought process is required before the conversion.
                    3)I dont know why Sb told you to check sql response time because that has nothing to do with free lists or assm. Its an advantage for concurrently running dmls. So if you want to test, you need to test it with multiple running dmls , first with free lists and then with assm.

                    HTH
                    Aman....ad, its actually same as like using 16 free lists.
                    • 7. Re: Segment space management to auto
                      Aman....
                      My last reply's one line got messed up because am using my phone for the reply. Sorry for it. What that line meant was that assm is equivalent to use a group of 16 free lists. I shall see if I can get to a proper box and can share the thread where Jonathan mentioned it.

                      Aman....
                      • 8. Re: Segment space management to auto
                        user13364377
                        Hi CKPT,

                        Edited by: user13364377 on 16-Jul-2010 03:40
                        • 9. Re: Segment space management to auto
                          Aman....
                          Are you sure that what you have mentioned is indeed correct because you mentioned is full of wrong information IMO?
                          If a tablespace is in manual segment space management mode, it will mark only some blocks as header for that tablespace. so header contention will occur first-of-all.
                          Do you mean to say that in the case of the MSSM, its the tablespace header(what's that) would be used ? Its a segment level feature so the contention would be on the segment header as it would be pinned always to start teh search.
                          While datadictionary managed tablespace and locally managed tablespace acts at tablespace level, freelists acts at block level. So free list is doing it at a fine grained level.
                          What does this statement means? The difference if DMT and LMT lies in this that DMT uses FET$ and UET$ to maintain the information about the "extents" and LMT uses a bit structure to maintain it. The free list, as I mentioned before is a segment level attribute.
                          The advantage of manual segment space management over ASSM is data will be tightly packed in the datablocks, coz data will get inserted till the pct_free value setting. ie if pct_free=10 , then data will get inserted till 90% of the datablock. only when a block filled upto 90%, then the next datablock will be used.
                          And where did you read this? Post a link here which does say this?

                          I shall suggest that you take some time and re-read your reply. It has got couple of mistaken statments.

                          Aman....
                          • 10. Re: Segment space management to auto
                            user13364377
                            If a tablespace is in manual segment space management mode, it will mark only some blocks as header for that tablespace. so header contention will occur first-of-all.

                            I was talking about the segment headers...

                            While datadictionary managed tablespace and locally managed tablespace acts at tablespace level, freelists acts at block level. So free list is doing it at a fine grained level.

                            I mean to say that dictionary managed tablespace and locally managed tablespace are tablespace level entities.

                            and freelist is something which acts on datablocks.

                            Edited by: user13364377 on 16-Jul-2010 04:25
                            • 11. Re: Segment space management to auto
                              Aman....
                              user13364377 wrote:
                              If a tablespace is in manual segment space management mode, it will mark only some blocks as header for that tablespace. so header contention will occur first-of-all.

                              I was talking about the segment headers...
                              I am not sure that I can agree to your statement. In the formation of a new table, there are three blocks which are formatted in the first extent of the segment. One, the segment header and two blocks for the level 1 and 2 bitmaps. I am not able to understand your "segment headers" concept at all. There is one segment header block AFAIK.
                              While datadictionary managed tablespace and locally managed tablespace acts at tablespace level, freelists acts at block level. So free list is doing it at a fine grained level.

                              I mean to say that dictionary managed tablespace and locally managed tablespace are tablespace level entities.

                              and freelist is something which acts on datablocks.
                              And this has nothing to do with the OP's question I believe since he is interested to know whether using ASSM would be better or not.

                              Aman....
                              • 12. Re: Segment space management to auto
                                CKPT
                                Hi aman,

                                its fine to hear from your end, you have any indepth information of weblinks on ASSM? so that i can check those once.

                                Thanks
                                • 13. Re: Segment space management to auto
                                  Aman....
                                  Hi Ckpt,

                                  Please check this link which goes into quite deep about ASSM .

                                  http://www.tanelpoder.com/files/Poder_Freelists_vs_ASSM.ppt

                                  HTH
                                  Aman....
                                  • 14. Re: Segment space management to auto
                                    CKPT
                                    Hi Aman,

                                    Thanks a lot, i will go through with this document, if need i will reply back to this thread in case of implementing ASSM,

                                    at last i need one suggetion from you i.e my database size is 80gb. so table data is in one tablespace and indexes are in another tablespaces, so with this ASSM auto, any dependent objects will become invalid?
                                    'Thanks
                                    1 2 Previous Next