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@xxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Orlando L
*Sent:* Thursday, March 03, 2016 7:37 PM
*To:* oracle-l@xxxxxxxxxxxxx
*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.**
