tablespace coalesce
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: tablespace coalesce

  1. #1
    Join Date
    Nov 2000
    Posts
    169

    Question

    When does smon automatically coaleseces in a tablespace?
    Is it when pct increase is 0 or when pct_increase is non Zero

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    The SMON process will wake up every 5th minute and checks for tablespaces with default pctincrease != 0

  3. #3
    Join Date
    Aug 2001
    Posts
    111
    If you're thinking on making your tablespace anything but pctincrease = 0 forget it, the overhead is not worth the savings. You get better results (in v8 and upwards) setting the minimum extent, making sure initial and next are the same and getting large tables (with larger initial/next extents) in their own tablespace.

    Just write a scheduled script to run it once a day, every 6 hours, immediately before and after heavy DML.

    Have Fun
    Performance... Push the envelope!

  4. #4
    Join Date
    Nov 2000
    Posts
    169
    ROOBARON. Can you explain yopur point again? I did not get it.

  5. #5
    Join Date
    Aug 2001
    Posts
    111
    Setting PCTINCREASE on a tablespace to a non-zero value eg 1 will mean that SMON will wake up every 5 minutes to try a coalesce any tablespaces.
    The overhead involved is non-trival (locking, excessive recursive calls)

    To automatically coalesce tablespaces just write some SQL to do the job and schedule it to run once per day (at nite) or whatever period is correct for your application.

    The other stuff, minimum extents and same extent sizes will reduce fragmentation due to every extent being able to be used by the tables within that tablespace.
    This extra stuff was purely to point out that should be unnecessary to use SMON (via PCTINCREASE != 0) to coalesce fragmentation as it is cured thru being more proactive with the space management.

    Hope this helps
    Performance... Push the envelope!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width