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)

Valid Node Checking for Registration (VNCR) Implementation on Oracle

Valid Node Checking and Registration (VNCR) is a new feature introduced in versions 11.2.0.4 and 12c of Oracle. VNCR allows registrations to the Oracle Listener more secure such that, they are only allowed from known servers/nodes; hence the namesake. The main advantage of VNCR is to get by without implementing Class of Secure Transport (COST) configurations that, tend to be much more complicated and expensive. VNCR is relatively simple to implement - Following MOS Notes contain the directions and other useful resources.

• Valid Node Checking For Registration (VNCR) (Doc ID 1600630.1)
• How to Enable VNCR on RAC Database to Register only Local Instances (Doc ID 1914282.1)

The following example shows how to implement VNCR on RAC to register the local RAC instances:
VALID_NODE_CHECKING_REGISTRATION_LISTENER=1
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(<enter the list of public ip's of all nodes separated by commas>)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN2=(<enter the list of public ip's of all nodes separated by commas>)
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=1
REGISTRATION_INVITED_NODES_LISTENER_SCAN3=(<enter the list of public ip's of all nodes separated by commas>)

Active Memory Expansion in IBM AIX 7.x - Useful Resources

This post covers some of the Q/A and MOS Notes and IBM Docs related to Active Memory Expansion (Fancy Name for Memory Compression) for Oracle Databases on IBM AIX 7.x.


1. Is IBM Active Memory Expansion (AME) - AIX 7.1 certified/supported for Oracle DB 11.2.0.4 (RAC and Non-RAC)?
Is IBM AIX Active Memory Expansion (AME) Certified or supported for Oracle Databases? ( Doc ID 1524569.1 )


2. Is IBM AME (AIX 7.x) certified for the Oracle Database?
Certification Information for Oracle Database on IBM AIX on Power systems ( Doc ID 1307544.1 )


3. What are the things to watch out for implementing AME on AIX 7.x - Any performance degradations to watch out for?
IBM POWER7 AIX and Oracle Database performance considerations -- 10g & 11g [ Note 1507249.1 ]


Other MOS Notes/Resources:

How to Tune Parameters Available in AIX to AvoNote Memory Failures Under Heavy Load [ Note 457271.1 ]
AIX: Top Things to DO NOW to Stabilize 11gR2 GI/RAC Cluster [ Note 1427855.1 ]




IBM Memory Compression - Active Memory Expansion (AME):
http://www.ibm.com/developerworks/aix/library/au-aix7memoryoptimize1/

Securing the Oracle Database - Kickstarter Resources

In this post, I have compiled Kick-Starter MOS Resources that can help you harden the Oracle Database, and make it more secure protecting it from potentially fatal intrusions/attacks:

• Security Checklist: 10 Basic Steps to Make Your Database Secure from Attacks (Doc ID 1545816.1)
• Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)
• Overview and Configuration of Oracle Network Encryption (Doc ID 76629.1)
• Oracle Database Security Checklist (Doc ID 1958932.1)
• Information Center: Using Database Security Products (Doc ID 1548954.2)
• All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption,
• OLS, Database Vault, Audit Vault [Document 207959.1]
• Database Privileges And Roles [Document 1347470.1]
• Oracle Database Authentication [Document 1349896.1]
• Oracle Database Auditing [Document 1299033.1]
• Oracle Database Vault [Document 1195205.1]
• Oracle Audit Vault [Document 1199033.1]
• Transparent Data Encryption (TDE) [Document 1228046.1]
• Kerberos Authentication [Document 1375853.1]
• Enterprise User Security (EUS) [Document 1376365.1]
• Oracle Virtual Private Database ( VPD / FGAC / RLS ) [Document 1352641.1]

Implementing LARGEPAGES for Oracle in AIX - A quick primer

This post is a quick pocketbook guide on how to implement LARGEPAGES (HUGEPAGES) for Oracle in AIX, a memory configuration that, can result in improved performance across the board for medium to large sized Oracle databases. Implementing LARGEPAGES is fairly simple but, will need a reboot of the AIX Server as well as the Oracle Database itself. The following commands show how to implement LARGEPAGES at 16MB - You have to calculate the amount of lgpg_regions by dividing the total number of bytes by 16777216 (16MB) = 3456 in this case = 54GB:
$ chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE root
$ lsuser -a capabilities root
$ chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
$ lsuser -a capabilities oracle
$ vmo -p -o lgpg_regions=3456 -o lgpg_size=16777216
SQL> alter system set use_large_pages=TRUE scope=spfile sid='*';
SQL> alter system set lock_sga=TRUE scope=spfile sid='*';
The following commands show how to check settings for LARGEPAGES in AIX?
vmo -L lgpg_size
lsuser -a capabilities oracle
vmo -l lru_file_repage
$ svmon
               size       inuse        free         pin     virtual   mmode
memory     29330148    29416756       42342    27409745    26782987     Ded
pg space   15649478      413636
               work        pers        clnt       other
pin        24552011           0        2008      849232
in use     26467765           0      232907
PageSize   PoolSize       inuse        pgsp         pin     virtual
s    4 KB         -     2431776      373298     1181576     2565099
m   64 KB         -      201562        4303      190125      209497
L   16 MB      3456        3410           0        3456        5131
S   16 GB         -           0           0           0           0

How to automatically purge Applied Archive Logs on Data Guard Standby Database?

In this quick post, i will show how to have RMAN automatically purge Applied Archive Logs on the Data Guard Standby Database in 11g onwards.
Prior to 11g:
SQL> alter system set "_log_deletion_policy"='ALL' scope=spfile;
Reboot the database.
11g onwards (Enhanced Feature-set):
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
The following MOS Notes serve as a good reference for the above:

• Configure RMAN to purge archivelogs after applied on standby (Doc ID 728053.1)
• RMAN Archived Redo Logs Are Deleted Before Being Applied at Standby Database (Doc ID 740322.1)
• RMAN backups in Max Performance/Max Availability Data Guard Environment (Doc ID 331924.1)
• Bug 6216036 : RMAN+DG ARCHIVELOG DELETION POLICY APPLIED ON STANDBY NOT RESPECTED

 

My new book coming out - Oracle Exadata Expert's Handbook

I am pleased to announce that my new book on Exadata is becoming available on Amazon very soon.


Considering that Exadata was the hot new kid on the Oracle block, I had been contemplating and reaching out to a lot of folks about writing a book on Exadata for over a year, before the stars got aligned and we started working on this project. From inception to writing to technical review to production, authoring a book is a complex, labor-intensive, lengthy, and at times painful process; - this project took more than 2+ years to complete and I along with my co-authors are very proud of the outcome.


You can order the book online at:


Amazon - Oracle Exadata Expert's Handbook


I have copied the Preface from the "Publisher's Website: Addison Wesley" to provide a brief overview to the Reader:

Oracle Exadata Expert's Handbook - Addison Wesley - Preface

Blazingly fast, Exadata is Oracle’s complete database machine—with unparalleled performance brought about by engineering hardware and software technologies from Oracle and Sun. Exadata has been widely embraced by enterprise users worldwide, including government, military, and corporate entities.

Authored by a world-renowned veteran author team of Oracle ACEs/ACE directors with a proven track record of multiple bestselling books and an active presence on the Oracle speaking circuit, this book is a blend of real-world, hands-on operations guide and expert handbook for Exadata Database Machine administrators (DMAs).

Targeted for Oracle Exadata DBAs and DMAs, this expert’s handbook is intended to serve as a practical, technical, go-to reference for performing administration operations and tasks for Oracle’s Exadata Database Machine. This book is a

Practical, technical guide for performing setup and administration of Oracle’s Exadata Database Machine

Expert, pro-level Exadata handbook

Real-world, hands-on Exadata operations guide

Expert deployment, management, administration, support, and monitoring guide and handbook

Practical, best-practices advice from real-life Exadata DMAs

The authors have written this handbook for an audience of intermediate-level, power, and expert users of the Exadata Database Machine.

This book covers both 11g and 12c versions of the underlying Exadata software.

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)

 

INITRANS and MAXTRANS in Oracle - Kickstarter Resources

The INITRANS parameter allows you to configure concurrent access to a block in Oracle, defining the initial number of Interested Transaction List (ITL). MAXTRANS defines the maximum value for concurrent access=255. In this post, i have compiled a list of MOS documents shedding light on INITRANS and MAXTRANS, how to configure them and so forth.

• INITRANS Relationship with DB_BLOCK_SIZE. (Doc ID 151473.1)
• Master Note: Oracle Transaction Management (Local) Overview (Doc ID 1506115.1)
• Create Index Initrans Does Not Allocate the Number Specified (Doc ID 729445.1)
• Unable To Increase Initrans For LOB Index Segment (Doc ID 1617125.1)
• How To Modify The Physical Attribute INITRANS For An Existing Table Or Index? (Doc ID 549074.1)
• How to Re-Organize a Table Online (Doc ID 177407.1)
• How To Modify The Physical Attribute INITRANS For An Existing Table Or Index? (Doc ID 549074.1)
• When a storage parameter such as; Initrans is modified DBA_TABLES view is not updated (Doc ID 1484309.1)
• DataPump Export/Import Generate Messages "The Value (30) Of Maxtrans Parameter Ignored" in Alert Log (Doc ID 455021.1)

Here is an example command on how to change the INITRANS for an existing table.

ALTER TABLE SCOTT.EMPTABLE INITRANS 64 MAXTRANS 255;

How to query SET undocumented (Hidden) INIT.ora parameters in Oracle?

This post is fairly simple - shows how to query the SET undocumented (Hidden) INIT.ora parameters in your Oracle database.  Each one of these undocumented parameters should only be set with Oracle's consent and approval. The following shows an example query.
SELECT name,description,value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_smu_debug_mode
<debug-flag> - set debug event for testing SMU operations
4
_ignore_desc_in_index
ignore DESC in indexes, sort those columns ascending anyhow
TRUE
_unnest_subquery
enables unnesting of complex subqueries
FALSE
_gby_hash_aggregation_enabled
enable group-by and aggregation using hash scheme
FALSE
_trace_files_public
Create publicly accessible trace files
FALSE

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)

Oracle Real Application Clusters (RAC): Jumbo Frames Notes/Recommendations

In this post, i shall quickly summarize recommendations for implementing Jumbo Frames on the Oracle Real Application Clusters (RAC) Private Cluster Interconnect.

Notes/Recommendations:

• It is recommended to implement Jumbo Frames for the RAC Cluster Interconnect for better overall performance.
• Interconnect Latency is significantly reduced by implementing Jumbo Frames.
• Implementing Jumbo Frames reduces the UDP, TCP, Ethernet overhead.
• The ifconfig -mtu 9000 shall implement Jumbo Frames with an MTU of 9000 (1500 for smaller frames).
• Test the RAC cluster thoroughly after configuring Jumbo Frames.

References: 

• Recommendation for the Real Application Cluster Interconnect and Jumbo Frames (Doc ID 341788.1)
• How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1)
• Problem - Dropped segments and bad checksums caused by Jumbo frames. (Doc ID 1073610.1)
• System experiences memory shortage when using nxge with jumbo frames and NFS (Doc ID 1353254.1)