Re: ASSM and tablespace fragmentation

I agree that ASSM could cause the partitions to be large (does anyone have a case study which shows if there is a difference if it is only 1 process (session) doing inserts and if there are multiple processes doing inserts [after all, ASSM is designed to avoid "hot" free lists under concurrency] ??)

Direct Loads however, will always, be _above_ the HWM.

But then, again, if the partitions are being dropped every 10 days, the space released those "bloated" (if they do get bloated) would be reusable.

If the tablespace was using AUTOALLOCATE, then you'd have differently sized extents but still , generally, multiples of the smaller sized ones. That way, the new partition starts with 64K extents and grows immediately (if you are doing xxGBs/day) to 64M extents.

Hemant

At 03:10 PM Wednesday, Frits Hoogland wrote:
>ryan,
>
>I've read (and witnessed) the phenomenon you are describing.
>ASSM uses bitmap blocks instead of one (or more) freelists.
>Probably because of the random bitmap block usage, and random data
>block allocation, some blocks did not fill up to their maximum.
>Also, using direct loads some blocks remained empty entirely
>(because the direct load is done behind the HWM)
>- I don't know if ASSM is more optimised or better in 10gr2/11g (anyone?)
>- As far as I see ASSM, it is an optimisation for RAC (because of
>the randomness of administration in the bitmap blocks)
>- When data warehouse like actions are done, probably freelist based
>storage would be better, because multi block reads are really
>sequential, because multiblock reads are not done really sequential.
>(anyone did investigation in this area)
>
>So, some answers, but also much more questions
>
>frits
>
>On 9/26/07,
><mailto:ryan_gaffuri_at_comcast.net>ryan_gaffuri_at_comcast.net <
><mailto:ryan_gaffuri_at_comcast.net>ryan_gaffuri_at_comcast.net> wrote:
>There was an article written a few years ago talking about certain
>cases where you can have tablespace fragmentation when using ASSM. I
>think Niall Litchfield wrote this(I could be wrong). I believe this
>was due to deletes. I am not using deletes, but will be dropping
>alot of partitions and am wondering whether I may have problems with
>this down the road. Here is my scenario.
>
>1. initally go to production with 500 GB of data
>2. This will grow to between 25-50 TBs over 15 months.
>3. We will load 1/10th of the data/day into the database. So at
>500GB we will load 50 GBs/day at 25-50 TBs we will load 2.5 - 5 TBs/day.
>4. tables are partitioned by date with hourly partitions.
>5. We will drop partitions that are 10 days old.
>
>So we are recycling data every 10 days while our database size is
>growing. Does this scenario have any risk of tablespace
>fragmentation with ASSM? We may use ASM, but I don't think that
>matters in this case.
>--
><http://www.freelists.org/webpage/oracle-l>http://www.freelists.org/webpage/oracle-l
>
>

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

"There is more to life than increasing its speed." Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

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