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.

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.

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.

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.

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.

• Introduction
• nVision Performance Options
• Effects of Performance Options
• Indexing of Ledger, Budget and Summary Ledger Tables
• Partitioning of Ledger, Budget and Summary Ledger Tables
• Additional Oracle Instrumentation for nVision
• Logging Selector Usage
• Analysis of Tree Usage  with the Selector Log
• Interval Partitioning and Statistics Maintenance of Selector Tables
• Compression without the Advanced Compression option
• Maintaining Statistics on Non-Partitioned Selector Tables
• Excel -v- OpenXML
• Hinting nVision with SQL Profiles

Related Posts:

• Setting Oracle Session Parameters for Specific Process Scheduler Processes
• Running nVision in a Mixed OpenXML/Excel mode
• Practical Application Performance Tuning: An nVision Case Study (Video)

The current versions of scripts mentioned in the series are available on GitHub.

Interview with PeopleSoft Administrator Podcast: Oracle Resource Manager

This week's  PeopleSoft Administrator Podcast includes a few minutes of me talking to Dan and Kyle about Oracle Resource Manager.

(22 September 2017) #99 - Oracle Resource Manager

You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.

PeopleSoft and Adaptive Query Optimization in Oracle 12c

Adaptive Query Optimization is a significant feature in Oracle 12c. Oracle has made lots of information available on the subject.(See https://blogs.oracle.com/optimizer/oracle-database-12c-is-here).

Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics…There are two distinct aspects in Adaptive Query Optimization: adaptive plans, which focuses on improving the execution of a query and adaptive statistics, which uses additional information to improve query execution plans.

from Optimizer with Oracle Database 12c Release 2, White Paper June 2017 (see http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf)

Adaptive Statistics includes:

• SQL plan directives: created and used to adapt SQL execution plans.
• Statistics feedback: cardinality from table joins is used to improve SQL execution plans.
• Performance feedback: improves the degree of parallelism chosen when PARALLEL_DEGREE_POLICY is set to ADAPTIVE
• Adaptive dynamic sampling for parallel execution: dynamic statistics are gathered for certain parallel queries to improve cardinality estimates. The sample size is determined automatically.

(Parallelism is not used by default within PeopleSoft because all objects are marked NOPARALLEL by Application Designer).

This additional information should help the optimizer make better decisions, but it comes at the price of making the database do more work during SQL parse. Unfortunately, PeopleSoft makes extensive use of dynamically generated SQL, often with literal values leading to large amounts of parse. Even a small additional overhead during SQL parse can result in a significant overhead for the entire system.
Adaptive Plans includes:

• Nested loop join/Hash join selection: the optimizer chooses between nested loops or hash joins at query runtime.
• Adaptive parallel distribution method: the parallel distribution method is determined at runtime.
• Star transformation bitmap pruning: certain bitmap indexes may be removed from the SQL execution plan at runtime if selectivity is significantly poorer than the estimate.

When joining tables, making the best choice between nested loops and hash joins is often critical for good performance. This feature could be of significant benefit to many applications, not just PeopleSoft. Let's take, as an example, this SQL generated by nVision on a 12c release 1 database. Due to the literal values it is very unlikely that the statement will be executed a second time.

SELECT SUM(A.POSTED_TOTAL_AMT) FROM sysadm.PS_LEDGER A WHERE A.LEDGER ='XXXXXXXXXX' AND A.FISCAL_YEAR=2015 AND (A.ACCOUNTING_PERIOD 
BETWEEN 1 AND 12 OR A.ACCOUNTING_PERIOD BETWEEN 998 AND 999) AND A.ACCOUNT IN ('40000001','40000002','40000003','40000004') AND 
(A.DEPTID ='001A' OR A.DEPTID ='002A' OR A.DEPTID ='003A' OR A.DEPTID ='004A' OR A.DEPTID ='005B' OR A.DEPTID ='006A' OR A.DEPTID 
='007A') AND A.CURRENCY_CD ='USD' AND A.STATISTICS_CODE=' '

This recursive SQL was generated by dynamic statistics processing during SQL parse. Such SQL is easily identifiable by the /* DS_SVC */ comment.

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
  SUM(C1)
FROM (
SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "A")  */ 1 AS C1 
FROM "SYSADM"."PS_LEDGER" SAMPLE BLOCK(82.1847, 8) SEED(12)  "A" 
WHERE ("A"."FISCAL_YEAR"=2015) AND ("A"."CURRENCY_CD"='USD') AND
  ("A"."ACCOUNT"='40000001' OR "A"."ACCOUNT"='40000002' OR
  "A"."ACCOUNT"='40000003' OR "A"."ACCOUNT"='40000004') AND
  ("A"."ACCOUNTING_PERIOD"<=170 AND "A"."ACCOUNTING_PERIOD">=1 OR
  "A"."ACCOUNTING_PERIOD">=901 AND "A"."ACCOUNTING_PERIOD"<=905) AND
  ("A"."DEPTID"='001A' OR "A"."DEPTID"='002A' OR "A"."DEPTID"='003A' OR
  "A"."DEPTID"='004A' OR "A"."DEPTID"='005B' OR "A"."DEPTID"='006A' OR
  "A"."DEPTID"='007A') AND ("A"."LEDGER"='XXXXXXXXXX') AND
  ("A"."STATISTICS_CODE"=' ')) innerQuery

It is easy to see that you wouldn't need too many additional queries like this to have a significant on system performance. In the early days of Oracle 12c many PeopleSoft customers reported just such experience. It is no surprise therefore that Oracle recommend disabling adaptive optimization in 12c. 

optimizer_adaptive_features = FALSE - After upgrading to Oracle Database 12c, many PeopleSoft customers have noticed overall performance degradation which is related to Oracle 12c Optimizer Adaptive Feature (OAF). It is recommended that this value be disabled in all Oracle Databases running PeopleSoft Applications. 

from E-ORA Advice for the PeopleSoft Oracle DBA (Doc ID 1445965.1) (see https://support.oracle.com/epmos/faces/DocumentDisplay?id=1445965.1)

In Oracle 12c release 1, the single parameter OPTIMIZER_ADAPTIVE_FEATUES controls all the adaptive optimization features. However, in Oracle 12c release 2 this is replaced by 2 new parameters:

• OPTIMIZER_ADAPTIVE_STATISTICS defaults to FALSE, thus saving the additional statistics gathering overhead at parse time.
• OPTIMIZER_ADAPTIVE_PLANS defaults to TRUE, giving the benefit of switching between nested loop and hash joins.

Significantly this enhancement is available in 12c release 1. See

• Mike Dietrich's blog: Enabling ADAPTIVE Features of Oracle 12.2 in 12.1 (see https://mikedietrichde.com/2016/11/23/enabling-adaptive-features-of-oracle-12-2-in-12-1/)
• Oracle Support Note: Recommendations for Adaptive Features in Oracle Database 12c (Doc ID 2187449.1) (see https://support.oracle.com/epmos/faces/DocumentDisplay?id=2187449.1).
• https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2

There are two patches available

• patch for bug# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS and removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
• patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.

Here is an example of another query from nVision, this time on a 12.1 system where the two patches have been applied. The optimizer has switched from a nested loop to a hash join in an adaptive plan. In this particular case, this was a good decision and the hash join outperformed the nested loop.

SELECT A.AFFILIATE,SUM(A.POSTED_TOTAL_AMT) FROM PS_XX_XXXXXXX_VW A,
PSTREESELECT10 L1 WHERE A.LEDGER='XXXXXXX' AND A.FISCAL_YEAR=2016 AND
A.ACCOUNTING_PERIOD BETWEEN 0 AND 12 AND L1.SELECTOR_NUM=63382 AND
A.ACCOUNT=L1.RANGE_FROM_10 AND A.BUSINESS_UNIT='XXXXXX' AND
A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY A.AFFILIATE

Plan hash value: 4041461164

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                   |       |       |    86 (100)|          |       |       |
|   1 |  HASH GROUP BY                                   |                   |     1 |    67 |    86   (3)| 00:00:01 |       |       |
|   2 |   HASH JOIN                                      |                   |     9 |   603 |    85   (2)| 00:00:01 |       |       |
|-  3 |    NESTED LOOPS                                  |                   |     9 |   603 |    85   (2)| 00:00:01 |       |       |
|-  4 |     STATISTICS COLLECTOR                         |                   |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR                    |                   |     9 |   459 |    84   (2)| 00:00:01 |    16 |    28 |
|   6 |       INLIST ITERATOR                            |                   |       |       |            |          |       |       |
|   7 |        PARTITION LIST ITERATOR                   |                   |     9 |   459 |    84   (2)| 00:00:01 |KEY(I) |KEY(I) |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER         |     9 |   459 |    84   (2)| 00:00:01 |   KEY |   KEY |
|   9 |          INDEX RANGE SCAN                        | PSCLEDGER         |   117 |       |    52   (2)| 00:00:01 |   KEY |   KEY |
|- 10 |     PARTITION RANGE SINGLE                       |                   |     1 |    16 |     1   (0)| 00:00:01 | 63382 | 63382 |
|- 11 |      INDEX RANGE SCAN                            | PSAPSTREESELECT10 |     1 |    16 |     1   (0)| 00:00:01 | 63382 | 63382 |
|  12 |    PARTITION RANGE SINGLE                        |                   |    23 |   368 |     1   (0)| 00:00:01 | 63382 | 63382 |
|  13 |     INDEX RANGE SCAN                             | PSAPSTREESELECT10 |    23 |   368 |     1   (0)| 00:00:01 | 63382 | 63382 |
--------------------------------------------------------------------------------------------------------------------------------------
…
Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

Conclusion 

Adaptive Optimization is a great feature that does deliver real benefits, but like many Oracle features, it works best on an application that has been developed how Oracle would recommend applications be developed. Since the default value for OPTIMIZER_ADAPTIVE_STATISTICS is FALSE, PeopleSoft is clearly not alone in not being such an application.
Given PeopleSoft's propensity to generate non-sharable SQL, adaptive statistics are not a viable option, and should not be enabled, and in 12c release 1 that has meant sacrificing the benefits of adaptive plans. However, patch 22652097 means that we don't have to wait for release 2, we can now choose which feature to use.
Every system is different, even every PeopleSoft system is different. However, my experience of this so far with PeopleSoft is that I have not seen Oracle switch join method cause a problem. It certainly isn't a panacea either. When the join method has changed, it hasn't always made a big difference, but it has been net beneficial. I would certainly recommend applying these patches.