Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10gR2 Performance sux!

RE: 10gR2 Performance sux!

From: David Kurtz <info_at_go-faster.co.uk>
Date: Fri, 15 Jun 2007 18:05:31 +0100
Message-ID: <003b01c7af6f$65d188d0$0a0a0a0a@GOFASTER4>

  1. Have you collected System Statistics?
  2. Have you removed any initialisation parameters set for Oracle 9i?

Read the "Required Operating System, RDBMS & Third Party Product Patches Required for Installation" on Customer Connection: http://www4.peoplesoft.com/psdb.nsf/8a8a06594f72ee9888256bae00726172/5edaf66 b6d4730da88256bab006e68b2?OpenDocument (you will need a customer connection login)
In short it says
* You must set _optimizer_cost_based_transformation=off (although this supposed to be fixed on some 10.2 versions) * For unicode databases set NLS_LENGTH_SEMANTICS=CHAR * If you rely on DISTINCT and GROUP BY to force the order of a query (which you shouldn't) then you should set _gby_hash_aggregation_enabled=false

And also the Platform Certification FAQ. http://www4.peoplesoft.com/psdb.nsf/8a8a06594f72ee9888256bae00726172/d713e74 ef633ca5a88256bab006ef215?OpenDocument&ExpandSection=2.2.9#_Section2.2.9

3. PeopleSoft makes extensive use of correlated sub-queries, mostly in effective dated logic. Although there is not as much of this in Financials as HR. Consider setting _UNNEST_SUBQUERY = FALSE. This was recommended by PeopleSoft for Oracle 9i to fix Bug# 2948326. The recommendation is not made for 10g. However, I have no doubt it is beneficial for HR and CRM. I haven't tried it for Financials, but I would expect it to help.

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 The PeopleSoft DBA Blog: http://psftdba.blogspot.com 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 Guerra, Abraham J
>Sent: Friday, June 15, 2007 4:31 PM
>To: oracle-l
>Subject: RE: 10gR2 Performance sux!
>Importance: High
>
>Hello gurus,
>
>I'm also having performance issues with 10g. Why would my
>dictionary queries take a long time to return... in 9i they
>used to take seconds, now they take minutes or they just never
>come back... This happens when I query dba_data_files or
>dba_tablespaces... The application queries are not too bad.
>
>Any tips will be appreciated.
>
>The database is in HP-UX11,10.2.0.3, FIRST_ROWS. (Migrated from
>9.2.0.7) PeopleSoft Financials.
>
>Thanks.
>
>Abraham
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Clarke, Andrew
>Sent: Friday, June 15, 2007 4:02 AM
>To: yoursraju007_at_gmail.com; amit.poddar_at_yale.edu; oracle-l
>Subject: RE: 10gR2 Performance sux!
>
>
>
>>> Well, I could not user opt_param.. as I can't touch SQLs,
>they come
>>> from 3rd party tool. So, I have created an after login trigger to
>set
>>> this parameter at session level.
>
>Dan Hotka has a presentation called "Tuning SQL when you
>cannot change the code!"
>
>It could help you understand what your options are for
>tackling your problem. Good luck!
>
>http://www.itcork.ie/files/eventresources/ITCorkOracle.pdf
>
>Cheers, APC
>
>A P Clarke
>Software Architect
>LogicaCMG UK Public Sector Division
>Stephenson House
>75 Hampstead Road
>London
>UK
>NW1 2PL
>Direct Tel: +44 (0)207 446 4006
>Fax: +44 (0)207 446 1352
>Email: andrew.clarke_at_logicacmg.com
>
>
>This e-mail and any attachment is for authorised use by the intended
>recipient(s) only. It may contain proprietary material,
>confidential information and/or be subject to legal privilege.
>It should not be copied, disclosed to, retained or used by,
>any other party. If you are not an intended recipient then
>please promptly delete this e-mail and any attachment and all
>copies and inform the sender. Thank you.
>--
>http://www.freelists.org/webpage/oracle-l
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 15 2007 - 12:05:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US