Re: LMT's with autoallocate
- From: "Henry Poras" <hporas@xxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>, <mark.powell@xxxxxxx>
- Date: Fri, 5 Nov 2004 08:53:09 -0500
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@xxxxxxxxx>
To: <mark.powell@xxxxxxx>
Cc: "HANDM@xxxxxxxxxxxx" <handm@xxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxx>
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@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hand, Michael T
> > Sent: Thursday, November 04, 2004 4:14 PM
> > To: Oracle-L (oracle-l@xxxxxxxxxxxxx)
> > 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.
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
