6 Replies Latest reply: Jan 6, 2013 11:17 PM by rp0428 RSS

    PCT increase

    $phinx19
      Hi All,

      I have read this explanation about PCTINCREASE storage clause in doc.oracle site as:

      If you want to keep all extents the same size, you can prevent the SMON background process from coalescing extents by setting the value of PCTINCREASE to 0. In general, Oracle recommends a setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing.

      But as we all know that SMON is the background process which is used to coalesce adjacent free space and thereby reducing fragmentation.
      So how come by setting pctincrease=0,disables the smon and also minimizes the fragmenation?

      BR
      Sphinx
        • 1. Re: PCT increase
          JohnWatson
          This is history. Locally managed tablespaces with uniform extent sizes mean that PCTINCREASE is ignored.
          • 2. Re: PCT increase
            $phinx19
            Hi John,

            Thanks.

            I know this is history and what are uniform extents but is the explanation correct.
            I just wana know the same.

            BR
            Sphinx
            • 3. Re: PCT increase
              EdStevens
              $phinx19 wrote:
              Hi John,

              Thanks.

              I know this is history and what are uniform extents but is the explanation correct.
              I just wana know the same.

              BR
              Sphinx
              Makes a much sense as worrying about misleading documentation on a TRS-80. I've been in IT 32 years and and Oracle DBA for about 17 of that -- and I've still got too much to learn about the current technology to worry about the obsolete stuff.
              • 4. Re: PCT increase
                Aman....
                I don't think that the SMON is disabled but the check to do the coalesce is not done. That said, as John and Ed has mentioned, it's not really important with the LMT in 2013 and that's the default and the only way now.

                Aman....
                • 5. Re: PCT increase
                  $phinx19
                  HI All,

                  Thanks.

                  BR
                  Sphinx
                  • 6. Re: PCT increase
                    rp0428
                    >
                    I have read this explanation about PCTINCREASE storage clause in doc.oracle site as:

                    If you want to keep all extents the same size, you can prevent the SMON background process from coalescing extents by setting the value of PCTINCREASE to 0. In general, Oracle recommends a setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing.

                    But as we all know that SMON is the background process which is used to coalesce adjacent free space and thereby reducing fragmentation.
                    So how come by setting pctincrease=0,disables the smon and also minimizes the fragmenation?

                    I know this is history and what are uniform extents but is the explanation correct.
                    I just wana know the same.
                    >
                    Yes - the explanation is correct.

                    Think about it - if all extents are the same size then there is nothing to coalesce. The purpose of coalescing was to consolidate different sized extents so that when a new extent needed to be allocated there might be enough space large enough for it.

                    If PCTINCREASE was the default of 50% then each extent would keep getting bigger and there would be extents of different sizes. Then when a new, large extent is needed there may be nothing available but a lot of smaller extents of different sizes.

                    If all extents are the same size then ANY of them can be allocated and no coalescing is needed.