Saturday, October 28, 2017

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.
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.
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.
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
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.

Managing Partitioning in PeopleSoft

Application Designer is not good at managing partitioning.  I have written previously about the limited support for partitioning introduced in PeopleTools 8.54.  It uses Oracle's DBMS_METADATA package to preserve existing settings, including partitioning, but the support for initially implementing partitioning is poor.
It may be reasonable to manually manage partitioning on a single table, but if you also have a number of summary ledgers, and perhaps have also built materialized views on them, you can have a significant number of partitioned objects to manage.  Manual scripting is going to become a significant overhead, particularly as you add new partitions for new fiscal years.  You might want to look at Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART package.

Wednesday, October 25, 2017

nVision Performance Tuning: 3. Indexing of Ledger, Budget, and Summary Ledger Tables on Non-Engineered Oracle Systems

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

If you are on a conventional non-engineered Oracle system, then it is not going to be viable to full scan the ledger tables for every query.  You are going to have to use indexes to find your data.  Every customer's nVision reports are unique to that customer and the nature of their business.  Different customers will analyse their data by different combinations of attributes.  Indexes will be needed to match those analysis criteria.
A survey of the nVision reports and the SQL they produce will reveal the various sets of columns by which ledger data is queried. The heaviest SQL statements can be identified from Active Session History (ASH) or the Automatic Workload Repository (AWR) (or Statspack if the Diagnostics pack is not licenced).   As an example, let's take the following two statements as examples, and consider the indexes that would be needed to support them.
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_LEDGER A, PSTREESELECT10 L4, PSTREESELECT10 L2, PSTREESELECT10 L 
WHERE A.LEDGER='ACTUALS' 
AND A.FISCAL_YEAR=2017 
AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=78948 
AND A.CHARTFIELD3>= L4.RANGE_FROM_10 AND A.CHARTFIELD3 <= L4.RANGE_TO_10 
AND L4.TREE_NODE_NUM BETWEEN 1000000000 AND 2000000000 
AND L2.SELECTOR_NUM=92481 AND A.ACCOUNT=L2.RANGE_FROM_10
AND (A.DEPTID BETWEEN '10000' AND '18999' 
…
OR A.DEPTID='29150' 
OR A.DEPTID=' ') 
AND L.SELECTOR_NUM=92469 AND A.CHARTFIELD1=L.RANGE_FROM_10 
AND A.CURRENCY_CD='USD' 
GROUP BY L4.TREE_NODE_NUM
  • nVision queries will always contain single value predicates on LEDGER and FISCAL_YEAR.
  • You may see some queries on a single ACCOUNTING_PERIOD, as in the query above, usually the current period.  Otherwise, they might be on a range of ACCOUNTING_PERIODs, usually the year to date as in the example below.
  • Then there will predicates on the other analysis columns, in this case, DEPTID, ACCOUNT, CHARTFIELD1, CHARTFIELD3.
  • You may see criteria on CURRENCY_CD, however, this may not be very selective.  Generally, most of the queries will be on the base currency, and most of the data will be in the base currency.
  • Sometimes you may see a criterion on STATISTICS_CODE=' '.  Normally, only a very few ledger rows have statistics code, so there is no benefit in adding STATISTICS_ CODE to any index.
  • Generally, single value predicate columns should be placed at the front of the index, followed by the other analysis criteria.
  • However, I would not attempt to overload the index with additional non-selective columns because unless you index all the referenced columns, including the amount columns, you will not be able to satisfy the query from the index only, and you will still have to visit the table.
  • So for this query, you might build an index on the following columns: LEDGER, FISCAL_YEAR, ACCOUNTING_PERIOD, DEPTID, ACCOUNT, CHARTFIELD1, CHARTFIELD3, CURRENCY_CD
This query, from the same system, is on a different combination of columns
SELECT L2.TREE_NODE_NUM,L3.TREE_NODE_NUM,L4.TREE_NODE_NUM,SUM(A.POSTED_BASE_AMT) 
FROM PS_LEDGER A, PSTREESELECT05 L1, PSTREESELECT10, L2, PSTREESELECT10 L3, PSTREESELECT10 L4, PSTREESELECT10 L 
WHERE A.LEDGER='ACTUALS' AND A.FISCAL_YEAR=2015 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 11 
AND L1.SELECTOR_NUM=30369 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05 
AND L2.SELECTOR_NUM=30374 AND A.CHARTFIELD1=L2.RANGE_FROM_10 
AND L3.SELECTOR_NUM=30375 AND A.ACCOUNT=L3.RANGE_FROM_10 
AND L4.SELECTOR_NUM=30376 AND A.CHARTFIELD2=L4.RANGE_FROM_10 
AND L.SELECTOR_NUM=30372 AND A.CHARTFIELD1=L.RANGE_FROM_10 
AND A.CURRENCY_CD='USD' 
GROUP BY L2.TREE_NODE_NUM,L3.TREE_NODE_NUM,L4.TREE_NODE_NUM
  • In this case, the above query is part of a fiscal year-to-date report, returning the same period in a previous fiscal year.  The system is currently in period 11, so this report is looking at accounting periods 1 to 11.  ACCOUNTING_PERIOD is a much less selective criterion in this case.  If partitioning option were not available, I might be tempted to demote ACCOUNTING_PERIOD to further down the list of indexed columns if the criterion on the next column is more selective.
  • This query uses 5 trees.  A cartesian join of all 5 is likely to produce a product so large that it will not be effective to do index lookups for each member of the product.  The columns with the most selective criteria should be earlier in the index.  It might not even be worth including columns with non-selective criteria in the index.
  • So you might build an index on the following columns: LEDGER, FISCAL_YEAR,  BUSINESS_UNIT, ACCOUNT, CHARTFIELD1, CHARTFIELD2, CURRENCY_CD, ACCOUNTING_PERIOD.

Recommendations

It is easy to see how you might build up a large number of indexes on the ledger and summary ledger tables.  You might have pairs of indexes, one for single accounting periods and perhaps another similar one for year-to-date for each set of analysis criteria.
However, as you add indexes the overhead of index maintenance experienced by any processes that post to the ledger, or by the summary ledger build process will increase.  There is a balance to be struck. You will have to build as many indexes with as many columns as are necessary, but as few as possible.
If you partition the tables on FISCAL_YEAR and ACCOUNTING_PERIOD and locally partition the indexes, you will only need one of the indexes.
Index leaf block dictionary compression can significantly reduce the size of the index.  Any increase in the CPU overhead of using and maintaining the index will be more than offset by the reduction in I/O overhead.  The optimal prefix length can be calculated using the command ANALYZE INDEX … VALIDATE STRUCTURE.  This feature does not require the Advanced Compression licence.

Saturday, October 21, 2017

nVision Performance Tuning: 2. Effects of Performance Options

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

In this post, I examine the effect of different settings for the tree performance options on how the SQL is generated.  It is common, to see different performance options in use on different trees in the same SQL query.  It is important to be able to look at a piece of SQL generated by nVision and to be able to work out which performance options have been set on which trees.
  • Access Method: join to tree selector –v- use literal values
  • Tree Selectors: statics –v- dynamic
  • Selector Options: single values –v- inequalities/between

Access Method

This option allows you control how the tree selector table is combined with the ledger table.  The choices of interest are whether to join the tree selector, or whether to use literal values where feasible.

Join

Here, two tree selectors are joined to the ledger table
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A, PSTREESELECT10 L4, … PSTREESELECT10 L 
WHERE A.LEDGER='ACTUALS'
AND A.FISCAL_YEAR=2017 
…
AND L.SELECTOR_NUM=73130 
AND A.CHARTFIELD1=L.RANGE_FROM_10
AND A.CURRENCY_CD='GBP'
GROUP BY L4.TREE_NODE_NUM
It is not feasible to use literal values for L4 because the data is grouped by L4.TREE_NODE, however, it is feasible to use literal values for L because no column from this table is included in either the SELECT or GROUP BY clauses.

Literal Values

When 'use literal values' is selected nVision replaces the join to the tree selector table with a set of literal values generated from the contents of the tree selector table.
In this example, the join to the CHARTFIELD1 tree selector has been replaced with a series of literal value predicates.  There is one for every selected tree leaf.  A simple equality condition for single value leaves, and a BETWEEN condition for ranged leaves.
SELECT L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A, PSTREESELECT10 L2
WHERE A.LEDGER='ACTUALS'
AND A.FISCAL_YEAR=2017
AND A.ACCOUNTING_PERIOD=1
…
AND L2.SELECTOR_NUM=101142
AND A.ACCOUNT=L2.RANGE_FROM_10
AND (A.CHARTFIELD1='0070700'
OR A.CHARTFIELD1='0078999'
…
OR A.CHARTFIELD1 BETWEEN '0091100' AND '0091101'
OR A.CHARTFIELD1='0150204')
AND A.CURRENCY_CD='GBP'
GROUP BY L2.TREE_NODE_NUM

Recommendations

In general, the literal values option is very beneficial.
  • It simplifies the queries generated by nVision by removing a table from the from clause and leaves the optimizer with fewer choices when determining the execution plan.
  • Data is filtered on the scan of the ledger table rather than when it is joined to the tree selector.  On an engineered system these predicates can be pushed down to the storage cells.
However, there are some disadvantages
  • As the number of leaves on a tree increase, so the number of literal predicates in the query increases, and the time taken to parse the SQL increases.  As a rough guideline, I would say that the parse time starts to become significant as the tree exceeds 2000 leaves, and you might be better using the Join Option.
  • Also, as the number of literal predicates increase the time taken to evaluate them increases.  This applies to both conventional and engineered systems.
  • Some PeopleSoft customers have found it generally beneficial to the system to raise the value of OPTIMIZER_DYNAMIC_SAMPLING from the default of 2 to 4 so that Oracle collects dynamic statistics if there are expressions in where clause predicates or multiple predicates on the same table (see Database SQL Tuning Guide -> Managing Optimizer Statistics).  However, this can significantly increase the parse overhead of nVision SQL using many predicates.  If necessary, this parameter can be reset at session level for nVision with a trigger.
  • The nVision client also has to do more work to generate the SQL.
  • Literal values increase the variability of SQL generated, reports using different tree nodes will generate SQL with different numbers of predicates and there is effectively no chance that any two statements would be similar enough to use any of Oracle's plan stability technologies (SQL baselines, SQL profiles, SQL plan directives).
  • In theory, cursor sharing could reduce the parse, but in my experience is that the optimizer does not correctly cost the predicates resulting in different execution plans with poorer performance.

Tree Selectors

This option allows you to choose whether a tree selector is managed in a static or dynamic manner.  As you will see, the Tree Selectors performance option is tied in with the Selector Options performance option.

Static

Where a tree uses a static selector, the entire tree is extracted into the tree selector.  There will be no tree node number criteria in the extract statement.
INSERT INTO PSTREESELECT10
(SELECTOR_NUM,TREE_NODE_NUM,RANGE_FROM_10,RANGE_TO_10) 
SELECT 78722,L.TREE_NODE_NUM, SUBSTR(L.RANGE_FROM,1,10),SUBSTR(L.RANGE_TO,1,10)
FROM PSTREELEAF L 
WHERE L.SETID='GLOBE' 
AND L.SETCNTRLVALUE=' ' 
AND L.TREE_NAME='FUNCTION' 
AND L.EFFDT=TO_DATE('1901-01-01','YYYY-MM-DD')
Note that the tree leaf was not joined to the underlying detail table.  Ranged leaves become ranged selectors.  This is because there is no mechanism to determine when the detail table has changed and thus when the static selector would be out of date and need to be refreshed.  This is why single value joins cannot be performed with static selectors.
Tree node number criteria will appear in the nVision queries to identify the required selectors.
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_LEDGER A, PSTREESELECT10 L4, PSTREESELECT10 L2, PSTREESELECT10 L 
WHERE A.LEDGER='ACTUALS' 
AND A.FISCAL_YEAR=2017 
AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=89052 
AND A.CHARTFIELD3>= L4.RANGE_FROM_10 
AND A.CHARTFIELD3 <= L4.RANGE_TO_10 
AND L4.TREE_NODE_NUM BETWEEN 1000000000 AND 2000000000
A control row is maintained on the table PSTREESELCTL.  This row indicates that a particular selector is static, and the version number is compared to the version number of PSTREEDEFN to determine whether the selector is valid, or whether the tree has changed.  If the tree has changed it will have a higher version number, and the selector will be extracted again.  The selector number from the query can be looked up on this table to identify the tree.

Dynamic

Dynamic selectors are built on-the-fly by nVision as the report runs.  They select just the rows from the tree that are required by the report.
INSERT INTO PSTREESELECT10
(SELECTOR_NUM,TREE_NODE_NUM,RANGE_FROM_10,RANGE_TO_10) 
SELECT DISTINCT 108090,L.TREE_NODE_NUM,D.ACCOUNT,D.ACCOUNT FROM
PS_GL_ACCOUNT_TBL D, PSTREELEAF L 
WHERE L.SETID='GLOBE' AND L.SETCNTRLVALUE=' ' 
AND L.TREE_NAME='ACCOUNT' 
AND L.EFFDT=TO_DATE('1901-01-01','YYYY-MM-DD') 
AND L.RANGE_FROM<>L.RANGE_TO 
AND D.ACCOUNT BETWEEN L.RANGE_FROM AND L.RANGE_TO 
AND D.SETID='GLOBE' AND
(L.TREE_NODE_NUM BETWEEN 789473386 AND 793372019 
OR L.TREE_NODE_NUM BETWEEN 810915873 AND 812865189 
…
OR L.TREE_NODE_NUM BETWEEN 1089668204 AND 1095516154 
OR L.TREE_NODE_NUM BETWEEN 1113060008 AND 1115009324)
All the tree node number predicates are found in the query that populates the selector table and not in the query that generates the report.
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_LEDGER A, PSTREESELECT10 L4, PSTREESELECT10 L2, PSTREESELECT10 L 
WHERE A.LEDGER='S_USMGT' 
AND A.FISCAL_YEAR=2017 
AND A.ACCOUNTING_PERIOD=1 
…
AND L.SELECTOR_NUM=96774 
AND A.CHARTFIELD1=L.RANGE_FROM_10 AND
A.CURRENCY_CD='USD' GROUP BY L4.TREE_NODE_NUM
nVision will delete dynamic selectors after they have been used.  However, if the report crashes or times out, rows will be left in the selector table, and this debris can build up over time.  So there is a requirement to manage these tables.
Note that in this example when the selector was extracted, the tree leaf table was joined to the underlying table on which the tree was based (in this case GL_ACCOUNT_TBL).  This occurs when the single value join option is selected.  The tree selector contains a row for each value rather than for each tree leaf.  This will result in larger tree selectors where ranged leaves are used.
PeopleSoft do not provide any way to identify the tree used to create a dynamic selector.  From the SQL query, all we can see is the field to which it was joined.  The above example is a tree related to CHARTFIELD1.

Recommendations

Although static selectors are easier to manage, the decision to use dynamic selectors is driven by the need to use single value joins.
  • It is important to maintain up-to-date statistics on the selector tables.  As new selectors are extracted, a series of ever-increasing selector numbers are allocated from a sequence maintained on the table PSTREESELNUM.   If not maintained, the selector numbers will exceed the high value recorded in the column statistics for column SELECTOR_NUM, and the database will not correctly calculate the number of rows expected, resulting in inappropriate execution plans.
  • It is easier to manage optimizer statistics on static selectors because the PSTREESELCTL table records when they have been updated.  In fact, it is possible to put a trigger on that table to maintain statistics.  However, static selectors imply range predicates that bring other problems that I discuss in the next section.
  • Using compound triggers on the selector tables it is possible to maintain a log of selectors, maintain statistics on them and purge selectors left behind by nVision reports that have not cleaned up after themselves.  This is also used to identify the trees used in dynamic selectors.  I will describe this solution later.
Recommendation: Prior to Oracle 11g, I have recommended using static selectors and ranged joins, and by careful management of statistics on the tree selector and indexing of the ledger tables good results can be obtained.  The tree selectors are Cartesian joined together before using index lookups on the ledger tables.
However, this approach is generally not effective for queries that reference more than 3 tree selector, nor for very large trees.  The size of the Cartesian product is the product of the number of rows extracted from each tree selectors table.  For example, three selectors with 100 rows each will result in 1 million lookups on the ledger table.  As the size of the Cartesian product grows the number of index look-ups also grows.  There comes a point when it is better to remove the least selective tree from the Cartesian and hash joining it after the looking up ledger.  Striking the correct balance can be difficult, and leads to using different performance options in different reports.
From Oracle 11g, and especially on engineered systems, I now prefer dynamic selectors because they permit the use of single value joins that can use Bloom filters and Extended Statistics.  Although, where smaller trees are in use that do not significantly cut down the query result set, range joins may still be preferable, in which cases, so might static selectors.

Selector Options

This performance option controls how the tree selector is joined to the ledger table.  The choice is between single value equality joins or inequality joins.

Ranged Joins

There are two forms of ranged joins, either using the BETWEEN keyword or using a pair of inequalities.  There is no difference to Oracle because the optimizer rewrites BETWEEN as a pair of inequalities.
NB: If you specify single value joins on a static selector you still get an inequality ranged join because only the tree leaves are extracted to the selector.  .  nVision still does this even if all of the leaves in the tree are single values and not ranges.
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_LEDGER A, PSTREESELECT10 L4, PSTREESELECT10 L2, PSTREESELECT10 L 
WHERE A.LEDGER='ACTUALS' 
AND A.FISCAL_YEAR=2017 
AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=89052 
AND A.CHARTFIELD3>= L4.RANGE_FROM_10 
AND A.CHARTFIELD3 <= L4.RANGE_TO_10 
…
AND A.CURRENCY_CD='GBP' 
GROUP BY L4.TREE_NODE_NUM

Single Value Joins

If single value joins are selected, nVision generates an equality join between the tree selector and the ledger table.  The tree selector must be dynamic, and nVision extracts all the intermediate values between the tree node ranges on the underlying detail table.
SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_LEDGER A, PSTREESELECT10 L4, PSTREESELECT10 L2, PSTREESELECT10 L 
WHERE A.LEDGER='ACTUALS' 
AND A.FISCAL_YEAR=2017 
AND A.ACCOUNTING_PERIOD=1 
…
AND L.SELECTOR_NUM=96774 
AND A.CHARTFIELD1=L.RANGE_FROM_10 
AND A.CURRENCY_CD='GBP' 
GROUP BY L4.TREE_NODE_NUM

Recommendations

In general, I would recommend using single value joins and therefore also dynamic selectors.  The main advantage is that they enable two particular optimisations in Oracle.
  • Bloom filters only work with equality predicates, and therefore only with single value joins.  They enable a filter on one column of one table to be roughly mapped to another column of another table upon which there is an equality join.  This filters data earlier in a query, cutting down the size of the eventual join operation.  It is a rough match in that it may return some false positives to the subsequent hash operation, but it will not omit any results.  The hash join then does an exact match of the rows to produce the correct result set.  On an Engineered Oracle system this filter can also be pushed down to the storage cells. 
  • This is an example of the kind of execution plan that we should see when a Bloom filter is used.  A filter has been created on PSTREESELECT10 at line 3, and it is applied to PS_LEDGER at line 6.
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                    |       |       | 12876 (100)|          |       |       |
|   1 |  HASH GROUP BY                           |                    |   501 | 35571 | 12876  (42)| 00:00:01 |       |       |
|   2 |   HASH JOIN                              |                    |   975K|    66M| 12822  (41)| 00:00:01 |       |       |
|   3 |    JOIN FILTER CREATE                    | :BF0000            |  2577 | 59271 |     7   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE SINGLE               |                    |  2577 | 59271 |     7   (0)| 00:00:01 | 87968 | 87968 |
|   5 |      INDEX FAST FULL SCAN                | PS_PSTREESELECT10  |  2577 | 59271 |     7   (0)| 00:00:01 | 87968 | 87968 |
|   6 |    JOIN FILTER USE                       | :BF0000            |  1715K|    78M| 12804  (41)| 00:00:01 |       |       |
|   7 |     PARTITION RANGE ITERATOR             |                    |  1715K|    78M| 12804  (41)| 00:00:01 |    27 |    34 |
|   8 |      MAT_VIEW REWRITE ACCESS STORAGE FULL| PS_LEDGER          |  1715K|    78M| 12804  (41)| 00:00:01 |    27 |    34 |
-------------------------------------------------------------------------------------------------------------------------------
  • Extended statistics, (i.e. on groups of columns) also only work with equality predicates.  Single value join queries can, therefore, benefit from them.
There are drawbacks to be overcome.
  • Single value joins require the use dynamic selectors.  That requires management of statistics on the dynamic selectors, and debris left in the tree selectors table from failed reports that have not cleaned up after themselves.
  • Single value joins can result in larger tree selector tables with many more rows than tree leaves.  For large trees or trees with many values in leaf ranges, resulting in more than about 2000 rows on the tree selector tables, it can be advantageous to change the Access Method performance option from using literal values to join because the parse overhead becomes significant.

Monday, October 16, 2017

nVision Performance Tuning: 1. nVision Performance Options

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

At no point when defining a nVision report does the developer directly specify a SQL statement, all the SQL is generated by nVision from the report definition.  That also means that it is not possible to directly intervene and change the SQL statement, for example, to add an optimizer hint.

However, the way that the SQL is generated can be controlled via the nVision performance options. Setting this appropriately can make a significant difference to nVision performance, but the optimal settings will differ from system to system, from tree to tree and sometimes even report to report.

Specification of Performance Options

nVision performance options are specified for named trees.  Since PeopleTools 8, they are defined in the tree definition.
Tree Performance Options in PIA
They are stored in the database in PeopleTools table PSTREEDEFN.
Field Name
Field Value
Description
TREE_ACC_METHOD
D
Use Application Defaults
J
Join to Tree Selector
L
Use Literal Values
S
Sub-SELECT Tree Selector
TREE_ACC_SELECTOR
D
Dynamic Selectors
S
Static Selectors
TREE_ACC_SEL_OPT
B
Range of Values (BETWEEN)
R
Range of Values (>=…<=)
S
Single Values

Performance options can also be specified in individual layouts, and the specific definition in the layout overrides the generic definition in the tree.
Add-Ins->nVision->Layout Options
You can also see the trees defined in a layout file in the Name Manager in Excel.  The performance options are converted into a string of 5 characters.  Below you can see a tree called OPERATING_UNIT. The easiest way to determine whether performance options are set in a layout is to look in the Name Manager, and the only reliable way to delete a performance option from a layout is to delete them from the Name Manager.
Formulas -> Name Manager

The string of flags in the NvsTree formula can be decoded with this table.
X
Value
Position
Y
N
S
1
Dynamic Selector
Static Selector

2
Join to tree selector
Suppress join; use literal values if feasible
Sub-SELECT tree selector
3
Range of values (BETWEEN)
Range of values (>= … <=)

4
Single Value
(overrides position 3)


5
Non-specific node criteria (above 2 billion)


Recommendations

It is rare to need to set different performance options for the same tree in different layouts. Therefore, it is easiest to set performance options at tree level and not at all in layouts unless absolutely necessary.
Tree performance options can even be updated in bulk by SQL script, though it is essential to maintain the versions numbers correctly.  Care should be taken when migrating trees from other environments to ensure that the performance options are still correctly set, as the performance options are part of the tree being migrated
It is now possible to give clear advice to developers and especially business users who create nVision reports:
  • Don't use them.
  • If you have specified them in layouts in the past, then remove them unless you really do need to use a different option in a particular report.
Some customers have written Visual Basic macros to work through all their layouts and remove all tree performance options settings.

In the next post I will start to look at how the performance options control how the SQL generated by nVision.

Tuesday, October 10, 2017

nVision Performance Tuning: Introduction

This blog post is the first in a series that discusses how to get good performance from nVision as used in General Ledger reporting.

PS/nVision is a PeopleTools technology that extracts data from the database and places it in an Excel spreadsheet (see PS/nVision Overview).  Although PS/nVision can be used with any PeopleSoft product, it is most commonly used in Financials General Ledger.

The SQL queries generated by nVision are, at least conceptually, similar to data warehouse queries. The ledger, ledger budget or summary ledger tables are the fact tables.

The ledger tables are analysed by their attribute columns. There are always literal conditions on the fiscal year and accounting period, there is usually a literal condition on currency code.  Then there are criteria on some of the other attributes.  I will take an example that analyses the ledger table in three dimensions: BUSINESS_UNIT, ACCOUNT and CHARTFIELD1, but there are many other attribute columns on the ledger tables.  These attributes are defined in lookup tables in the application, but their hierarchies are defined in trees.

nVision reports use the trees to determine which attribute values to report.  A report might report on a whole tree, or particular nodes, or branches of a tree.  nVision joins the tree definition to the attribute table and produces a list of attributes to be reported.  These are put into working storage tree selector tables (PSTREESELECT01 to 30).  The choice of selector table is controlled by the length of the attribute column.  BUSINESS_UNIT is a 5 character column so it goes into PSTREESELECT05. CHARTFIELD1 and ACCOUNT are 10 character columns so they use PSTREESELECT10.  These selector tables form the dimensions in the queries.

Here is an example of a SQL statement generated by nVision.  The tree selector 'dimension' tables are joined to the ledger 'fact' table.

SELECT L.TREE_NODE_NUM,L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM   PS_LEDGER A
,      PSTREESELECT05 L1
,      PSTREESELECT10 L
,      PSTREESELECT10 L2
WHERE  A.LEDGER='ACTUALS'
AND    A.FISCAL_YEAR=2016
AND    A.ACCOUNTING_PERIOD BETWEEN 1 AND 11
AND    L1.SELECTOR_NUM=30982 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05
AND    L.SELECTOR_NUM=30985 AND A.CHARTFIELD1=L.RANGE_FROM_10
AND    L2.SELECTOR_NUM=30984 AND A.ACCOUNT=L2.RANGE_FROM_10
AND    A.CURRENCY_CD='GBP'
GROUP BY L.TREE_NODE_NUM,L2.TREE_NODE_NUM
This SQL looks simple enough, but there are various complexities
  • The tree selector tables are populated at runtime.  Many dimensions can be stored in each tree selector table, each keyed by a different SELECTOR_NUM.
  • Selectors can be static or dynamic.  In dynamic selectors, the data is only stored temporarily for the lifetime of the report and will be deleted when it completes.  So immediately, there is a challenge of keeping statistics up to date, and even then Oracle doesn't always manage to find an effective execution plan.
  • Different selectors will have different numbers of rows, so the statistics have to describe that skew.
  • Different nVision reports and even different parts of the same report generate different statements that can use different combinations of attribute columns.  The number of dimensions can vary, I have seen systems that use as many as five different trees in a single query.
  • Then the database needs to find the relevant rows on the ledger table for the dimensions specified as efficiently as possible.
This very quickly becomes a difficult and complex problem.  This series articles works through the various challenges and describe methods to overcome them.  Not all of them are applicable to all systems, in some cases, it will be necessary to choose between approaches depending on circumstances.

nVision Performance Tuning: Table of Contents

This post is an index for a series of blog posts that discuss how to get good performance from nVision as used in General Ledger reporting.