Re: peoplesoft and partitioning

From: Orlando L <oralrnr_at_gmail.com>
Date: Fri, 4 Mar 2016 05:13:52 -0600
Message-ID: <CAL8Ae77t4BAS9Pddbt5TDW=G1OcCnQX-iRMtS6tzte5hEQgmhA_at_mail.gmail.com>

Thanks a lot Thomas and Scott. Scott how big are the partitioned tables in terms of size. How big is the DB itself?

On Thu, Mar 3, 2016 at 7:23 PM, Deas, Scott <Scott.Deas_at_lfg.com> wrote:

> Orlando,
>
>
>
> We’re partitioning PS_LEDGER, PS_LEDGER_BUDG and PS_JRNL_LN, as well as
> several associated indexes (using same partitioning strategy). We’re range
> partitioning by FISCAL_YEAR,ACCOUNTING_PERIOD and list sub-partitioning by
> BUSINESS_UNIT.
>
>
>
> We’re LIST partitioning PS_TSEL_P_TAO3, PS_TSEL_P_TAO4, and PS_TSEL_P_TAO5
> by CHARTFIELD, all indexes on these tables follow the same partitioning
> strategy.
>
>
>
> We’re LIST partitioning PS_COMBO_DATA_TBL by PROCESS_GROUP, all indexes on
> this table follow the same partitioning strategy.
>
>
>
> We’ve seen significant improvements while running allocations processing
> at month end (especially once we partitioned the PS_LEDGER unique index).
>
>
>
> Post-implementation maintenance is just a matter of adding the additional
> partitions each year – and I’d recommend not dumping all of your partitions
> into one tablespace, as logically designing the tablespace layout will
> allow for future compression, storage tiering, tablespace point in time
> recovery, transportable tablespace migrations, etc (I say this after seeing
> partitioned multi-terabyte tables with all partitions shoved in a single
> tablespace).
>
>
>
> We’ve also recently implemented BASIC COMPRESSION on older periods.
>
>
>
> The issue we’re facing now, and for which I’m working at 8pm tonight, is
> that we’re upgrading from 9.1 to 9.2 and the scripts that our PeopleSoft
> admin has been running (provided by Oracle), don’t take any of our
> partitioning or compression into account, so I’ve been re-writing the
> process to fix this.
>
>
>
> Overall, with the proper design and forethought, the benefits of
> partitioning will far outweigh any of the additional maintenance tasks you
> may face.
>
>
>
> Thanks,
>
> Scott
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Orlando L
> *Sent:* Thursday, March 03, 2016 7:37 PM
> *To:* oracle-l_at_freelists.org
> *Subject:* peoplesoft and partitioning
>
>
>
> Hi
>
>
>
> Our peoplesoft Fin system is getting bigger. Currently we do not have
> partitioned tables in there. We are thinking partitioning. Can anyone share
> their experiences with partitioning in psft?
>
>
>
> I have several questions on
>
> - what to partition by (this probably is the toughest - the tables don't
> make any good sense),
>
> - the indexes, esp, the global ones,
>
> - post partition-implementation maintenance,
>
> - query and db performance afterwards, etc.
>
> Does a paper exist for the above. TIA.
>
>
>
> Orlando.
>
> Notice of Confidentiality: **This E-mail and any of its attachments may
> contain
> Lincoln National Corporation proprietary information, which is privileged,
> confidential,
> or subject to copyright belonging to the Lincoln National Corporation
> family of
> companies. This E-mail is intended solely for the use of the individual or
> entity to
> which it is addressed. If you are not the intended recipient of this
> E-mail, you are
> hereby notified that any dissemination, distribution, copying, or action
> taken in
> relation to the contents of and attachments to this E-mail is strictly
> prohibited
> and may be unlawful. If you have received this E-mail in error, please
> notify the
> sender immediately and permanently delete the original and any copy of
> this E-mail
> and any printout. Thank You.**
>

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

Received on Fri Mar 04 2016 - 12:13:52 CET