Peoplesoft give some recommandation regarding the Oracle init parameters. These recommandations include the hidden parameter "_unnest_subquery" to be set to false (true by default), even for the most recent version.
It is surprising me, because this recommandation exists for several years now, and on older database as well.
Ok, let's see what if we don't follow this specific recommandation.
On 10.2.0.4 database, we have the following query running for hours :
UPDATE PS_AB_PDI_CN_TAO
SET country_2char = COALESCE(( SELECT q.country_2char
FROM ( SELECT o.oprid
, j.emplid
, j.empl_rcd
, j.effdt
, j.effseq
, c.country_2char
FROM ps_job j
, psoprdefn o
, ps_opr_def_tbl_Hr h
, ps_location_Tbl l
, ps_country_tbl c
WHERE EXISTS ( SELECT 'x'
FROM ps_job j2
WHERE j.emplid = j2.emplid
AND j2.empl_Rcd = 1)
AND j.emplid = o.emplid
AND o.oprclass = h.oprclass
AND j.effdt = ( SELECT MAX(j3.EFFDT)
FROM PS_JOB j3
WHERE j3.EMPLID = j.EMPLID
AND j3.EMPL_RCD = j.EMPL_RCD
AND j3.EFFDT <= sysdate)
AND j.effseq = ( SELECT MAX(j4.EFFSEQ)
FROM PS_JOB j4
WHERE j4.EMPLID = j.EMPLID
AND j4.EMPL_RCD = j.EMPL_RCD
AND j4.EFFDT = j.effdt)
AND l.setid = j.setid_location
AND l.location = j.location
AND h.country = l.country
AND l.effdt = ( SELECT MAX(l2.effdt)
FROM ps_location_tbl l2
WHERE l.setid = l2.setid
AND l.location = l2.location
AND l2.effdt <= j.effdt)
AND c.country = l.country ) q
WHERE Q.EFFDT = ( SELECT MAX(EFFDT)
FROM PS_JOB Q2
WHERE Q.EMPLID = Q2.EMPLID
AND Q2.EFFDT <= SYSDATE
AND Q.EFFDT <= SYSDATE)
AND Q.EFFSEQ = ( SELECT MAX(J3.EFFSEQ)
FROM PS_JOB J3
WHERE Q.EMPLID = J3.EMPLID
AND Q.EMPL_RCD = J3.EMPL_RCD
AND Q.EFFDT = J3.EFFDT)
AND Q.OPRID = PS_AB_PDI_CN_TAO.OPRID)
,' ')
WHERE COUNTRY_2CHAR = ' ';
The explain plan is the following :
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41882 | 3599K| 18 (0)| 00:00:01 |
| 1 | UPDATE | PS_AB_PDI_CN_TAO | | | | |
|* 2 | TABLE ACCESS FULL | PS_AB_PDI_CN_TAO | 41882 | 3599K| 18 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 137 | 26 (12)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 137 | 15 (14)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 117 | 8 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 110 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS SEMI | | 1 | 85 | 5 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | PSOPRDEFN | 1 | 27 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PS_PSOPRDEFN | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PS_OPR_DEF_TBL_HR | 33 | 396 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PS_OPR_DEF_TBL_HR | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 35 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PSAJOB | 1 | | 1 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 16 | | |
|* 17 | FILTER | | | | | |
|* 18 | INDEX RANGE SCAN | PSAJOB | 2 | 32 | 2 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 22 | | |
| 20 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
| 22 | SORT AGGREGATE | | 1 | 22 | | |
| 23 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN (MIN/MAX)| PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | PSAJOB | 8 | 88 | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | PS_LOCATION_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | | 1 (0)| 00:00:01 |
| 28 | SORT AGGREGATE | | 1 | 21 | | |
|* 29 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | 21 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | PS_COUNTRY_TBL | 1 | 7 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PS_COUNTRY_TBL | 1 | | 0 (0)| 00:00:01 |
| 32 | VIEW | VW_SQ_1 | 3743 | 74860 | 6 (17)| 00:00:01 |
| 33 | SORT GROUP BY | | 3743 | 109K| 6 (17)| 00:00:01 |
|* 34 | INDEX FAST FULL SCAN | PS_JOB | 3743 | 109K| 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Well, the last three lines are not as I would expect.
Let's try this hidden parameter :
"_unnest_subquery"=false (true by default)
Finally the explain plan change a lot and looks much better.
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41882 | 3599K| 18 (0)| 00:00:01 |
| 1 | UPDATE | PS_AB_PDI_CN_TAO | | | | |
|* 2 | TABLE ACCESS FULL | PS_AB_PDI_CN_TAO | 41882 | 3599K| 18 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 117 | 21 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 117 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 110 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS SEMI | | 1 | 85 | 5 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | PSOPRDEFN | 1 | 27 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PS_PSOPRDEFN | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | PS_OPR_DEF_TBL_HR | 33 | 396 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PS_OPR_DEF_TBL_HR | 1 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 35 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | PSAJOB | 1 | | 1 (0)| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 16 | | |
|* 16 | FILTER | | | | | |
|* 17 | INDEX RANGE SCAN | PSAJOB | 2 | 32 | 2 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 22 | | |
| 19 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
| 21 | SORT AGGREGATE | | 1 | 19 | | |
| 22 | FIRST ROW | | 1 | 19 | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 19 | 2 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 22 | | |
| 25 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN (MIN/MAX)| PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PSAJOB | 8 | 88 | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | PS_LOCATION_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | | 1 (0)| 00:00:01 |
| 30 | SORT AGGREGATE | | 1 | 21 | | |
|* 31 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | 21 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | PS_COUNTRY_TBL | 1 | 7 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PS_COUNTRY_TBL | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Finally the query is running in few seconds only.
Ok, the Peoplesoft support continue to recommand this setting "_unnest_subquery"=false, and I understand now it helps.
However, looks interesting, cost is same, so what happens ? What makes Oracle decide to run the first intead of the second ?
The only comment about this parameter on Metalink is : "This parameter controls whether the optimizer attempts to unnest correlated subqueries or not." Fair enough, let's see within 10053 trace file the differences.
The part of the query causing the issue is the subquery with J3 alias and SYSDATE.
AND J.EFFDT = ( SELECT MAX(J3.EFFDT)
FROM PS_JOB J3
WHERE J3.EMPLID = J.EMPLID
AND J3.EMPL_RCD = J.EMPL_RCD
AND J3.EFFDT <= SYSDATE)
Surprisingly, for this subquery, the 10053 trace with "_unnest_subquery"=true shows that it even doesn't consider a RANGE SCAN (Min/Max) against PSAJOB where it is the lowest cost when "_unnest_subquery"=false.
Well, maybe time to raise a SR and start to follow the advices.
Enjoy !
Saturday, May 09, 2009
Subscribe to:
Post Comments (Atom)
10 comments:
Wait until you find out about the need to set
_optimizer_mjc_enabled=FALSE...
Without which you'll never be able to run a pay calculation in PS HR with full confidence it will finish within this century!
DAMHIKT...
It is also an interesting one indeed ! After 29 patches, several hidden parameters as fixes for performances and bugs including _IGNORE_DESC_IN_INDEX to avoid an ORA-600 error as I described here http://gasparotto.blogspot.com/2009/02/demo-hrms-90-ora-00600.html
I think 10.2.0.4 is much less ready for Peoplesoft than was 9.2.0.x. Even on 8.1.7.4, and also 9.2.0.x, Peoplesoft was more or less ready to use without so many fixes. Just impressive, when it works on 10.2.0.x, it works faster than ever, but when it does not work, it is worse than ever. And unfortunately, this last case is reproducing too often.
To be completely fair, there are also some clangers in the latest PS and Peopletools releases.
The row security they implemented recently for HR comes to mind.
The main view that implements it uses a SELECT DISTINCT ... to grab the user's authorization level.
This is in turn used, joined in all other SQL statements dealing with secure row-level access.
The result is a horrible performance disaster that hardly ever works and no one in their right minds will ever implement!
We tried for a couple of days to create enough profiles that it'd work in most cases. Eventually gave up on the idea altogether. Talk about a deranged implementation!
One wonders who came up with the brilliant idea for "portable" row-level security using a DISTINCT in a high use view...
word verification: defecti
how appropriate!
The default value for _UNNEST_SUBQUERY changed from FALSE to TRUE in Oracle 9i. Ever since then PeopleSoft recommended (in the platform advisory referenced from Certification reports) setting it back to FALSE. Otherwise, Oracle's cost-based optimizer tends to unnest effective date sub-queries to the front of the from clause because the calculated cardinalities on tables with correlated values in key columns can be significantly different from reality (eg. PS_JOB on columns EMPLID and EFFDT - see Wolfgang Breitling's 'Tuning by Cardinality Feedback papers/presentations). I have never seen setting this parameter have a negative effect on a PeopleSoft application (although I know of one site who have had to set it at session level with a trigger because they are using a data warehouse tool on the same database where unnesting sub-queries is beneficial).
Thank you David, appreciate your comments and feedback regarding this point.
Yes, this parameter is TRUE when OPTIMIZER_FEATURES_ENABLE >= 9.0.0, but I have to say our current 9.2 db has the default one (TRUE) and we have not such crazy performance.
Anyway, thank you both for your informative posts.
You may gain significant performance improvment on certain SQLs,but at the same time it may cause performance issues for many other well-running sub-query SQLs.
Recently,I came up with the same problem with ELM fullsync staging process.The job was completed in 11hrs and 3 SQLs were contributing 10.25 hrs of execution time.Especially one Update SQL was running for 9 hrs with NL join due to poor cardinality estimates.
I knew this SQL will be benefited by setting "_unnest_subquery"=true.
After I set it at the session-level,the same sql ran in seconds and th job was completed in 64 minutes.But it has impacted other SQLs which were using sub-queries.For an example,a SQL which took 54 seconds before is now taking 23 minutes to complete.
So I would prefer not to set this parameter at the system-level unless until the impact is completely known.
Regards
Antony
Hi Nicoles
Can I set this parameter _UNNEST_SUBQUERY to FALSE for Oracle 11.2.0.2.0 ?
Thanks in advanced.
Mr. Juliusss
According to the nore Required Interim Patches for the Oracle Database with PeopleSoft [ID 1100831.1], such recommendation is still there for 11gR2 database.
Nicolas.
We have just set up our app servers for hcm, elm and portal 8.54. when i look at the database I see around 400+ sessions for the user "PEOPLE" with and inactive status. Shouldnt these PEOPLE sessions go away? Any insight you can provide would be greatly appreciated. We go live next week. thanks so much.
there is a bug fix for PEOPLE sessions not disconnecting in Patch.8.54.15 .
Post a Comment