Re: autoextend size vs. extent size in LMT

Datafile resizes are always reported in the alert log. You should be able to read the last part of that file, and see whether multiple re-sizes are performed or just one.

My best guess is that it will be a single resize of the uniform extent size, since the general rule on autoextensions is that, without a next specified, you get whatever the segment wanting to extend demands to acquire as its next extent size.

As for convincing your associate that large NEXTs are advisable (and that autoextend is a naff idea for slipshod DBAing only), even a looney should see that it's all a question of I/O. If I visit the disk 10 times to acquire 32K each time, or once to acquire 320K, which do you think will have less impact on the performance of the database for other users?

Regards
HJR "Ed Stevens" <spamdump_at_nospam.noway.nohow> wrote in message news:3d088d39.174079382_at_ausnews.austin.ibm.com...
> On Thu, 13 Jun 2002 20:48:49 +1000, Nuno Souto
<nsouto_at_optushome.com.au.nospam>
> wrote:
>
> >In article <3d075d9a.96364364_at_ausnews.austin.ibm.com>, you said (and I
> >quote):
> >>
> >> If the file is full when another table extent is needed, will he get
the
> >> necessary 32k (as specified for the TABLESPACE) in one operation, or
will go by
> >> the 4k specified for the FILE and repeat as necessary until he has
enough space
> >> to satisfy the 32k?
> >
> >I suspect it's gonna be 4K as many as needed to get to the required
> >space.
> >
> >>
> >> Whatever the answer, is there a way to demonstrate and verify?
> >
> >Try it?
> >
>
> That's what I'm not sure about how to do. Suppose I set up a test by
creating a
> smallish TS, create a table in it, and insert into the table up to the
point the
> TS is full -- one more insert will require an additional segment extent,
which
> in turn will require a file extent. Now, I issue an INSERT statement and
when
> it completes, the file has extended. What can I look at to tell me if
Oracle
> did a single extent operation on the file, or multiple operations until it
had
> enough to satisfy the segment extent.
>
> Yes, I know it's a moot point if I make the file extent specification some
large
> multiple of the TS segment size. But as usual, I'm having a hard time
> convincing my associate. Even after convincing him the file extent needed
to be
> AT LEAST as large as the segment extent, he didn't want to go any larger
than
> the AT LEAST . . .
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)
Received on Sat Jun 15 2002 - 19:59:57 CDT