RE: _unnest_subquery set to FALSE
Date: Mon, 5 Nov 2012 13:28:16 -0000
Message-ID: <015f01cdbb59$6b30b4d0$41921e70$_at_go-faster.co.uk>
Actually PeopleSoft is far more proscriptive than that. It is "required at install" to set it to false for PeopleSoft systems. This advice has been issued for all versions of the database since the default value of _unnest_subquery changed from FALSE to TRUE - which was (I think) Oracle 9i.
PeopleSoft makes extensive use effective-dated and effective-sequenced rows and so generates SQL with correlated sub-queries to find the current effective-dated rows. The problem (at least in PeopleSoft) is that the optimizer grossly underestimates the cardinality of the correlated sub-queries where there are multiple correlating columns leading it to choose SQL transformation where it has unnested the sub-query as an inline view. Setting this parameter is a blunt but highly effective tactic in PeopleSoft systems, I have seen systems rendered totally unusable by failing to set it. Needless to say, you will find cases where the UNNEST_SUBQUERY hint is beneficial!
regards
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Christopher.Taylor2_at_parallon.net
Sent: 05 November 2012 13:02
To: sidi.bouzid.meknessy_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: _unnest_subquery set to FALSE
Importance: High
I think PeopleSoft recommends setting this to FALSE (IIRC).
Check Metalink.
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mek S
Sent: Monday, November 05, 2012 7:00 AM
To: ORACLE-L
Subject: _unnest_subquery set to FALSE
hello,
When recently visting a custumer, I have seen this init parameter
_unnest_subquery set to FALSE.
It is an 11.2.0.3 Oracle database.
I wonder what are the reasons behind setting this parameter to non-default
value.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 05 2012 - 14:28:16 CET