nVision Performance Tuning: 4. Partitioning of Ledger, Ledger Budget, and Summary Ledger Tables

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Note: Partitioning is a licenced option in Oracle RDBMS, and is only available on Enterprise Edition.

nVision queries always contain single value predicates on LEDGER and FISCAL_YEAR.  They will also always have either single value predicate or a range predicate on ACCOUNTING_PERIOD.  Therefore, partitioning the ledger tables on these columns is an effective way to cut down the data to be processed by the query as early as possible.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT … SUM(A.POSTED_BASE_AMT) 
FROM PS_LEDGER A, …
WHERE A.LEDGER='ACTUALS' 
AND A.FISCAL_YEAR=2015 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 11 
…

I usually partition the ledger, ledger budget and summary ledger tables on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD in a single range.
Most customers have monthly accounting periods, in which case I create 14 partitions for the current and previous fiscal years, but only have a single partition for each previous fiscal years.

• One for each of the 12 accounting periods
• One for period 0 (brought forward)
• One for periods 998 and 999 (carry forward and adjustments)

I have seen one system with daily accounting periods that also had 14 partitions per year, in groups of 30 days.  This also worked very well.
I would then consider sub-partitioning on another column depending on the nature of data and the processing.  For example:

• LEDGER is often a good candidate.  In which case, I would create one list sub-partition for each of the larger ledgers and have a default list partition for the rest.
• On one a global system with various regional, but locally overnight, batch processing windows ledger was list sub-partitioned on BUSINESS_UNIT. A list partition was proposed for each region containing the top business units for that region.  This not only helped regional reporting but also minimised inter-regional contention.
• It would even be possible to vary the sub-partitioning in different fiscal years if a change occurred in the business.

This example shows part of the DDL used to create the LEDGER table.

• There is one range partition for the whole of the fiscal year 2015 because it is historical and rarely queried, and then not usually by a single period.
• Monthly partitioning is used from the previous fiscal year, 2016, onwards.
• Historical partitions are created without any reservation for free space as they are closed and won't be updated any further.  The could also be compressed.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE TABLE sysadm.gfc_ledger
(...)
TABLESPACE GLLARGE
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(FISCAL_YEAR,ACCOUNTING_PERIOD)
SUBPARTITION BY LIST (LEDGER)
(PARTITION ledger_2014 VALUES LESS THAN (2015,0) PCTFREE 0 …
(SUBPARTITION ledger_2014_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2014_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015_bf VALUES LESS THAN (2015,1) PCTFREE 0…
(SUBPARTITION ledger_2015_bf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_bf_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015_01 VALUES LESS THAN (2015,2) PCTFREE 0 …
(SUBPARTITION ledger_2015_01_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_01_z_others VALUES (DEFAULT)
)
…
,PARTITION ledger_2015_12 VALUES LESS THAN (2015,13) PCTFREE 0 …
(SUBPARTITION ledger_2015_12_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_12_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015_cf VALUES LESS THAN (2016,0) PCTFREE 0 …
(SUBPARTITION ledger_2015_cf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_cf_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2016_bf VALUES LESS THAN (2016,1) PCTFREE 0 …
(SUBPARTITION ledger_2016_bf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2016_bf_z_others VALUES (DEFAULT)
)
…
)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/

I usually recommend locally partitioning all indexes. 
The unique index on PS_LEDGER is perhaps an exception.  It can be locally partitioned because FISCAL_YEAR and ACCOUNTING_PERIOD are the last two of 25 columns in the index.  However, the OLTP processes that query the ledger table frequently do not use FISCAL_YEAR but the leading columns.  Therefore, every such query would have to probe every partition.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE UNIQUE INDEX sysadm.ps_ledger_new ON sysadm.ps_ledger
(business_unit,ledger,account,altacct,deptid
,operating_unit,product,fund_code,class_fld,program_code
,budget_ref,affiliate,affiliate_intra1,affiliate_intra2,chartfield1
,chartfield2,chartfield3,project_id,book_code,gl_adjust_type
,date_code,currency_cd,statistics_code,fiscal_year,accounting_period
) LOCAL
(PARTITION ledger_2014 PCTFREE 0
(SUBPARTITION ledger_2014_actuals
…
,SUBPARTITION ledger_2014_z_others
)
…
)
TABLESPACE GLLEDGER_IDX
PCTFREE 5 COMPRESS 3
PARALLEL 
/
ALTER INDEX ps_ledger NOPARALLEL
/

Maintenance Tasks

The introduction of range partitioning on FISCAL_YEAR brings some regular maintenance tasks.

• New partitions must be added to the ledger and summary ledger tables for each new fiscal year before it is opened and transactions posted to it.  The LEDGER_BUDG table can be similarly partitioned and partitions should be added before budget entries are made.
• I deliberately do not create MAXVALUE partitions on ledger tables.  If the application attempted to post data to an accounting period for which new partitions had not been created it would experience an Oracle error.  Therefore it is essential to remember to add the partitions in advance. I think this is preferable to forgetting to add the partitions and having performance degrade as data accumulates in the MAXVALUE partition.
• As periods close and the application ceases to insert or update rows, their partitions can be compressed using

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE … PARTITION … COMPRESS UPDATE ALL INDEXES

• As and when old fiscal years are no longer needed they can be archived by simply dropping the partitions, or exchanging them out to another table.