Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

BIX job performance degradation after moving to Oracle RAC

SA-9263

Summary



Users are seeing major performance degradation in BIX job after moving from Oracle Standalone database to Oracle RAC. The job was running fine before upgrading to Oracle RAC.

Below are the timings of the BIX job run -
Total Time JOB Taken to be complete (Before migration to RAC ) 1:45:19
Total Time JOB Taken to be complete (After migration to RAC 1st day) 3:07:18
Total Time JOB Taken to be complete (After migration to RAC 2nd day) 2:31:18
Total Time JOB Taken to be complete (After migration to RAC 3rd day) 2:40:37

Are there any configuration changes that needs to be made after moving to Oracle RAC for the BIX job to run without performance issues? Currently, users were facing slowness issues after they have upgraded the database, the job was delaying than the SLA with lesser data. The job is running fine without any failures. But users are in risk of losing SLAs on a day if they process high load of data.

Error Messages



Not Applicable

Steps to Reproduce



Run BIX job on standalone database with high volume of data -> Run BIX job on RAC database with high volume of data -> Compare results

Root Cause



Couple of SQLs in AWR report indicate tuning.

Resolution



There were no configuration changes required in general after moving from standalone Oracle database to Oracle RAC for BIX to work properly. Following best practices were shared with user which could help improve performance of BIX processing -

 
  1.    Specifying the extract rules separated by comma
  2. prconfig changes to reduce the start-up time setting <env name="initialization/dictionary/EnableConclusionPreLoad" value="false” /> <env name="initialization/dictionary/EnableGlobalDeclarativeCachePreLoad" value="none” />
  3. There were couple of SQLs identified in AWR which indicates that some tuning is required on the database level (build statistics and check if they are up to date, appropriate indexes are added and used) –
SELECT pzPVStream FROM CUSTOM_WORK_HISTORY WHERE pzInsKey like :1 AND pxTimeCreated > :2 AND pxTimeCreated < :3 ORDER BY pzInsKey
SELECT count(pzInsKey) FROM CUSTOM_WORK_HISTORY WHERE pzInsKey like :1 AND pxTimeCreated > :2 AND pxTimeCreated < :3

 
  1. Try increasing JVM memory and batch size. Perform tests in development by increasing the JVM memory and batch size (example: setting batch size to 100 and JVM heap settings to -Xms512m -Xmx768m).
  2. Use username and password options to avoid multiple executions of non-rule resolved extract rules.
  3. Apply new indexes for the columns - pxUpdateDateTime and pxTimeCreated corresponding to the Work and History classes respectively in your respective tables.
  4. To avoid network latency, source PRPC database server and the BIX server (from where BIX extraction process runs) should be co-located in same data center.
 
 
Suggest Edit

Published January 31, 2016 - Updated December 2, 2021

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us