RE: _cost_equality_semi_join, Peoplesoft, performance issues
- From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Jan 2005 20:14:40 -0000
I found the following on the PeopleSoft Customer Connection Website:
(as the DBA you _DO_ need a login to this support site)
http://www4.peoplesoft.com/psdb.nsf/0/244e1ab25a22d61088256bab00002375?OpenD
ocument&ExpandSection=8#_Section8
"Operating System, RDBMS & Third Party Product Patches Required for
Installation PT 8.40-8.43
dated 29 April 2002
Required INIT.ORA Parameters:
Its come to our attention that the Oracle 9.0.1.x.x CBO optimizer may
produce inefficient plans.
Oracle recommended that our customers set the following init.ora parameter
for Oracle 9i versions 9.0.1.x.x:
optimizer_features_enable=8.1.7
These optimizer issues have been addressed in Oracle 9.2.0.2.x Setting the
optimizer_features_enable=8.1.7 parameter is not needed with 9.2.0.2 and
beyond.
The remaining reason for the necessity of the
optimizer_features_enable=8.1.7 init.ora parameter was for an Upgrade issue
discovered internally while testing the upgrade to PT 8.4x . The upgrade
compare step was completing but the results of the step were incorrect. The
reason for this was the underlying select criteria in some of the upgrade
compare steps were not updating the correct number of rows.
Although setting optimizer_features_enable=8.1.7 did address the Upgrade
issue, there was a negative performance impact on the kinds of SQL utilized
in the Tools Tree feature. Using PeopleSoft Trees would be very slow.
The aforementioned specific Upgrade issue has been fixed in Oracle 9.2.0.4.
For those customers still on Oracle 9.2.0.2 and 9.2.0.3, they should be
adding the following parameter to their init.ora:
_cost_equality_semi_join=false
in lieu of setting optimizer_features_enable=8.1.7 in their init.ora in
order to address the specific Upgrade issue."
It goes on to discuss some Oracle 9.2 bugs that cause incorrect query
results, that are resolved in 9.2.0.4, but can be worked around by setting:
_complex_view_merging=FALSE
_unnest_subquery=FALSE
The PSFT certification report for PeopleTools 8.45 on Oracle 9i also
specifies these parameters.
The documentation says issue goes away when you fully patch 9.2.
regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk
mailto:david.kurtz@xxxxxxxxxxxxxxx
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
> Sent: 12 January 2005 19:33
> To: jclarke@xxxxxxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: _cost_equality_semi_join, Peoplesoft, performance issues
>
>
> I have updated a few Peoplesoft systems and I have never changed this
> particular init.ora parameter. Two that I know of (with the potential
> of) causing performance problems in Peoplesoft on 9i are
>
> _b_tree_bitmap_plans
> and
> _complex_view_merging
>
> They changed from false to true from 8i to 9i and I know many Peoplesoft
> installations running on 9i set these back to false.
> John Clarke wrote:
>
> > I've recently taken on the role of a DBA for a Peoplesoft
> upgrade projec=
> > t and have a question about =5Fcost=5Fequality=5Fsemi=5Fjoin.
> >
> > We're running Oracle 9.2.0.3 on HP-UX 11.00, upgrading from
> Peoplesoft 7=
> > .5 to 8.<something>. During this little project, we'll be
> upgrading from=
> > 7.3.4 to 9iR2, upgrading HP-UX from 11.00 to 11i, and
> upgrading Peoples=
> > oft and its tools, but due to where we're starting and what
> sort of hard=
> > ware we're on, we need to upgrade from 7.3.4 to 9.2.0.3 first,
> then upgr=
> > ade Peoplesoft, then upgrade HP-UX to 11i, then patch Oracle up
> to 9.2.0=
> > .5 or 6.
> >
> > Anyway ...
> >
> > Since we'll be running Peoplesoft 7.x against 9.2.0.3 for a
> month or so,=
> > we're in the middle of doing performance/load testing with the
> version =
> > combination. We've found a handful of performance issues that
> are relate=
> > d to us setting =5Fcost=5Fequality=5Fsemi=5Fjoin to FALSE, the
> non-defau=
> > lt value.
> >
> > We did this b/c we've found documentation recommending doing
> this, plus =
> > a handful of postings in various places have done this against 9.2.0.3.
> >
> > My understanding is that =5Fcost=5Fequality=5Fsemi=5Fjoin
> needed to be s=
> > et to FALSE to circumvent a bug that happens during the
> Peoplesoft upgra=
> > de process, but I'm not sure since my sources are varied and unreliable.
> >
> > Does anyone have experience with this parameter against
> 9.2.0.3, specifi=
> > cally in the context of Peoplesoft=3F If so, I'd be curious to
> see what =
> > sorts of behavior you've seen.
> >
> > Thanks,
> >
> > John
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
