Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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/58Received on Fri Sep 08 2006 - 14:10:42 CDT
![]() |
![]() |