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.