_unnest_subquery=FALSE for PeopleSoft performance?

Oracle support recommends setting the hidden parameter _unnest_subquery to FALSE to improve the performance of PeopleSoft applications on Oracle databases.  I’m trying to understand the consequence of changing this parameter from its default setting of TRUE.

PeopleSoft applications have a lot of effective dated rows.  I.e. If you change jobs the change is effective on a given date.  So, you don’t have just one job record you have all the records for the various jobs you have had with the company.  The logic looks something like this to look up a given employee’s job as of today:

  SELECT *
    FROM   PS_JOB D
   WHERE   D.EMPLID = 'NOTREAL'
           AND D.EMPL_RCD = 0
           AND D.EFFDT =
                 (SELECT   MAX (J.EFFDT)
                    FROM   PS_JOB J
                   WHERE       J.EMPLID = D.EMPLID
                           AND J.EMPL_RCD = D.EMPL_RCD
                           AND J.EFFDT <= sysdate)
           AND D.EFFSEQ =
                 (SELECT   MAX (K.EFFSEQ)
                    FROM   PS_JOB K
                   WHERE       K.EMPLID = D.EMPLID
                           AND K.EMPL_RCD = D.EMPL_RCD
                           AND K.EFFDT = D.EFFDT)

I didn’t use a real employee id for this plan – NOTREAL is just something I made up as a fake employee id.

If you set _unnest_subquery to FALSE you get a pretty simple plan:

-------------------------------------------------
| Id  | Operation                      | Name   |
-------------------------------------------------
|   0 | SELECT STATEMENT               |        |
|   1 |  TABLE ACCESS BY INDEX ROWID   | PS_JOB |
|*  2 |   INDEX RANGE SCAN             | PSAJOB |
|   3 |    SORT AGGREGATE              |        |
|   4 |     FIRST ROW                  |        |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| PSDJOB |
|   6 |    SORT AGGREGATE              |        |
|   7 |     FIRST ROW                  |        |
|*  8 |      INDEX RANGE SCAN (MIN/MAX)| PSDJOB |
-------------------------------------------------

The way I read this it looks like it uses the PSAJOB index to look up the emplid and empl_rcd and then for each job record it checks to see if it has the max effdt and effseq.

Here is the plan with the default setting of _unnest_subquery=TRUE:

-------------------------------------------------
| Id  | Operation                     | Name    |
-------------------------------------------------
|   0 | SELECT STATEMENT              |         |
|*  1 |  HASH JOIN                    |         |
|   2 |   NESTED LOOPS                |         |
|   3 |    VIEW                       | VW_SQ_2 |
|   4 |     HASH GROUP BY             |         |
|*  5 |      INDEX RANGE SCAN         | PSDJOB  |
|   6 |    TABLE ACCESS BY INDEX ROWID| PS_JOB  |
|*  7 |     INDEX UNIQUE SCAN         | PS_JOB  |
|   8 |   VIEW                        | VW_SQ_1 |
|*  9 |    FILTER                     |         |
|  10 |     HASH GROUP BY             |         |
|* 11 |      INDEX RANGE SCAN         | PSDJOB  |
-------------------------------------------------

In this case it looks like steps 2 through 7 group job records together getting the maximum effseq.  These are then hash joined to the rows from steps 8 through 11 which are grouped together getting the maximum effdt.  In both groupings the PSDJOB index scans have the conditions on emplid and empl_rcd in their predicates so maybe this is where the unnesting occurs.  The D.EMPLID = ‘NOTREAL’ AND D.EMPL_RCD = 0 predicates get mixed in with the subqueries.

In a test with a real employee id the test with _unnest_subquery = FALSE had fewer consistent gets which probably means it is more efficient:

_unnest_subquery = FALSE

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       6671  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

_unnest_subquery = TRUE

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
       6671  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

We started down this path because we had a payroll confirm process start to run too long after patching to 11.2.0.3 from 11.2.0.1.  I think we may have hit bug 14110304 which was introduced in 11.2.0.2.  Setting _unnest_subquery = FALSE fixed the performance issue with the confirm and all of our other testing has gone well with this setting.  Also, I’ve extracted a bunch of production queries and run them on our patched system with the new setting and they work fine.  So, it appears that _unnest_subquery = FALSE is a good setting for PeopleSoft systems just as Oracle recommends and that at least in some cases the effective date logic works better with this setting.

Here is a zip of a script and its log showing the two plans from above.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply