Friday, May 06, 2016

PeopleSoft on Oracle 12c

I was asked by Dan Iverson from about my experiences of PeopleSoft on Oracle 12c.  I have seen a number of PeopleSoft Financials systems on 12c recently.  Generally, the experience is very positive, but one common feature is that they had all disabled Adaptive Query Optimization.

What is Adaptive Query Optimization?

Adaptive Query Optimization is a term for a set of new features in Oracle 12c to allow the optimizer to improve the accuracy of execution plans.  It is described in the Optimizer with Oracle 12c white paper.  Put simply, Oracle collects additional statistics at parse which it can use to generate a better execution plan than if just using the ordinarily collected statistics, and further statistics at execution time which can be used to decide to change the next execution of the same SQL statement.

Why does it cause a Problem in PeopleSoft?

Adaptive Optimization is clearly a very useful feature in an application where SQL is shareable and reused.  But there's the rub.  This is another Oracle feature that works best with an application that is written the way that Oracle advise applications be written (Automatic Memory Management and the plan stability/management technologies also come to mind), and PeopleSoft is not such an application.  PeopleSoft applications are SQL parse intensive.

Parsing SQL is an expensive business in terms of time and CPU.  The library cache preserves the results of previous parses to save the overhead of repeatedly parsing the same SQL for subsequent executions.  If the SQL is different, even just by a literal value, it will have a different SQL_ID and will not be matched.  If SQL statements in the library cache are not being reused, then you incur the cost of parse for each execution.  Adaptive Optimization adds to that overhead, in PeopleSoft sometimes to the point where it can have a significant effect on performance.

PeopleSoft has several behaviours which lead to different SQL statements being generated from the same source:

  • Application Engine %BIND() variables are resolved to literals in the generated SQL if the ReUseStatement attribute is not set, which by default it is not.  Thus, each iteration of a loop may cause the same Application Engine step to produce a different SQL statement with a different SQL ID (see Performance Benefits of ReUse Statement Flag in Application Engine).  PeopleSoft development has got better at using this attribute in delivered code.  However, there are still many places where it could be set but is not.
  • There are many places in PeopleSoft where SQL is generated dynamically to cater for dynamic application configuration options.  Bind variables may be embedded in the SQL as literals, or more significant changes may be introduced such as completely different criteria or joining different tables.  In Application Engine, ReUseStatement usually cannot be used in conjunction with such steps.
  • Different concurrently executing instances of the same Application Engine programs will be allocated a different non-shared instance of a temporary record, so they reference different tables.  
Some parts of PeopleSoft do produce shareable SQL statements.  Much of the SQL generated by the component processor and PeopleCode uses bind variables, the exception being where dynamic SQL is generated in PeopleCode.  PeopleSoft COBOL programs also widely use bind variables, but again some places dynamically generate SQL statements.

What does Oracle say about it?

There are a number of documents on the Oracle support website that touch on this (you will need to log in to follow these links):

Advice for the PeopleSoft Oracle DBA: The source of this document is not clear.  It is not attached to a MOS note, and is not in the usual format for such notes.  It is the only document that I can find that makes specific recommendations for Adaptive Optimization in conjunction with PeopleSoft, and it recommends totally disabling the entire Adaptive Optimization feature:
  • "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."
  • It has always been tempting to enable cursor_sharing with PeopleSoft so that Oracle converts literals in the SQL back to bind variables and thus matching SQLs in the library cache with different literal values.  However, it has no benefit for some dynamic SQL and different temporary table instances.  It can also introduce other optimizer problems.  On the few occasions that I have tried it, I have never had a good experience.  This document also recommends against it.
How to Approach Issues that Appear Related to Adaptive Features Such as Adaptive Query Optimization (Doc ID 2068807.1): This note acknowledges that "while some issues with adaptive features have been found, in a number of cases, issues manifesting as performance degradation associated with Adaptive Query Optimization simply highlight deficiencies in the system, primarily inaccurate or stale object or system statistics,  Due to the nature of the activities that Adaptive Query Optimization perform, the impact of degraded performance can be widespread and can include (but not limited to):
    • Poor SQL execution performance (where a poor plan is selected)
    • Poor Parse performance (where the optimizer takes more time to determine the optimal access path)
  • The scope of either of the issues may not be limited to individual statements but can impact the whole system in unforeseen ways, for example, both poor SQL execution and parse performance may cause locks to be held for a greater duration than normal causing lock contention while poor parse performance may hold latches or mutexes to similar results.
  • Blanket disabling of features: While disabling Adaptive Features with blanket parameters (see: Document 2031605.1) may provide a short-term workaround, the loss of their benefits to other queries may be significant. If the underlying cause is some other factor then a better solution is to identify and address that"
Adaptive Query Optimization (Doc ID 2031605.1) discusses how to disable either the entire Adaptive Query Optimization feature
OPTIMIZER_ADAPATIVE_FEATURES=FALSE /*disables adaptive optimisation as a whole*/
or to disable individual sub-features
_optimizer_adaptive_plans=false /*disables adaptive plans*/
_optimizer_use_feedback=false /*disables the use of statistics feedback for subsequent executions.  Default is true*/
_px_adaptive_dist_method=off /*disables the adaptive parallel distribution methods*/
_optimizer_dsdir_usage_control=0 /* disables the optimizer usage of dynamic statistics (sampling) directives. Default value is 126 */
_optimizer_gather_feedback=false /*disables the gathering of execution feedback in the optimizer*/
_optimizer_nlj_hj_adaptive_join=false /*disables only the adaptive join from nested loops to hash join*/


I have worked with a number of PeopleSoft Financials systems on Oracle 12c.  All but one of them had already disabled Adaptive Query Optimization, and I had to disable it on that one.  Once disabled performance improved such that it was at least as good as it had been on 11g.
There is no question that it is important to maintain accurate statistics and histograms on tables in PeopleSoft.  I have written elsewhere about how to achieve that, especially on temporary tables.  However, I am left with a concern that disabling the entire feature may be excessive and that there may be areas in PeopleSoft where it can bring some benefits.  I would like to find the opportunity to test whether it is possible to achieve better performance by only disabling certain parts of the feature.

Update 8.7.2017: The story moves on.  PeopleSoft and Adaptive Query Optimization in Oracle 12c.


saad Abbasi said...

Is there any documentattion where we can know what test should we do to check performnace of application (FSCM 9.1 and 8.54)on 12 C (FROM 11-G)

Tyler said...

David - you ended with wishing you could turn off 12c Adaptive features more granularly, but had to solve the problem quickly (paraphrasing). I wanted to share that (in part due to your article here as well as from my own research - ... yesterday I had the opportunity to apply only disabling one part of Adaptive features on a Finance database. It did indeed work, jobs are back to normal, and a lot of other things are faster as well. Users are over the moon. We've been running it on 12c for months without complaint but really didn't get the benefit of the new hardware we moved to at the same time, performance was roughly the same as before. Then about 10 days ago (I only heard about it a few days ago) performance took a nosedive.

Through tracing job sessions I was able to see that the Optimizer was running many dynamic sampling queries for each query in the Nvision reports, and those queries don't use binds (not sure if that's delivered or custom, but ours don't), and they issue thousands of these non-bind queries, so this was a perfect storm ... jobs that ran in an hour were taking 6, 8, 10 hours to finish, and all the time was due to these dynamic sampling queries. Execution plans did not change, it chose the same ones anyway, and actual query runtime didn't change, just parse. So far to deal with this problem I have been turning off Adaptive features on a per-query or per-job (session) level in all of our Peoplesoft pillars, but this change was so drastic and whole-database that I disabled Adaptive Dynamic Statistics at the database level. The jobs finished instantly (as in 5 min later), and overall performance is faster across most jobs and also the user experience.

Obviously something changed in stats or SQL Plan Directives or the like to trigger this change, and I'll need to look into that, but really the benefit of this feature is not worth the risk. It should be disabled in any Finance db (_optimizer_dsdir_usage_control=0 /* disables the optimizer usage of dynamic statistics (sampling) directives. Default value is 126 */), and possibly any Peoplesoft db. In fact, in any database that is not perfectly best-practices and has frequently-issued non-bind queries. This feature in particular really feels not realistic in its implementation. It should be using the FORCE_MATCHING_SIGNATURE to 'know' that it has already run for a given non-bind query, and not destroying performance.

I'm going to try to use RAT (we are licensed thankfully) and see the impact of turning this off in our other databases, as I have time.

While I'm at it - this is another one I've disabled in all peoplesoft db, although would be superfluous if you disable dynamic adaptive stats:

Thanks for your work, it's very helpful for those of us dealing with Peoplesoft on Oracle!

Amit said...

Hi jim,

Currently we are using oracle db 12c and ps 8.54. db is unicode. Db is able to store non- ascii value like ¿,Ä. Is there any way to strip these value before saving. Is there any solution at db level. Or at ps aide