Re: number of extents question

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:alptvf$1mmv$1_at_ID-152732.news.dfncis.de...
> Thank You, Howard, for Your comments. Let me respond to some of them.
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> schrieb im Newsbeitrag
> news:3d805179_at_dnews.tpgi.com.au...
> >
> > "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
> > news:alpeuc$1rfm94$1_at_ID-152732.news.dfncis.de...
> > > Hi, Yong Huang,
> > >

 ...
> > >
> > > For Tables, we export them, make a COALESCE of the TS
> > > and import them again.
> >
> > Gawd! What a lot of work.
>
> Indeed. To be precise, I personally never did - the former specialists
 from
> whom we inerited the db did.
> But they are gone.

Not before time I'd say !!

>
> > And for no good reason, too! I suppose there's a
> > reason you haven't switched to Locally Managed Tablespace, where all
 this
> > kerfuffle would be completely unnecessary?
>
> LMT came with 8i, and we have just upgraded 2 months ago. And yet
> we did not change the DMTs. It´s time, though ...

I think so ...

Look in the archive here for recommendations on how best to do this. However, recreating objects shouldn't be a problem for you with all this experience ;)

>
> >
> > >compress=n makes sense for uniform extent sizes ...
> >
> > Compress=n makes sense in just about every situation I can imagine.
 There
> > are NO good reasons why a segment should comprise just one extent.
>
> OK, I understand now, reading the postings.
> But why then, many books point out that a segment _should_
> be put into _one_ extent ? .
> Why do still many dba courses teach this ? Are they just old fashoined,
> or did something change inside of the RDBMS, so that it once
> might have had a meaning ?

OK. Going back to the days when I was young little kid living on gob stoppers and jelly babies (I always asked for *male* jelly babies as they had that extra bit of jelly :) there was a time when you could only have a finite number of extents. In those dark days, 2K blocks were not uncommon and the figure of 121 maxextents was common. The larger the block size, the more extents as this limit was due to being only able to have one extent map which was stored in the one and only segment header. Therefore if an object was approaching it's maxextents you had a real issue as that object couldn't physically grow if it ever did hit the maxextents. So what did you do. You exported the table, set compress=y, reimported the object and ah ha, the object is back to 1 extent again. Saved. In those days, PCTINCREASE even made some sense as it helped to prevent the allocation of extra extents and the hitting of this limit.

That's history. I've grown up to be a somewhat suave, handsome and sophisticated adult and Oracle has grown up to have unlimited extents, locally managed tablespaces and the such to make all this totally irrelevant.

>
> >
> > >i
> > > never tried it before,
> > > but I may do it soon.
> > >
> > > For Indices, I do
> > >
> > > ALTER INDEX my_index
> > > REBUILD TABLESPACE TS_IND
> > > STORAGE (
> > > INITIAL 1M
> > > NEXT 1M
> > > PCTINCREASE 0
> > > MAXEXTENTS 999999
> > > )
> >
> > Firt mistake. Index very, very seldom need rebuilding. The re-use of
 deleted
> > leaf entries happens all the time making a rebuild an expensive
 operation,
> > and an unnecessary one too. Possible exceptions are indexes on
 monotonically
> > incrementing sequence numbers, where earlier entries cannot possibly be
> > re-used
> I am also thankful to any critics like this. I admit, I do not have much
> experience
> in reorganising.

Totally agree. Rebuilding indexes should be quite a rare activity. The other example I can think of is if a table has shrunken in data and no longer contains the number of rows it once did (and is unlikely to do so). A rebuild may be of benefit.

>
> >
> > > COALESCE
> > > /
> >
> > Second mistake. If you've made the mistake of rebuilding an index, a
> > coalesce will achieve precisely nothing. It is designed to scan along
 the
> > base of an index, and merge adjacent partially-filled leaf nodes into
> > single, fully packed leaf nodes. After a rebuild, there will be no
 partially
> > filled leaf nodes. So the coalesce is yet more I/O to precisely no
 effect.
> >
> Thank You for this information. Sereval books that I have been through
> did not mention it that precise.

Again totally agree. If you feel there may be "gaps" in the index structure and by that I mean many blocks are close to being empty and are unlikely to be reused (a range of data is deleted and not to be re-entered) then a coalesce of the index may be of benefit. But never after a rebuild, the structure should be perfect. it's brand new.

>
> > > ALTER TABLESPACE TS_IND COALESCE
> > > /
> > >
> >
> > You don't ever need to coalesce locally managed tablespace.
> We still do not have LMT, see more below.
>
> > > after every rebuild. I do not know if COALESCE could be _overdone_,
 too,
 but
> > > I had a lot of
> > > fragmentation solved by this successfully.
> > >
> >
> > Why you are curing fragmentation, and not preventing it in the first
 place,
> > I guess I can't imagine.
> Pity, I came too late for the first place - because I would have done it.

If you use LMT, coalescing the tablespace is totally redundant.

> The db has been created 3 years ago.
> And unless I cannot convince the management (for reasons taht I won´t
 reveal
> here),
> I may not be allowed to make
> "too big" changes. I like the concept of LMT just as well, unfortunately,
 it
> is
> not well known around me. And anything new is "suspicious", You know ...

A "big change" that is dramatically and drastically going to reduce overheads and improve efficiency is something that management should bloody well be interested in doing.

>
> Though I do some admin works on it, I do not "own" the db. The management
> wants to keep control over any changes, so I am somewhat limited what to
> to - yet ...

At this point, you probably don't want to own this little mamma. However if you implement some of these changes it won't be long until management very much wants you to own it !!

Good Luck

Richard

>
> Jan=)
>
>
Received on Thu Sep 12 2002 - 08:42:27 CDT