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.

PeopleSoft on Oracle 12c

I was asked by Dan Iverson from psadmin.io 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*/

Summary

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.

Introducing the Analytic Keep Clause for Effective-Dated/Sequence Queries in PeopleSoft

Those of us who work with PeopleSoft, and especially the HCM product, are all too familiar with the concept of effect-dated data, and the need to find data that was effective at a particular date.  PeopleSoft products have always made extensive use of correlated sub-queries to determine the required rows from an effective-dated record.
The JOB record is the heart of HCM. It is both effective-dated and effective sequenced. I will use it for the demonstrations in this article. I am going to suggest an alternative, although Oracle-specific, SQL construction.
 Let's start by looking at the job data for an employee in the demo database. Employee KF0018 has 17 rows of data two concurrent jobs.  The question I am going to ask is "What was the annual salary for this employee on 11 February 1995?".  Therefore, I am interested in the rows marked below with the asterisks. 

column annual_rt format 999,999
SELECT  emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM	ps_job j
WHERE	j.emplid = 'KF0018'
ORDER BY 1,2,3,4
/ 

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018               0 12-JUN-83          0 HIR 13000      FRF   120,000
KF0018               0 01-JAN-84          0 PAY 13000      FRF   123,600
KF0018               0 01-JAN-85          0 PAY 13000      FRF   127,308
KF0018               0 01-JAN-86          0 PAY 13000      FRF   131,764
KF0018               0 01-JAN-87          0 PAY 13000      FRF   136,376
KF0018               0 01-JAN-88          0 PAY 13000      FRF   140,467
KF0018               0 01-JAN-89          0 PAY 13000      FRF   147,490
KF0018               0 22-JAN-95          0 PRO 13000      FRF   147,490
KF0018               0 22-JAN-95          1 PAY 13000      FRF   294,239 *
KF0018               0 22-JAN-96          0 PAY 13000      FRF   318,575
KF0018               0 01-JAN-98          0 PAY 13000      FRF   346,156
KF0018               0 01-JAN-00          0 DTA 13000      FRF   346,156
KF0018               0 01-JAN-02          0 PAY 13000      EUR    52,771
KF0018               1 01-NOV-89          0 ASG 21300      GBP    22,440
KF0018               1 31-DEC-93          0 ASC 21300      GBP    22,440
KF0018               1 01-JAN-94          0 ASG 12000      GBP    22,440 *
KF0018               1 31-DEC-95          0 ASC 10000      GBP    22,440

I will set the statistics level to ALL so I can obtain detailed information about how the SQL statements execute:

ALTER SESSION SET statistics_level = ALL;

I extracted the execution plans and execution statistics with the following command

select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS')) 

Typical PeopleSoft Platform Agnostic Construction

This is the usual way to construct the query in PeopleSoft. It is also valid on all databases platforms supported by PeopleSoft, not just Oracle. 

SELECT  emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM	ps_job j
WHERE	j.effdt = ( 
        SELECT MAX (j1.effdt) FROM ps_job j1 
        WHERE j1.emplid = j.emplid 
        AND j1.empl_rcd = j.empl_rcd 
        AND j1.effdt <= TO_DATE('19950211','YYYYMMDD')) 
AND j.effseq = ( 
        SELECT MAX (j2.effseq) FROM ps_job j2 
        WHERE j2.emplid = j.emplid 
        AND j2.empl_rcd = j.empl_rcd 
        AND j2.effdt = j.effdt)
AND j.emplid = 'KF0018'
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018               0 22-JAN-95          1 PAY 13000      FRF   294,239
KF0018               1 01-JAN-94          0 ASG 12000      GBP    22,440

This required three access of indexes on the PS_JOB table, and two accesses of the table, using 26 consistent reads.

Plan hash value: 2299825310
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |      1 |        |      2 |00:00:00.01 |      26 |      2 |
|   1 |  SORT ORDER BY                    |         |      1 |      1 |      2 |00:00:00.01 |      26 |      2 |
|   2 |   NESTED LOOPS                    |         |      1 |      1 |      2 |00:00:00.01 |      26 |      2 |
|   3 |    NESTED LOOPS                   |         |      1 |      1 |      3 |00:00:00.01 |      21 |      2 |
|   4 |     VIEW                          | VW_SQ_1 |      1 |      1 |      2 |00:00:00.01 |      14 |      2 |
|*  5 |      FILTER                       |         |      1 |        |      2 |00:00:00.01 |      14 |      2 |
|   6 |       HASH GROUP BY               |         |      1 |      1 |      2 |00:00:00.01 |      14 |      2 |
|   7 |        TABLE ACCESS BY INDEX ROWID| PS_JOB  |      1 |      1 |     12 |00:00:00.01 |      14 |      2 |
|*  8 |         INDEX RANGE SCAN          | PS_JOB  |      1 |      1 |     12 |00:00:00.01 |       2 |      2 |
|   9 |     TABLE ACCESS BY INDEX ROWID   | PS_JOB  |      2 |      1 |      3 |00:00:00.01 |       7 |      0 |
|* 10 |      INDEX RANGE SCAN             | PSAJOB  |      2 |      1 |      3 |00:00:00.01 |       4 |      0 |
|* 11 |    VIEW PUSHED PREDICATE          | VW_SQ_2 |      3 |      1 |      2 |00:00:00.01 |       5 |      0 |
|* 12 |     FILTER                        |         |      3 |        |      3 |00:00:00.01 |       5 |      0 |
|  13 |      SORT AGGREGATE               |         |      3 |      1 |      3 |00:00:00.01 |       5 |      0 |
|* 14 |       FILTER                      |         |      3 |        |      5 |00:00:00.01 |       5 |      0 |
|* 15 |        INDEX RANGE SCAN           | PSAJOB  |      3 |      1 |      5 |00:00:00.01 |       5 |      0 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("J1"."EMPLID"='KF0018')
   8 - access("J1"."EMPLID"='KF0018' AND "J1"."SYS_NC00164$">=HEXTORAW('883CFDF4FEF8FEFAFF') )
       filter(SYS_OP_UNDESCEND("J1"."SYS_NC00164$")<=TO_DATE(' 1995-02-11 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
  10 - access("J"."EMPLID"='KF0018' AND "ITEM_2"="J"."EMPL_RCD" AND
              "J"."SYS_NC00164$"=SYS_OP_DESCEND("MAX(J1.EFFDT)"))
       filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")="MAX(J1.EFFDT)")
  11 - filter(SYS_OP_UNDESCEND("J"."SYS_NC00165$")="MAX(J2.EFFSEQ)")
  12 - filter(COUNT(*)>0)
  14 - filter('KF0018'="J"."EMPLID")
  15 - access("J2"."EMPLID"='KF0018' AND "J2"."EMPL_RCD"="J"."EMPL_RCD" AND
              "J2"."SYS_NC00164$"=SYS_OP_DESCEND(SYS_OP_UNDESCEND("J"."SYS_NC00164$")))
       filter(SYS_OP_UNDESCEND("J2"."SYS_NC00164$")=SYS_OP_UNDESCEND("J"."SYS_NC00164$"))

This construction is also the reason you are required to set

_UNNEST_SUBQUERY=FALSE

on all PeopleSoft systems

Analytic Function and In-Line View/Sub-query Factor

I have seen people use a combination of analytic functions and in-line views to avoid having to use the correlated sub-query construction. This has been possible since Oracle 9i.

WITH X AS (
SELECT  emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
,	ROW_NUMBER() OVER (PARTITION BY emplid, empl_rcd 
                           ORDER BY effdt DESC, effseq DESC) myrowseq
FROM	ps_job j
WHERE	j.effdt <= TO_DATE('19950211','YYYYMMDD')
AND	j.emplid = 'KF0018'
)
SELECT 	emplid, empl_rcd, effdt, effseq, action, deptid, currency_cd, annual_rt
FROM 	x
WHERE	myrowseq = 1
ORDER BY 1,2,3,4
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018               0 22-JAN-95          1 PAY 13000      FRF   294,239
KF0018               1 01-JAN-94          0 ASG 12000      GBP    22,440

We get the same result, but now the index is scanned just once and we only need 14 consistent reads, so it produces a significant improvement. However, it still includes a sort operation in addition to the window function. We have to create a sequence number field in the in-line view and filter by that in the final query.

Plan hash value: 1316906785
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |      1 |        |      2 |00:00:00.01 |      14 |
|   1 |  SORT ORDER BY                 |        |      1 |      1 |      2 |00:00:00.01 |      14 |
|*  2 |   VIEW                         |        |      1 |      1 |      2 |00:00:00.01 |      14 |
|*  3 |    WINDOW NOSORT               |        |      1 |      1 |     12 |00:00:00.01 |      14 |
|   4 |     TABLE ACCESS BY INDEX ROWID| PS_JOB |      1 |      1 |     12 |00:00:00.01 |      14 |
|*  5 |      INDEX RANGE SCAN          | PSAJOB |      1 |      1 |     12 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MYROWSEQ"=1)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMPLID","EMPL_RCD" ORDER BY
              "J"."SYS_NC00164$","J"."SYS_NC00165$")<=1)
   5 - access("J"."EMPLID"='KF0018' AND "J"."SYS_NC00164$">=HEXTORAW('883CFDF4FEF8FEFAFF')
              )
       filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")<=TO_DATE(' 1995-02-11 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

Analytic Function Keep Clause

This form of the analytic functions is documented for the first time in 12c, but is available in 10g (my thanks to Tony Hasler for introducing me to it). It works by effectively keeping a running maximum value of the columns in the order by clause within in group.

SELECT emplid, empl_rcd
, MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS effdt
, MAX(effseq) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS effseq
, MAX(action) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS action
, MAX(deptid) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS deptid
, MAX(currency_cd) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS currency_cd
, MAX(annual_rt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq) AS annual_rt
FROM ps_job j
WHERE	j.effdt <= TO_DATE('19950211','YYYYMMDD')
AND	j.emplid = 'KF0018'
GROUP BY emplid, empl_rcd
/

EMPLID        EMPL_RCD EFFDT         EFFSEQ ACT DEPTID     CUR ANNUAL_RT
----------- ---------- --------- ---------- --- ---------- --- ---------
KF0018               0 22-JAN-95          1 PAY 13000      FRF   294,239
KF0018               1 01-JAN-94          0 ASG 12000      GBP    22,440

Although this construction uses an additional consistent read, it has the advantage of not using either an inline view or a window function and does not sort the data.

Plan hash value: 1550496807
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |      1 |        |      2 |00:00:00.01 |      15 |
|   1 |  SORT GROUP BY NOSORT        |        |      1 |      1 |      2 |00:00:00.01 |      15 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PS_JOB |      1 |      1 |     12 |00:00:00.01 |      15 |
|*  3 |    INDEX RANGE SCAN          | PS_JOB |      1 |      1 |     12 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("J"."EMPLID"='KF0018' AND "J"."SYS_NC00164$">=HEXTORAW('883CFDF3FEF8FEFAFF'
              ) )
       filter(SYS_OP_UNDESCEND("J"."SYS_NC00164$")<=TO_DATE(' 1995-02-12 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

I think this construction could be useful in PeopleSoft.  At first glance, the SQL appears more complicated, but in this example, it removed two correlated sub-queries. 

Using Analytic Functions in PS/Query

Of course, you can code it anywhere where you can simply enter SQL as text.  However, it also has the advantage over the other analytic function construction that it can be coded in the PS/Query tool.  The analytic functions in the select clause should be created in PS/Query expressions with the aggregate expression checkbox ticked.

Analytic Function in Aggregated Expression in Windows Client version of PS/Query 

The analytic functions can be selected in the PS/Query, and their lengths and titles can be tidied up.

PS/Query with Analytic 'Keep' Functions

This is the resulting SQL which is the same as before (with row-level security added by PS/Query) and produces the same results.

SELECT A.EMPLID, A.EMPL_RCD, MAX(effdt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq)
, MAX(effseq) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(action) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(deptid) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(currency_cd) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq), MAX(annual_rt) KEEP (DENSE_RANK LAST ORDER BY effdt, effseq)					
  FROM PS_JOB A, PS_EMPLMT_SRCH_QRY A1
  WHERE ( A.EMPLID = A1.EMPLID
    AND A.EMPL_RCD = A1.EMPL_RCD
    AND A1.OPRID = 'PS'
    AND ( A.EFFDT <= TO_DATE('1995-02-11','YYYY-MM-DD')
     AND A.EMPLID = 'KF0018' ) )
  GROUP BY  A.EMPLID,  A.EMPL_RCD