RE: 10gR2 Performance sux!

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.

* I don't think I would recommend using FIRST_ROWS for any database, and
probably not FIRST_ROWS_n either for a Financials database.

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
The PeopleSoft DBA Blog: http://psftdba.blogspot.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 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@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Clarke, Andrew
>Sent: Friday, June 15, 2007 4:02 AM
>To: yoursraju007@xxxxxxxxx; amit.poddar@xxxxxxxx; 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@xxxxxxxxxxxxx 
>
>
>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

Other related posts: