PeopleSoft Tables: Deadlocking and Locking causing performance issues

PeopleSoft has some inherent Deadlocking and Locking issues in some of it's tables. These issues can translate into some serious performance problems. I have compiled a list of MOS Notes outlining the more well-known issues and potential solutions in this post. If you come across any other, please add them in the comments section and I can incorporate them as well:

• Deadlocking in PSIBQUEUEINST (Doc ID 656090.1)
• Integration Broker Performance Downgraded because of Locks On Table PSIBQUEUEINST and PSAPMSGPUBSYNC (Doc ID 1367618.1)
• Dead Lock on PSIBQUEUEINST and GetNextNumberWithGapsCommit() Function (Doc ID 1476687.1)
•  CRM: Deadlocks at Database Level on PSLOCK and/or PSVERSION Tables(Doc ID 653099.1)
• E-SEC: Deadlocking on PSVERSION and PSLOCK Tables(Doc ID 1064647.1)
• E-PT: Locking at Database Level on PSLOCK and/or PSVERSION Tables(Doc ID 1951231.1)
• E-SEC/DB2 Issue With Deadlocking On PSLOCK Table When Saving Roles in PT 8.5x(Doc ID 1372612.1
• E-IB: Deadlocks at PSAPMSGPUBINST Table Updates in PeopleTools 8.40-8.47(Doc ID 1291577.1)
• EC: EOPMTOCI Failling With Deadlock Error(Doc ID 2188015.1)

Setting PeopleSoft-specific init.ora parameters for the Oracle database

In this post, i have compiled a list of the common custom parameters set for the Oracle database in PeopleSoft applications, some of which can have a dramatic performance impact on the system. Some of the parameters mentioned are also applicable for general Non-PeopleSoft database applications. Please note that not all of them may apply in your current setup/version - i have included MOS Notes for reference and further reading/exploration.
alter system set "_unnest_subquery"=FALSE scope=spfile sid='*';
alter system set "fast_start_parallel_rollback"=false scope=spfile sid='*';
alter system set "_gby_hash_aggregation_enabled"=FALSE scope=spfile sid='*';
alter system set "_ignore_desc_in_index"=TRUE scope=spfile sid='*';
alter system set "_smu_debug_mode"=4 scope=spfile sid='*';
alter system set "optimizer_adaptive_features"=false scope=spfile sid='*';
alter system set "_optimizer_skip_scan_enabled"=false scope=spfile sid='*';
alter system set "nls_length_semantics"=char scope=spfile sid='*';
alter system set "cursor_sharing"=exact scope=spfile sid='*';
alter system reset "db_file_multiblock_read_count" scope=spfile sid='*';
alter system reset "_optimizer_cost_based_transformation" scope=spfile sid='*';
alter system reset "_complex_view_merging" scope=spfile sid='*';
MOS Notes/Documents:

• E-ORA Advice for the PeopleSoft Oracle DBA (Doc ID 1445965.1)
  From 10gR2, HASH UNIQUE Operation Returns Results in UNSORTED ORDER by Default
  (Doc ID 341838.1)
• Required Interim Patches for the Oracle Database with PeopleSoft (Doc ID 1100831.1)
• Operating System, RDBMS & Additional Component Patches Required for Installation
  PeopleTools - Master List (Doc ID 756571.1)
• ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting. (Doc ID 1169017.1)
• E-ORA: How to convert an Oracle Non-Unicode Database to a Unicode Database (Doc ID 1437384.1)
• Init.ora Parameter "FAST_START_PARALLEL_ROLLBACK" Reference Note (Doc ID 68932.1)
• Removing Descending Indices for PeopleSoft Databases (Doc ID 1909646.1)
• Master Note: Troubleshooting guide for Automatic Undo Management (Doc ID 1579081.1)

 

Getting the Best Performance on Oracle for PeopleSoft Applications: Kickstarter Resources

I have compiled MOS Resources on how to go about getting the best performance on Oracle for PeopleSoft Applications - this includes Best Practices as well as resources about the PeopleSoft Performance Monitor.

• PeopleSoft Performance Monitor Red Paper (Doc ID 747510.1)
• E-ORA PeopleSoft Enterprise Performance on Oracle 11g Database (Doc ID 1460735.1)
• E-ORA Red Paper: PeopleSoft Enterprise Performance on Oracle 10g Database (Doc ID 747254.1)
• PeopleSoft Performance Monitor Database Schema and Use Cases (Doc ID 704808.1)
• PeopleSoft Enterprise DFW Plug-In - Performance Monitor Configuration Information (Doc ID 1324755.1)
• E-PerfMon: Performance Monitor System Performance Page Loads Slow (Doc ID 970485.1)
• E-Perfmon: Performance Monitor No Longer Shows User Sessions When Using Coherence in a Domain (Doc ID 1678612.1)
• If Performance Monitor Is Down Will It Collect Data In a Repository About the Monitored Systems? (Doc ID 2104397.1)
• E-Perfmon: Stale Agent Data Detected and no User Sessions in Performance Monitor (Doc ID 622919.1)
• Top Solutions - PeopleTools Server Tools: Application Server, Performance Monitor, REN, Tuxedo, Weblogic, Websphere, Verity, PSEM (Doc ID 1304972.1) 
• -PHC: PeopleSoft Health Center vs Performance Monitor (Doc ID 2127778.1)
• Prevent and Resolve Performance Issues Advisor: PeopleSoft (PSFT) (Doc ID 1437143.1) 
• Information Center: Optimizing Performance for PeopleSoft Purchasing 9.1 and 9.2 (Doc ID 1454383.2)
• Information Center: Optimizing Performance for PeopleSoft SCM Strategic Sourcing 9.1 (Doc ID 1509869.2)
• Information Center: Optimizing Performance for PeopleSoft SCM Cost Management 9.1 (Doc ID 1509864.2)
• EAR8.8+: Troubleshooting Performance Issues in FSCM PeopleSoft Applications (Doc ID 984534.1)
• E-AE: F.A.Q. on ReUse (or ReUse Statement) Option of SQL Sections in PeopleTools 8.5x Application Engine Programs (Doc ID 1936139.1)