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. >-- >//www.freelists.org/webpage/oracle-l >-- >//www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l