RE: locally managed autoallocate (was: Separate Indexes and Data)

> -----Original Message-----
> From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com]
> Sent: Tuesday, September 30, 2003 7:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: locally managed autoallocate (was: Separate Indexes and Data)
>
>
> > Ive read the book. PCTINCREASE is basically set to 100% so
> > the extent sizes double. Thats 'basically' how it works. I
> > have seen some posts on dejanews saying it doesnt necessarily
> > work this way and some people are finding large extent sizes
> > with just a few extents and when tables are dropped this is
> > leading to fragmentation. It hasnt happened to me, but the
> > posts on dejanews were from some pretty good posters. So Im
> > playing conservative. We also had one of the contributors
> > here mention issues.
>
>
> I think Jonathan Lewis has explained the algorithm before,
> but it's also something that we have investigated here.
> The algorithm (ignoring some details) is:
> There will be 4 extent sizes used, 64K, 1M, 8M, 64M
> As long as object allocation is 1M or less, 64K extent sizes are used,
> When object allocation is between 1M and 64M, 1M extent sizes
> are used.
> When object allocation is between 64M and 1G, 8M extent sizes
> are used.
> When object allocation is more than 1G, 64M extent sizes are used.
>
> However, when you initially create the object, the extents
> are determined by figuring out the space allocated to the
> newly created object taking into account the INITIAL, NEXT,
> PCTINCREASE, MINEXTENTS storage parameters. So the object
> might start off with 1M extents instead of starting off with
> 64K extents. The algorithm is similar to the one outlined
> above but it is more complicated. The NEXT and PCTINCREASE
> seem to be ignored after the object is created.
> e.g.
> create table ... tablespace locally_managed_autoallocate
> storage (initial 1M next 512K minextents 15 pctincrease 0) ... ;
> Initial allocation will be 1M + (15 - 1) * 512K = 8M
> When you create the table, you will see eight extents, each
> of one megabyte.
>
> There are additional wrinkles, but I don't think the
> algorithm has "bugs".
>
> I don't think that there really is "fragmentation" in the
> sense that an unused extent will remain unused forever. All
> extents will be in one of the 4 sizes mentioned above, and
> all are subject to reuse at some point.

Theoritically, perhaps, but what if an existing table needs to auto-extend at 1M and all that's left in the table is 16 (or whatever) 64K chunks. I still maintain that system-managed tablespaces are barely better than DMTs -- fragmentation is still potentially a problem and needs to be monitored.

On the flip-side, LMT segments need to be watched too in case they are growing beyond the design of the TS (e.g. more than 1024 or how ever many extents). I'd much rather deal with the latter because it's much less likely to happen unexpectedly in our environment.

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).