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 -
- Specifying the extract rules separated by comma
- prconfig changes to reduce the start-up time setting <env name="initialization/dictionary/EnableConclusionPreLoad" value="false” /> <env name="initialization/dictionary/EnableGlobalDeclarativeCachePreLoad" value="none” />
- 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
SELECT count(pzInsKey) FROM CUSTOM_WORK_HISTORY WHERE pzInsKey like :1 AND pxTimeCreated > :2 AND pxTimeCreated < :3
- 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).
- Use username and password options to avoid multiple executions of non-rule resolved extract rules.
- Apply new indexes for the columns - pxUpdateDateTime and pxTimeCreated corresponding to the Work and History classes respectively in your respective tables.
- 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.
Published January 31, 2016 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.