E-ORACLE:10g Master Performance Solution for Oracle 10g

Summary:
					E-ORACLE:10g Master Performance Solution for Oracle 10g
			Details:		SOLUTION 201049233: E-ORACLE:10g Master Performance Solution for Oracle 10g SPECIFIC TO: Enterprise Peoplesoft , Oracle 10g ISSUE: Oracle 10g Performance Solution SOLUTION: INITIAL TRIAGE --------------------- 1] Check Certification Page to ensure that the environment is Certified URL - http://www.peoplesoft.com/corp/en/iou/platforms/platforms_supported.jsp 2] Check Operating System, RDBMS & Additional Component Patches Required for Installation . This page is initially created during Testing before product ga and  is updated as and when we find out about parameters/patches required for our application. For any performance problem after confirming that the environment is certified, this should be the next place to check URL - http://www4.peoplesoft.com/psdb.nsf/CommByTopic?OpenView&Start=1&Count=30&Expand=4#4 RECOMMENDATIONS ------------------------------- Following recommendations and workaround were helpful to many customers. Tesing on a demo environment is strongly recommended before making changes to your Production. Some of these parameters may already be recommended either in the Red Paper or the Third party required patches document. 1] Init.ora Parameters _UNNEST_SUBQUERY=FALSE 2] Dynamic Sampling -  Since most of PeopleSoft programs use %UpdateStats to manage statistics of our temporary tables so we recommend to turn this parameter off by setting the following: OPTIMIZER_DYNAMIC_SAMPLING = 0 3] OCI Bind Issue Summary: Oracle 10.2.x.x.x client regression causing severe performance issues(up to 100% regression) with Enterprise PeopleSoft versions supporting Oracle 10gR2. Description: Severe performance degradation issues being experienced by our customers who have upgraded to Oracle 10gr2. This is caused by a behaviour regression of OCIHandleFree of OCI_HTYPE_BIND OCI calls issued against Oracle 10.2 client.  Execution of these calls by the Enterprise PeopleSoft against prior versions of the Oracle client did not exhibit this performance behaviour. Solution: Patches are now available on all  Enterprise PeopleSoft supported platforms for Oracle versions 10.2.0.2.0 and 10.2.0.3.0.  Customer Connection has been updated to reflect the necessity of the patch for all PeopleSoft installations using Oracle versions 10.2.0.2.0 and 10.2.0.3.0. Solution ID: 201050932 4] Analyze v/s DBMS_STATS for Online Programs Starting 8.48 %UPDSTATS metasql uses DBMS_STATS to collect statistics. DBMS_STATS is the recommended way to collect stats, If you suspect that DBMS_STATS is causing performance degradation of your AE process, you can review your AE trace, and look for statements that used the - RECSTATS - Command: Once you confirm that using DBMS_STATS to update statistics is causing a lot of overhead when compared to Analyze with estimate-  when using %Updatestats, you can revert %Updatestats to use the Analyze command by doing the followings: Check Oracle 10g Performance Red paper for instructions on how to make this change. 5] DBFLAGS This parameter in the Application Server and Process Scheduler files controls secondary database connection which is used for a CRM function, because of sporadic performance problems reported along with deadlocking its recommended to set this parameter to 8 6] DESC Indexes Third Party required patches document has been updated with Patches required for the use of DESC Indexes, without these patches you may experience ORA-03113 errors, Incorrect Rows being returned. 7] Oracle 10g Red Paper Oracle 10g Red Paper can be found at the following location on customer connection. http://www.peoplesoft.com/corp/en/doc_archive/red_paper/rp_e_perf_10g.jsp WORKAROUND: KEYWORDS: Oracle 10g, Oracle, 10, Performance, Hang, Slow , Hints, SPOTLIGHT