QP : Bulk Import of Price List has Poor Performance (Doc ID 2338951.1)

Last updated on NOVEMBER 13, 2019

Applies to:

Symptoms

ACTUAL BEHAVIOR

QP : Bulk import of price list has poor Performance

We have a critical business function for which we mass update price list lines of all selected / eligible Price List lines.
We are interfacing records to QP Interfaces tables and then calling QP : BULK Import to first UPDATE End Date to current date of all eligible Price List Lines.
We are then inserting new Price Lines lines with new price calling QP : BULK Import.

In our current performance instance we have hardware configuration as 30 CPUs and 64 GB Memory.
We are currently at below metric with that.

4237 Price List Header / 92143 Price list lines / 223908 Price List Attributes

# of Batches Duration of Update process Duration of Create process
10 6 minutes 51 minutes
20 30 minutes 25 minutes

So we now have a throughput of 1500 lines / min processed in performance instance.
We are also not getting a linear gain in throughput with the increase of batch size after increase of number of CPUs from to 30.
We have used Profile value of "QP : Batch Size" of 500. We have taken usual tuning steps like running GSS and truncating interface table before the runs.



EXPECTED BEHAVIOR
The business requirement is to complete the Price increase process of 1.2 M Price List lines in 2 hours.
Desires to process 10000 price list lines / minute.


STEPS
The issue can be reproduced at will with the following steps:

1. Populate QP interface tables
    4237 Price List Header / 92143 Price list lines / 223908 Price List Attributes
2. Gather statistics
3. Run bulk import of price lists

# of Batches Duration of Update process Duration of Create process
10 6 minutes 51 minutes
20 30 minutes 25 minutes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.