Support Article
BIX extract running very slow impacts performance in 6.3 SP1
SA-7808
Summary
User has a BIX job setup which runs every night. The BIX extract performance is very poor and inserts batch records which are 30 in 15 to 20 seconds.
Error Messages
No Error message as such, it is just taking time.
Steps to Reproduce
Run the BIX JOB with full data load.
Root Cause
The root cause of this problem is defect/misconfiguration in the PRPC operating environment.
Resolution
This issue is resolved by making the following change to the PRPC operating environment:
Some suggestions for how to improve the performance of this extraction.
1. Regarding the Oracle target database:
* Control files and data file must be located on different disks.
* Page Control files must be large enough to reduce log file switches.
* Data file in target DB should be pre-allocated enough space to hold all extracted data. As frequent auto extend of datafile has negative impact on performance.
2. Source and target databases must be in the same data center or minimize the number of hops across the network between the two databases.
3. Drop any constraints on indexes on the target tables.
4. Increase the batch size of the number of inserts into the target table before a commit. While increasing the batch size of the extraction will improve performance, there is always a tradeoff between memory and resources when we increase the batch size. When using a large batch size, the JDBC driver will keep those many statements (# of target tables * batch size * number of records per table) in memory. This is a huge memory consumption. This also means that if a single record in a batch fails, then the whole batch fails and reconciliation could be a big effort considering not all databases work the same way in notifying which record caused the issue.
5. If possible run two (or more) command line instances of BIX which will extract sub sets of the total data. This does mean that the extract rules must be able to divide the data equally (or almost) using filter conditions.
Published March 14, 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.