Re: Simple Oracle Query Taking Too Long

DA Morgan wrote:
> Frank van Bortel wrote:
> > DA Morgan schreef:
> >>
> >> That is about as horrible a definition as I can imagine. I can't think
> >> of a single justification for either the 64K extents or a pctincrease
> >> that is anything except 1.
> >>
> >
> > And what would be wrong with 64kB extents?
> >
> > I'm off to the Chimay :D Sayonara!
>
> The problem with 64K extents is one Howard Rogers ranted about many
> years ago here at c.d.o. The fact that it doesn't match any operating
> system. Or at least not Windows or any variant of UNIX or Linux.
>

Block size isn't extents, is it? Block size impacts performance writing/getting blocks in a dynamic manner, extent size merely means how many are being gotten when you extend the segment.

I've heard tell, but have no proof (and would like some) that LMT uses 64K extents under the covers, no matter what you tell it.

The issue in this thread is we are not certain if the OP is using DMT. If he is, then using 64K extents for 80M rows implies a possible DMT/dictionary problem. If he is not, nothing wrong with 64K extents. But of course, he said the _initial_ extent was 64K, so the next one (or does it get 5, so the sixth one...) is 1.01* 64K, and the one (or is it 5?) after that 1.01**2*64K and so on. So we would need to know what NEXT is currently.

Of course, this has dragged on so long and it's been so long since I've looked at DMT's I may be completely stuffed, so let me know if I am or should be.

I don't think anyone answered the OP's basic question of how big to make extents, either. The answer again depends on whether LMT's are being used. If not, the answer is to use them (not by using the DMT to LMT migration, either, which merely maintains any existing problems). If you can't use them, you should make all initial and next extents the same size throughout the tablespace - there are a number of ways to manage this, some people used to recommend splitting tablespaces by object size or volatility. So, very roughly (and I mean so rough this isn't really right), see how big the data in each table is, and figure how big extents would have to be to make it fit in less than some arbitrary number like 100 extents, rounding up to some multiple of 1M. Then make the defaults of the tablespace so the smaller tables will generally fit in an extent. There is generally no performance advantage to making everything fit in one extent (that is an old myth that evolved from the days where some objects were limited to 121 extents). But thousands of extents are a different matter with DMT, and if you have a lot of small tables you don't want to waste space, either. You want to try to make all extent sizes the same (or at least multiples of same) so that when you delete things you leave holes properly sized to have new things. With the pctincrease slowly incresing, your new things won't fit into the holes left by deleted old things. Howard explains it better:
http://www.dizwell.com/prod/node/62

jg

--
@home.com is bogus.
Dizwell's block size http://www.dizwell.com/prod/node/58