RE: _unnest_subquery set to FALSE

From: David Kurtz <info_at_go-faster.co.uk>
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

Original text of this message