Optimising Journal Line Queries: 3. Partitioning

This is the third of a series of five articles that examine the challenges posed by typical queries on the journal line table (PS_JRNL_LN).

1. Problem Statement
2. Exadata System Statistics
3. Partitioning
4. Compression
5. Conclusion

PeopleSoft does not partition tables by default.  Application Designer does not support partitioning, mainly because different databases implement partitioning differently.  Thus, it is always left to the customer to implement as a customisation.  In this article, I am only going to discuss partitioning on Oracle.

Interval Partitioning

This is the sample query that I started with.

SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, TO_CHAR(B.JOURNAL_DATE,'YYYY-MM-DD'), B.LEDGER, B.ACCOUNT
, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, SUM( B.MONETARY_AMOUNT), B.FOREIGN_CURRENCY
, SUM( B.FOREIGN_AMOUNT), A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, TO_CHAR(A.POSTED_DATE,'YYYY-MM-DD'), A.OPRID, A.DESCR254
, B.DEPTID, A.SOURCE, B.ALTACCT, TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LINE_DESCR 
FROM PS_JRNL_HEADER A, PS_JRNL_LN B 
WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.JOURNAL_ID = B.JOURNAL_ID 
AND A.JOURNAL_DATE = B.JOURNAL_DATE 
AND A.UNPOST_SEQ = B.UNPOST_SEQ 
AND A.JRNL_HDR_STATUS IN('P','V','U') 
AND A.FISCAL_YEAR IN (2024) 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 
AND B.CHARTFIELD1 IN ('1234567','1234568','1234569')
AND B.LEDGER IN ('LEDGER')) 
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.LEDGER, B.ACCOUNT, B.PRODUCT
, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, B.FOREIGN_CURRENCY, A.REVERSAL_CD, A.REVERSAL_ADJ_PER
, A.JRNL_HDR_STATUS, A.POSTED_DATE, A.OPRID, A.DESCR254, B.DEPTID, A.SOURCE, B.ALTACCT, A.DTTM_STAMP_SEC, B.LINE_DESCR

It would have been desirable to have been able to partition PS_JRNL_LN by FISCAL_YEAR.  However, that column is only present on the parent table, PS_JRNL_HEADER.  Oracle can do referential partitioning, where the child table is partitioned by an attribute of a column in the parent table.  The parent table must also be partitioned similarly, thus producing a 1:1 mapping of partitions between the parent and child tables.  However, this feature also requires the presence of an enforced foreign key constraint between parent and child tables.  

PeopleSoft has never implemented or supported database-enforced referential integrity constraints (again, mainly because it was done differently on different databases).  Although it is tempting to add a foreign key constraint between these tables, that would be a customisation to PeopleSoft that Oracle would not support.  The application would then have to insert parent rows before child rows and delete child rows before deleting parent rows.  It has never been tested against these constraints.  

Therefore, it is only possible to consider partitioning by a column on PS_JRNL_LN.  A column in the unique key is an obvious choice.

• Depending on how BUSINESS_UNIT is set up and used, you might be able to list sub-partition by this column, and split journal lines down into several subpartitions.  However, it is almost inevitable that the volumes will be heavily skewed.
• It is tempting to range partition on JOURNAL_ID.  Although this column usually contains an entirely numeric value, it is in fact defined as a character (VARCHAR2) data type.  Therefore, it is not possible to interval partition upon it.  Periodically, it would be necessary to add partitions manually.
• The alternative is to interval range partition on JOURNAL_DATE.  I chose to define a monthly interval.  I specified the first few partitions for whole years because at this customer, these partitions contained less data after archiving.  Thereafter, Oracle automatically creates monthly partitions as data is inserted.

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL 
,JOURNAL_DATE DATE NOT NULL 
,UNPOST_SEQ NUMBER(*,0) NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL 
,LEDGER VARCHAR2(10 CHAR) NOT NULL 
…
) PARTITION BY RANGE (JOURNAL_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
(PARTITION JRNL_LN_2016 VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2017 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2018 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')) 
) 
/

Partitioning on JOURNAL_DATE still arranges the journal line data into current and historical partitions. We find that the various financial processes only create journal lines in the current and previous fiscal years.  Therefore, earlier fiscal years are effectively static.  This presents an opportunity to compress these partitions because nothing will subsequently update them that would decompress compressed rows.  Thus, partitioning and compression go together.

Renaming Partitions

By default, interval partitions are given system-generated names.  I find it convenient to rename them to something more meaningful.  This has no bearing on performance.  In this case, I used something based on the date to which the partition relates.  There are 2 implicit cursors in the following PL/SQL block.  The first renames table partitions, and the second renames index partitions.  This script should be run periodically as new partitions are added. It is available on GitHub.

rem rename_jrnl_ln_partitions.sql    
rem requires https://github.com/davidkurtz/psscripts/blob/master/psftapi.sql
spool rename_jrnl_ln_partitions.lst
set serveroutput on
DECLARE
  l_high_value DATE;
  l_sql CLOB;
  l_new_partition_name VARCHAR2(30);
BEGIN
  psft_ddl_lock.set_ddl_permitted(TRUE);
  FOR i IN (
    select /*+LEADING(r upt upkc utc)*/ r.recname, upt.table_name, utp.partition_name, utp.high_value, upt.interval interval_size
    from sysadm.psrecdefn r 
      INNER JOIN user_part_tables upt ON upt.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) 
        AND upt.partitioning_type = 'RANGE' and upt.interval IS NOT NULL
      INNER JOIN user_part_key_columns upkc ON upkc.name = upt.table_name AND upkc.object_Type = 'TABLE' and upkc.column_position = 1
      INNER JOIN user_tab_columns utc ON utc.table_name = upkc.name AND utc.column_name = upkc.column_name
      INNER JOIN user_tab_partitions utp ON utp.table_name = upt.table_name AND utp.partition_name like 'SYS_P%'
    WHERE r.recname = 'JRNL_LN' AND r.rectype = 0
    AND (utc.data_type = 'DATE' OR utc.data_type like 'TIMESTAMP%')
  ) LOOP
    l_sql := 'SELECT '||i.high_value||'-'||i.interval_size||' FROM DUAL';
    EXECUTE IMMEDIATE l_sql INTO l_high_value;
    l_new_partition_name := i.recname||'_'||TO_CHAR(l_high_value,'YYYYMM');
    l_sql := 'ALTER TABLE '||i.table_name||' RENAME PARTITION '||i.partition_name||' TO '||l_new_partition_name;
    IF i.partition_name != l_new_partition_name THEN
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END IF;
  END LOOP;

  FOR i IN (
    select /*+LEADING(r upi upkc utc)*/ r.recname, upi.index_name, uip.partition_name, uip.high_value, upi.interval interval_size
    from sysadm.psrecdefn r 
      INNER JOIN user_part_indexes upi ON upi.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) 
        AND upi.partitioning_type = 'RANGE' and upi.interval IS NOT NULL
      INNER JOIN user_part_key_columns upkc ON upkc.name = upi.index_name AND upkc.object_Type = 'INDEX' and upkc.column_position = 1
      INNER JOIN user_tab_columns utc ON utc.table_name = upi.table_name AND utc.column_name = upkc.column_name
      INNER JOIN user_ind_partitions uip ON uip.index_name = upi.index_name 
        AND (uip.partition_name like 'SYS_P%' OR SUBSTR(uip.partition_name,1+LENGTH(r.recname),1) != SUBSTR(upi.index_name,3,1))
    WHERE r.recname = 'JRNL_LN' AND r.rectype = 0
    AND (utc.data_type = 'DATE' OR utc.data_type like 'TIMESTAMP%')
  ) LOOP
    l_sql := 'SELECT '||i.high_value||'-'||i.interval_size||' FROM DUAL';
    EXECUTE IMMEDIATE l_sql INTO l_high_value;
    l_new_partition_name := i.recname||SUBSTR(i.index_name,3,1)||TO_CHAR(l_high_value,'YYYYMM');
    l_sql := 'ALTER INDEX '||i.index_name||' RENAME PARTITION '||i.partition_name||' TO '||l_new_partition_name;
    IF i.partition_name != l_new_partition_name THEN
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END IF;
  END LOOP;
  psft_ddl_lock.set_ddl_permitted(FALSE);
END;
/
spool off

nVision Performance Tuning: 8. Interval Partitioning and Statistics Maintenance of Tree Selector 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.

The decision to use interval partitioning on the tree selector tables came from the need to have accurate statistics for the optimizer when parsing nVision queries.  It is not possible to introduce hints into nVision SQL. The dynamic nature of the code means it is not viable to consider any of the forms of database plan stability across the whole application, (although it may be possible to use SQL Profiles in limited cases). Therefore, as far as possible the optimizer has to choose the best plan on its own. Without accurate optimizer statistics, I have found that the optimizer will usually not choose to use a Bloom filter.
If the selector tables are not partitioned, then each table will usually contain rows for many different selectors. Even with perfectly up to date statistics, including a histogram on SELECTOR_NUM, and extended statistics on SELECTOR_NUM and RANGE_FROM_nn, I have found that Oracle miscosts the join on RANGE_FROMnn and the attribute on the ledger table.
I propose that the tree selector tables should be interval partition such that each selector goes into its own partition.

CREATE TABLE PSYPSTREESELECT10 
(SELECTOR_NUM INTEGER NOT NULL,
 TREE_NODE_NUM INTEGER NOT NULL,
 RANGE_FROM_10 VARCHAR2(10) NOT NULL,
 RANGE_TO_10   VARCHAR2(10) NOT NULL) 
PARTITION BY RANGE (selector_num) INTERVAL (1)
(PARTITION VALUES LESS THAN(2))
TABLESPACE "PTWORK"
STORAGE(INITIAL 128K NEXT 128K)
/
INSERT INTO PSYPSTREESELECT10 
(       SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_10, RANGE_TO_10)
SELECT  SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_10, RANGE_TO_10
FROM PSTREESELECT10
/
DROP TABLE PSTREESELECT10 
/
ALTER TABLE PSYPSTREESELECT10 RENAME TO PSTREESELECT10
/

• nVision queries will reference a single selector with a literal value, and therefore Oracle will eliminate all but that single partition at parse time and will use the statistics on that partition to determine how to join it to other tables.
• Statistics only have to be maintained at partition level, and not at table level. 
• Now that there is only a single selector number in any one partition, there is no need for extended statistics. 
• The need to use dynamic selectors, in order to get equality joins between selectors and ledger tables, in order to make use of the Bloom filter, means that statistics on selector table will inevitably be out of date. The PL/SQL triggers and package that log the selector usage, are also used to maintain statistics on the partition. 
• Partitions do not have to be created in advance. They will be created automatically by Oracle as they are required by the application. 

Compound Triggers on Tree Selector Tables 

There are a pair of compound DML triggers on each tree selector tables, one for insert and one for delete.

• The after row section captures the current selector number. The one for insert also counts the number of rows and tracks the minimum and maximum values of the RANGE_FROMnn and RANGE_TOnn columns. 
• The after statement section updates the selector log. The insert trigger directly updates the statistics on the partition, including the minimum and maximum values of the range columns.
• It is not possible to collect statistics in a trigger in the conventional manner because dbms_stats includes an implicit commit. If dbms_stats was called within an autonomous transaction it could not see the uncommitted insert into the tree selector that fired the trigger. Hence the trigger calls the XX_NVISION_SELECTORS package that uses dbms_stats.set_table_stats and dbms_stats.set_column_stats to set values directly. 
• The trigger then submits a job to database job scheduler that will collect statistics on the partition in the conventional way using dbms_job. The job number is recorded on the selector log. The job will be picked up by the scheduler when the insert commits. However, there can be a short lag between scheduling the job, and it running. The first query in the nVision report can be parsed before the statistics are available. 
• The procedure that directly sets the statistics has to make some sensible assumptions about the data. These mostly lead the optimizer to produce good execution plans. However, testing has shown that performance can be better with conventionally collected statistics. Therefore, the trigger both directly sets statistics and submits the database job to collect the statistics.
• It is important that table level statistics are not maintained by either technique as this would lead to locking between sessions. Locking during partition statistics maintenance will not occur as no two sessions populate the same selector number, and each selector is in a different partition. A table statistics preference for granularity is set to PARTITION on each partitioned tree selector table. 

The combination of dynamics selectors, single value joins, interval partitioning of selector tables, logging triggers on the selector tables driving timely statistics maintenance on the partitions delivers execution plans that perform well and that make effective use of engineered system features.

However, there are two problems that then have to be worked around. 

Library Cache Contention 

Some data warehouse systems can need new partitions in tables daily or even hourly. If partitions were not created in a timely fashion, the application would either break because the partition was missing, or performance would degrade as data accumulated in a single partition. Oracle intended interval partitions to free the DBA from the need to actively manage such partitioning on a day-to-day basis by creating them automatically as the data was inserted. 

However, on a busy nVision system, this solution could create thousands of new selectors in a single day, and therefore thousands of new partitions. This is certainly not how Oracle intended interval partitioning to be used.  I freely admit that I am abusing the feature.

If you have multiple concurrent nVision reports running, using dynamic selectors, you will have multiple database sessions concurrently inserting rows into the tree selector tables each with a different selector number, and therefore each creating new partitions mostly into the same tables.

The recursive code that creates the new partitions, and maintains the data dictionary, acquires a lock the object handle in library cache to prevent other sessions from changing it at the same time.  As the number of concurrent nVisions increase you will start to see nVision sessions waiting on the library cache lock event during the insert into the tree selector table while the new partition is being created. Perversely, as the performance of the nVision queries improve (as you refine tree selector settings) you may find this contention increases. 

The workaround to this is to create multiple database schemas, each with copies of the partitioned tree selector tables (similarly interval partitioned) and the PSTREESELCTL table (to manage static selectors in those schemas). Synonyms will be required for all other tables referenced by nVision queries. 

Then a trigger on the process scheduler request table PSPRCSRQST will arbitarily set the current schema of the nVision batch processes to one of those schemas. The nVision reports still connect and run with privileges of the Owner ID (usually SYSADM), but the objects tables from the current schema. 

I have used a hash function to distribute nVision processes between schemas. I suggest the number of schemas should be a power of 2 (ie, 2, 4, 8 etc.).

CREATE OR REPLACE TRIGGER sysadm.nvision_current_schema
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.runstatus IN('7') AND new.prcsname = 'RPTBOOK' AND new.prcstype like 'nVision%')
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema = NVEXEC'||LTRIM(TO_CHAR(dbms_utility.get_hash_value(:new.prcsinstance,1,8),'00'));
EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/

Thus different nVision reports use different tree selector tables in different schemas rather than trying to create partitions in the same tree selector table, thus avoiding the library cache locking.

Limitation on the Maximum Number of Partitions 

In Oracle, it is not possible to have more than 1048576 partitions in a table. That applies to all forms of partitioning.
The tree selector tables are interval partitioned on selector number with an interval of 1 starting with 1. So the highest value of SELECTOR_NUM that they can store is 1048575.

INSERT INTO pstreeselect05 VALUES(1048576,0,' ',' ')
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

New selector numbers are allocated sequentially from PSTREESELNUM. Left unattended, the selector numbers used by nVision will increase until they eventually hit this limit, and then nVision and ad-hoc queries that use the tree-exists operator will start to fail with this error.
Therefore, a procedure RESET_SELECTOR_NUM has been added to the PL/SQL package to reset the selector number allocation table PSTREESELNUM back to 0, delete any tree selector entries for which there is no logging entry, and then runs the regular selector PURGE procedure in the same
package that will drop unwanted interval partitions.

Recommendation: XX_NVISION_SELECTORS.RESET_SELECTOR_NUM should be scheduled run sufficiently frequently to prevent the selector number reaching the maximum.