Re: Algorithm for calculating extent size in LMT

Hi Nuno,

Wasn't suggesting that LMTs were problem-free, just that the number of extents *for a table* has no performance impact on deletes done to *that table*, subject to the proviso I made about the extent map. (For the record, I still don't like seeing more than 500 or so extents for anything on an 8K block system).

How many extents were we talking about for the rollback segment, though? I've never encountered anything as serious as you describe, but I can well imagine that internal rollback handling is not bazillion-extent aware.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
news:3c83682d.4195649_at_news-vip.optusnet.com.au...


> >The essential feature of locally managed tablespace is that we no longer

> >really give a damn how many extents a segment acquires, because extent

> >allocation is now a trivial operation for the database (though I agree

that


> >having the extent map for a segment fit into one block makes for some

small


> >performance improvement, and therefore limiting the number to the old

hard


> >limits (121 for 2K blocks, 504 for 8K blocks and so on) is still not a

bad


> >idea).

>

>

> I won't buy into the "no more problems with LMTs" concept, Howard.

> I'm currently running them in a production system with 8.1.7/NT.

> There are indeed problems.  Things slow down dramatically once you go

> over a certain boundary in terms of number of extents.  Would love to

> have the numbers for you at the drop of a hat, but I don't.

>

> First noticed the problem with a recursive PL/SQL update that ate up

> the RLB.  All the rollback segment traffic became horrendously slow as

> the number of extents used went ballistic.  Wasn't full, but there

> were a lot of 16K extents(block size is 8K).  I mean a LOT.  Boom,

> performance of the system went out the window.

>

> Maybe Jonathan will have some numbers?  I can't afford the time to

> chase this up, gotta get this system out by the end of April and no

> time left for experiences.  But it's worth doing some research in this

> area.

>

> IMHO, it's not an absolute number.  Relative to the size of the

> datafile.  Which leads me to believe there is some "sweet" boundary or

> ratio between datafile size, db block size and fixed extent size for

> LMT beyond which things will go "bonk".  It's important that someone

> does a little bit of investigative work in this area to make sure we

> don't collectively stick our feet in mud with a real live system.  Any

> takers?

>

>

> Cheers

> Nuno Souto

> nsouto_at_optushome.com.au.nospam