Re: Some limited experience with PeopleSoft/Oracle database with LMT
- Quick example of the space difference between UNIFORM and
AUTOALLOCATE LMTs. I recently build a SYS database (Tools 8.42) for
PeopleSoft Finanacials (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
--- In firstname.lastname@example.org, "David Kurtz" <david.kurtz@g...>
> I haven't recreated PeopleSoft delivered tables as LMTs in a
> environment, but that is mainly due to not getting there earlyenought.
> I have experimented with LMTs on a demo database. All the PS
> tablespaces were created as LMT with autoallocate - so the segmentsare
> sized as 64K, 1M and 8M. Even A PeopleSoft HR database deliveredseveral
> thousand tables, Financials is over 10000! Many of these tableswill be
> empty. The resultant demo database will be larger, but I am fairlysure
> that if this was done in a real database there would not be anyadditional
> space overhead, there might even be a saving.tables,
> The problem is that PeopleSoft chooses all sorts of sizes for its
> and with the continual flow of updates causing tables to be alteredby
> recreation (which you should always do in production to avoidchaining) you
> end up with free space fragments scattered through the tablespace.In an
> LMT the spaces will be of a uniform size (3 uniform sizes forautoallocate)
> or multiples thereof, so they are more likely to be reused. InFinancials
> especially, this effect is magnified by the use of temporaryworking storage
> tables which grow, and if you are attentive are periodicallytruncated, thus
> dropping all but the first extent in the object.may be
> In a dictionary managed tablespace continguous free space extents
> reported separately on DBA_FREE_SPACE, and so may be reusedseparately
> unless the free space is coalesced. This can actually promotefragmentation
> of free space.different
> Jonathan Lewis generally recommends using a uniform extent size over
> autoallocate because you can then redistribute the tables into
> tablespaces with different uniform extents sizes. This is probablya better
> approach, but if you want to retain the PS tablespace model then Ithink you
> are probably forced into autoallocate for at least the xxAPPtablespaces.
> You might choose a uniform extent size for some of the xxLARGEtablespaces.
> (Since I have mentioned Jonathan his website is well worth a look -
> The best part of this is that you don't have to do anything to
> order to implement LMTs. Although the PS DDL will have a storageclause
> specify in initial, next etc this will be SILENTLY ignored byOracle when
> the tablespace is LM. Note that if you use SETTABLE.sqr andSETINDEX.sqr to
> backport the storage options from USER_TABLES and USER_INDEXES backinto the
> PeopleTools tables, the storage values in Oracle will be NULL andthey will
> become 0 in the PeopleSoft database. Storage options on at leastuser
> specified indexes will be moved to the target database during anupgrade and
> if you are upgrading from database with LMTs to a database withoutLMTs the
> index create may fail with an invalid storage clause. So, youmight choose
> to comment the storage clause out of the PS DDL model just to be onthe safe
> I have used LMTs on two Global Payroll projects where I introduced
> tablespaces. The GP result tables become very large and so Ipartitioned
> them to match the GP streams. So at one site we have 30 payrollstreams (ie
> there are 30 payroll calculation processes running in parallel) andeach
> stream corresponds to one of 30 partitions. I created two newtablespaces
> for each stream (one for tables, and one for indexes). The indexesare
> locally partitioned. Partition 1 of each table goes into the sameuniform
> tablespace, etc. All the new tablespaces are locally managed with
> extent sizes of 20M. This was chosen because some of thepartitions were
> expected to reach several hundred Mb.Managed
> David Kurtz
> Go-Faster Consultancy Ltd.
> phone: +44 7771 760660
> fax: +44 7092 348865
> web: www.go-faster.co.uk
> sms: sms@g...
> PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
> > Message: 1
> > Date: Wed, 19 Feb 2003 14:40:43 -0000
> > From: "Henry Poras <hporas@a...>" <hporas@a...>
> > Subject: Re: Any experience with a Oracle database with Loc.
> Tablespaces ?benefit
> > LMTs really add more to ease of administration rather than a
> > in performance. See the following article from Jonathan Lewis
> > http://www.dbazine.com/jlewis8.html
> > Henry
> > --- In email@example.com, "litbighor <lehuyluan@h...>"
> > <lehuyluan@h...> wrote:
> > > Hi,
> > >
> > > I would like to have feedback/experience about PS with a
> > > Oracle database with Loc. Managed Tablespaces ?
> > >
> > > Do you use autoallocate ?
> > > It seems difficult to me to use uniform size with Peoplesoft ...
> > >
> > > Any performance improvement ?
> > >
> > > I have a LMT database but it's not in production yet.