Oracle partitioning for Peoplesoft Financials -- JApplewhite 2006-06-07
Bob,
We're in the process of partitioning
huge tables in the 3rd Party Financials system (IFAS) that AISD uses. It's
not Peoplesoft, but I think the approach would be the same. We did
get approval from the app's vendor (SunGard Bi-Tech) that partitioning
wouldn't invalidate our support agreement.
Of course you start with the biggest
tables that cause performance degradation as they grow beyond the application's
scalability. We then ask the users what processes or reports they
run against those tables that are slow and look at the SQL to determine
likely candidate partition keys. We then partition the table in our
Dev database and have the users test their reports. We find that
partitioning can work better than an index on the same column(s). The
Optimizer really pays attention to opportunities for Partiton Pruning.
It looks like Range Partitioning - by
month - on a date column in each table gives us the best performance improvement.
We hope to be able to eventually put old partitions in read only
tablespaces, which will reduce the time and space required for our nightly
backups. Compression of those little-used partitions and their local
index segments should help, too.
Since some of the app's utilities check
for the existence of specifically-named tables and indexes, we keep the
same names, but make all indexes Local, which helps tremendously for Inserts
and Deletes in the current "hot" fiscal periods. Making
unique indexes local means we sometimes have to add the partition key column
to the unique index, but, overall, the extra space required is not significant.
Hope this helps.
Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk) / 512.935.5929 (pager)
I'm OK, you're OK - in small doses. -- Introverts'
Motto
Bob Robert <mssql_2002@yahoo.com> Sent by: oracle-l-bounce@freelists.org
06/07/2006 09:07 AM
Please respond to
mssql_2002@yahoo.com
To
oracle-l@freelists.org
cc
Subject
Oracle partitioning for Peoplesoft Financials
Gurus,
Did any one implement oracle 9i table partitioning in Peoplesoft
Financials 8.8?
I have identified few tables for partitioning. They are PS_LEDGER,
PS_JRNL_LN, PS_JRNL_HEADER and PS_LEDGER_BUDG. I am planning to use
list partition by using business_unit or range partition by using
fiscal_year.
Could you please share your experience for the following questions?
1) What are the tables you have implemented table partitioning?
2) What is the partition key and partition type?
3) Did you build any additional indexes?
4) Did you come across any issues after implementation?
5) Any tips, you could suggest for me.
Thanks,
Bob
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
