Re: LMT's with autoallocate

I've occasionally used it as an easy method of saving space with PeopleSoft. PeopleSoft has a standard set of tablespaces and depending on the modules in use a lot of its tables and indexes remain empty. Instead of creating new tablespaces (a customization), autoallocate uses the smallest extent size for the empty objects. Less space is wasted.

>From a note I posted to another site about a year ago:
" Quick example of the space difference between UNIFORM and AUTOALLOCATE LMTs. I recently build a SYS database (Tools 8.42) for PeopleSoft Financials (8.40). Just looking at PSINDEX tablespace, I created it with EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K. The datafile also had AUTOEXTEND on, using the default extend of one block at a time (yeah it was slow, but that didn't matter for this test). After building the tablespace using uniform extents, I created a new LMT tablespace (PSINDEX_TMP) using AUTOALLOCATE. I then rebuilt all of my indexes from PSINDEX into PSINDEX_TMP. (at the end, I dropped and recreated PSINDEX and rebuilt everything back there to keep PeopleSoft happy).

The size of the original data file for psindex was ~2.6G (2,648,391,680 bytes). After rebuilding stuff into psindex_tmp, the size was ~ 2G (2,021,343,232 bytes).

So if there is a space limitation, autoallocate is definitely the way to go."

Henry

• Original Message ----- From: "Jared Still" <jkstill_at_gmail.com> To: <mark.powell_at_eds.com> Cc: "HANDM_at_polaroid.com" <handm_at_polaroid.com>; <oracle-l_at_freelists.org> Sent: Thursday, November 04, 2004 6:00 PM Subject: Re: LMT's with autoallocate

> I don't believe that anyone has actually supplied
> sufficient reason for using autoallocate.
>
> Why bother if it can run out of space, even though
> space is available?
>
> Shades of DD TBS fragmentation...
>
> Jared
>
>
>
> On Thu, 4 Nov 2004 16:35:14 -0500, Powell, Mark D <mark.powell_at_eds.com>
wrote:
> > I saw a demo of this where there were no 64M extents so the object took
8M
> > extents and then errored out even though there were still 8M extents
left
> > after it took so many of them, maybe 128. I do not think that I saved
the
> > post.
> >
> > Oracle has stated that the logic behind auto-allocate is subject to
change
> > without notice so observed behavior may not survive an upgrade.
> >
> > HTH -- Mark D Powell --
> >
> >
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Hand, Michael T
> > Sent: Thursday, November 04, 2004 4:14 PM
> > To: Oracle-L (oracle-l_at_freelists.org)
> > Subject: LMT's with autoallocate
> >
> > I understand the change in extent size as segments get larger in LMT's
with
> > autoallocate, but read on a 3rd-party application web site that the
smaller
> > extent sizes (8M, 1M, 64K) are used if there isn't enough freespace for
64M
> > extent. My experiments so far (using allocate extent and table
insertion)
> > have shown that if there is not 64M of freespace, the process errors
out.
> > Has anybody heard of this "feature", and under what conditions it
manifests
> > itself? Or is this unnamed 3rd party smoking something? The platform
is
> > Tru64 using 9.2.0.5.
> >
> > Thanks,
> > Mike Hand
> >
> > --
> > This transmission is intended only for use by the addressee(s) named
herein
> > and may contain information that is proprietary, confidential and/or
legally
> > privileged. If you are not the intended recipient, you are hereby
notified
> > that any disclosure, copying, distribution, or use of the information
> > contained herein (including any reliance thereon) is STRICTLY
PROHIBITED. If
> > you received this transmission in error, please immediately contact the
> > sender and destroy the material in its entirety, whether in electronic
or
> > hard copy format. Thank you.
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l