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.
>
> > 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 ...
>
> >
> > >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 ?
>

That is an excellent question -and in this particular case, it can (I think) be answered. Oracle itself put out a piece of information with version 5 (read that and weep: version FIVE) stating that modest performance benefits would arise if everything was in one extent. It wasn't particularly true then, but given that the number of extents a segment could have was finite, and strictly enforced, I suppose they were really just trying to say 'don't run out of extents, because then you won't be able to extend further'.

But this got picked up by everyone as saying 'one extent in and of itself brings performance benefits'. And the myth has persisted ever since.

Oracle gets a tad lazy in these matters, too: when it sees a myth brewing, it should strenuously squash it, but rarely does (in its defence, it's got better things to do). Occasionally, it promotes things I consider to be bad practice (such as the 'block size depends on what sort of application you have' one) because it makes for an easy story in the classroom, and however wrong it may be, the consequences are not (normally) that serious. I expend a lot of effort in the classroom trying to put the truth of the matter before students, but not every DBA instructor is going to do that.

Regards
HJR
> >
> > >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 reorganizing.
>
> >
> > > 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.
>
> > > 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.
> 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 ...
>
> 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 ...
>
> Jan=)
>
>
Received on Thu Sep 12 2002 - 14:34:46 CDT