Re: should initial = next?

Whether a table is comprised of 1 or 1000 extents will, ordinarily, and subject to a bunch of provisos, have no effect on the speed at which data is retrieved.

The provisos are essentially this: if a table is ordinarily accessed via indexes, (ie, you are retireving only a few of the rows from the table in any one select), then the index will direct you to the right blocks wherever they may be stored, and however discontinuously they are stored.

Full table scans are a different story: theoretically, there will be some delay when your disk head reads the end of extent 1, and has to position itself for the start of extent 2. Obviously, in that situation, contiguous extents would speed things up.

But it would only be a question of head latency, and with today's hardware, I can't see it making a vast difference to most people or most situations. It's also the case that if you create your tablespaces with multiple data files, each file on a separate physical device, Oracle will automatically allocate extents in a round robin fashion: extent 1, disk 1...extent 2 disk 2, extent 3 disk 3, extent 4 disk 1, extent 5 disk 2 and so on. That would cut the latency down to trivial levels.

And if you've got some form of hardware striping going on, the issue is entirely moot.

All of which misses the point: 1000 extents is still a bad idea because of the havoc it plays with the UET$ and FET$ tables in the data dictionary, which will impact on every segment in the database. For that reason, I still advice no more than 6 extents per segment in dictionary managed tablespace (though disaster is not going to strike if you let it creep up to 10 or 20). In locally managed tablespace, it's not an issue. Have as many extents as you like.

Regards
HJR "Steve Salvemini" <steve.salvemini_at_adelaide.edu.au> wrote in message news:3A807CE0.25F56F8D_at_adelaide.edu.au...
> I saw a another comment by Dave Ensor:
>
> "Your goal is to minimize the number of extents on disk. Access to
> contiguous areas of disk is much faster than access to noncontiguous
> areas. In one test that we did on a 4,000-row table, we found that when
> the entire table fit on one extent, it took 0.76 second to scan it; when
> the table was spread over 10 extents, it took 3.62 seconds.
>
> We have found that the existence of a small number of such extents
> (fewer than five) doesn't seem to affect performance very much, but it
> is still good practice to store your objects in a single extent. (In the
> next section, we describe how to size the table for the reorganization
> while not wasting valuable disk space.)"
>
> so the other statement I'm speaking about below are in
> http://www.dbazine.com/ensor1.html
>
> In one article it's basd to have extents and in the other its fine to
> have extents, in one artice you should squeeze the table into one
> extent, presumably the next then isn't the same size, and in the other
> next should = initial.
>
> Thanks for the comments so far guys, I just seem to be getting
> complicting ideas, even from the Author. Are the comments from Dave
> above to for older versions of oracle or did he change his mind?
>
>
> Steve Salvemini wrote:
> >
> > Hi, I've read recently of the importance setting the initial extent size
> > and all next extent sizes to the same value, and that the overhead of
> > for eg 1000 extents is insignificant compared to the gains [Dave Ensor -
> > BMC Software]
> > (I hope I'm reading this right Dave!)
> >
> > Anyway, as an example, if I've got a table of 900Mb, currently we are
> > setting:
> > initial extent to 900Mb
> > next entent of 256K
> > max extents 400
> >
> > From reading this article, is it better to have a setting something like
> > initial extent to 1098 K
> > next entent of 1098 K
> > max extents 1000
> > (ie 1098*1024 * 600 extents = 900Mb, leaving 200 extents free (=200*1098
> > = 219Mb for growth))
> >
> > Is it generally accepted out there that this is a better approach or is
> > this splitting hairs (or did I totally misunderstand this) ?
>
> --
> -----------------------------------------------------------
>
> Steven Salvemini
> Peoplesoft Technical Specialist, Information Technology Services
> ADELAIDE UNIVERSITY SA 5005
> AUSTRALIA
> Tel: +61 8 8303 6358 Fax: +61 8 8303 4400
> Email: steve.salvemini_at_adelaide.edu.au
>
> -----------------------------------------------------------
> This email message is intended only for the addressee(s)
> and contains information which may be confidential and/or
> copyright. If you are not the intended recipient please
> do not read, save, forward, disclose, or copy the contents
> of this email. If this email has been sent to you in error,
> please delete this email and any copies or links to this
> email completely and immediately from your system. No
> representation is made that this email is free of viruses.
> Virus scanning is recommended and is the responsibility of
> the recipient.
Received on Wed Feb 07 2001 - 01:52:53 CST