Re: Re:ASSM and tablespace fragmentation

It shouldn't matter because all the extents are the same size. You will lose some space if you create objects in parallel because (on average) each PX slave will waste half an extent as it finishes it's bit of the job. So, for example,  if you create an object using parallel 16 you will have 16 half empty extents in the object.

If the tablespace is freelist managed then the empty space will be below the highwater mark and will be scanned. (which means, I guess, that it may have to be formatted and written at some point - I'll have to check if Oracle has enhanced this part of the process). If you use ASSM (bitmap freespace management) the empty space will be skipped, but regions between the "low highwatermark" and the "high highwatermark" may be scanned in 16 block chunks.

You may want to create one very large object in two or three ways and see how it behaves, just in case there is an obvious advantage in one technology over the other.

My default choice would be locally managed, uniform at 8 - 64M, with freelist management - unless I had some time to test before going live. (And in your case you can always change your mind within a 10 day cycle after you've gone live anyway).

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: <ryan_gaffuri_at_comcast.net> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Cc: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> Sent: Wednesday, September 26, 2007 3:58 PM Subject: Re:ASSM and tablespace fragmentation

> thanks jonathan. If I am adding and dropping this much data do I have any risk
> of fragmentation with uniform extents? Or does it not matter because all the
> extents are the same size.
> -------------- Original message ----------------------

--
http://www.freelists.org/webpage/oracle-l