Orlando,
None of our tables are particularly huge. Below is the list (you’ll see I
missed a few tables in my earlier email).
SEGMENT_NAME SIZE_MB
---------------------- ------------
PS_COMBO_DATA_TBL 1,304
PS_JRNL_LN 127,074
PS_LEDGER 46,757
PS_LEDGER_BUDG 50,702
PS_LEDGER_TMP 66
PS_LEDGER_TMP_BUDG 216
PS_TSEL_P_TAO4 65
PS_TSEL_P_TAO5 48
Thanks,
Scott
From: Orlando L [mailto:oralrnr@xxxxxxxxx]
Sent: Friday, March 04, 2016 6:14 AM
To: Deas, Scott <Scott.Deas@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: peoplesoft and partitioning
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<mailto: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>
[mailto: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<mailto: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.**
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.**
