Loading ...
Sorry, an error occurred while loading the content.

Re: Some limited experience with PeopleSoft/Oracle database with LMT

Expand Messages
  • Henry Poras
    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).
    Message 1 of 2 , Jun 3, 2003
    • 0 Attachment
      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
      to go.

      Henry

      --- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@g...>
      wrote:
      > Hello
      >
      > I haven't recreated PeopleSoft delivered tables as LMTs in a
      production
      > environment, but that is mainly due to not getting there early
      enought.
      >
      > I have experimented with LMTs on a demo database. All the PS
      delivered
      > tablespaces were created as LMT with autoallocate - so the segments
      are
      > sized as 64K, 1M and 8M. Even A PeopleSoft HR database delivered
      several
      > thousand tables, Financials is over 10000! Many of these tables
      will be
      > empty. The resultant demo database will be larger, but I am fairly
      sure
      > that if this was done in a real database there would not be any
      additional
      > space overhead, there might even be a saving.
      >
      > The problem is that PeopleSoft chooses all sorts of sizes for its
      tables,
      > and with the continual flow of updates causing tables to be altered
      by
      > recreation (which you should always do in production to avoid
      chaining) 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 for
      autoallocate)
      > or multiples thereof, so they are more likely to be reused. In
      Financials
      > especially, this effect is magnified by the use of temporary
      working storage
      > tables which grow, and if you are attentive are periodically
      truncated, thus
      > dropping all but the first extent in the object.
      >
      > In a dictionary managed tablespace continguous free space extents
      may be
      > reported separately on DBA_FREE_SPACE, and so may be reused
      separately
      > unless the free space is coalesced. This can actually promote
      fragmentation
      > of free space.
      >
      > Jonathan Lewis generally recommends using a uniform extent size over
      > autoallocate because you can then redistribute the tables into
      different
      > tablespaces with different uniform extents sizes. This is probably
      a better
      > approach, but if you want to retain the PS tablespace model then I
      think you
      > are probably forced into autoallocate for at least the xxAPP
      tablespaces.
      > You might choose a uniform extent size for some of the xxLARGE
      tablespaces.
      >
      > (Since I have mentioned Jonathan his website is well worth a look -
      > www.jlcomp.demon.co.uk)
      >
      > The best part of this is that you don't have to do anything to
      PeopleSoft in
      > order to implement LMTs. Although the PS DDL will have a storage
      clause
      > specify in initial, next etc this will be SILENTLY ignored by
      Oracle when
      > the tablespace is LM. Note that if you use SETTABLE.sqr and
      SETINDEX.sqr to
      > backport the storage options from USER_TABLES and USER_INDEXES back
      into the
      > PeopleTools tables, the storage values in Oracle will be NULL and
      they will
      > become 0 in the PeopleSoft database. Storage options on at least
      user
      > specified indexes will be moved to the target database during an
      upgrade and
      > if you are upgrading from database with LMTs to a database without
      LMTs the
      > index create may fail with an invalid storage clause. So, you
      might choose
      > to comment the storage clause out of the PS DDL model just to be on
      the safe
      > side.
      >
      > I have used LMTs on two Global Payroll projects where I introduced
      new
      > tablespaces. The GP result tables become very large and so I
      partitioned
      > them to match the GP streams. So at one site we have 30 payroll
      streams (ie
      > there are 30 payroll calculation processes running in parallel) and
      each
      > stream corresponds to one of 30 partitions. I created two new
      tablespaces
      > for each stream (one for tables, and one for indexes). The indexes
      are
      > locally partitioned. Partition 1 of each table goes into the same
      > tablespace, etc. All the new tablespaces are locally managed with
      uniform
      > extent sizes of 20M. This was chosen because some of the
      partitions were
      > expected to reach several hundred Mb.
      >
      >
      >
      > _________________________
      > David Kurtz
      > Go-Faster Consultancy Ltd.
      > phone: +44 7771 760660
      > fax: +44 7092 348865
      > mailto:david.kurtz@g...
      > 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.
      Managed
      > Tablespaces ?
      > >
      > > LMTs really add more to ease of administration rather than a
      benefit
      > > in performance. See the following article from Jonathan Lewis
      > > http://www.dbazine.com/jlewis8.html
      > > Henry
      > >
      > > --- In psftdba@yahoogroups.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.
    Your message has been successfully submitted and would be delivered to recipients shortly.