SA-278

Summary

After performing a PROD roll out, a customer has observed steadily declining performance of the overall PRPC environment.  The developer has obtained the system ALERT logs, as well as contacted the DBA to obtain a database report identifying slow performing SQL statements.

Error Messages

Users observe no errors on screen.  Pega logfiles demonstrate no errors specific to identifiable performance problems.

Steps to Reproduce

The poorly performing SQL statements appear during normal use of the PRPC environment.

Root Cause

Ultimately, two root causes were found:

1) Need for some additional table indexes.  Some SQL statements were found to need additional INDEXes to improve their performance.
2) Because of customer class name lengths (example: Customer-Business-European-Application1-Feature1), some PRPC properties defined in WHERE clauses were found to contain data that, when stored in the database, caused problems with the Oracle Optimizer and its selection of INDEXes to satisfy SQL statements. Because of the length of the property contents (string data), the Oracle Optimizer would reject the use of existing INDEXes, and resort to use of FULL TABLE SCANS (FTS).  This resulted in a significant impact to the overall environment - not just to the immediate SQL being executed.  This issue is referred to as the Oracle 32char Cardinality Problem.

Resolution

To resolve these issues, each SQL statement was analyzed (QTY:27), and recommendations were made for each SQL statement:

1) Provided developer with additional INDEX definitions for each SQL statement that was missing an INDEX to satisfy the WHERE clause statements.
2) Examine each SQL statement property list in the WHERE statements: examine each property's contents, and ensure that the 32char limitation for the Oracle Optimizer was not being impinged.  If so, provide developer with instructions to isolate the property, and remove its HISTOGRAM data.  This procedure involved four steps:

a) Delete the HISTOGRAM data on the Table/Property reference
b) Configure the DBMS to NOT compute HistoGram Data on the Table/Property reference.
c) Trigger the regeneration of Index Data on the Table
d) Update the Oracle Optimizer to grant more emphasis on the use of Indexes rather than performing Full Table Scans