Poor Performance for Custom Price Book Using API QP_PREQ_PUB.PRICE_REQUEST (Doc ID 1955793.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Order Management - Version 12.1.3 and later
Oracle Advanced Pricing - Version 12.1.3 and later
Information in this document applies to any platform.
QP_PREQ_PUB.PRICE_REQUEST
qp_preq_pub.price_request
PRICE_REQUEST
price_request
"QP_PREQ_PUB.PRICE_REQUEST performance"
"pricing engine performance"

Symptoms

On : 12.1.3 version, Formula Screen and API

ACTUAL BEHAVIOR
---------------
Pricing engine performance is too slow when processing line for our price books.

Customer is producing a custom price book using the API procedure called QP_PREQ_PUB.PRICE_REQUEST.
The developer has been running the price book in full pricing mode over the last couple of days and the pricing is fairly slow.

Three  pricing calls per second is what he is seeing and some of the catalogs have 500+ pricing calls to make.
While monitoring the catalog generation (via toad session monitor) they have noticed that the pricing seems to be spending a lot of time on one particular statement:
 

We searched My Oracle Support (MOS) and found a match for this issue in 11i but not in R12. I have provided his explain plans for the process with the issue and then after he implemented the changes in R12 test environment temporarily to see the difference. Please review the documentation to see if this this 11i solution should be ported to R12 OR if there is something else that should be done.

We will be producing customer specific pricing catalogs for thousands of customers and 3 prices per second isn't quick enough.

EXPECTED BEHAVIOR
-----------------------
Expect pricing performance to be better.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Oracle Pricing Manager
2. Reports.
3. Submit MBC Pricing Catalog using parameters:
DivisionL 01
Catalog: RI
Effective Date: null is today
Default Price List: RIGHT HAND
Customer: null
Agreement: null
Mini-Region: 0002
Get Price?: Yes

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms