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
