RE: _unnest_subquery set to FALSE
- From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
- To: <Christopher.Taylor2@xxxxxxxxxxxx>, <sidi.bouzid.meknessy@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Nov 2012 13:28:16 -0000
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@xxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: 05 November 2012 13:02
To: sidi.bouzid.meknessy@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
