My Oracle Support Banner

Pricing Engine CURSOR L_SEC_LIST_CUR_CURRENCY Performance Issue (Doc ID 2226295.1)

Last updated on FEBRUARY 21, 2019

Applies to:

Oracle Advanced Pricing - Version 12.2.5 and later
Information in this document applies to any platform.
QP Engine - pricing engine


On : 12.2.5 version, Pricing Engine

QP engine CURSOR L_SEC_LIST_CUR_CURRENCY performance issue

Have tracked the issue to the package QP_PREQ_GRP cursor L_SEC_LIST_CUR_CURRENCY. And further determined that since there is ORDERD hint but the order of the tables is NOT optimal and in fact the order of table listed in the 4 unions that make up this cursor is a lot more efficient. The same cursor in 11i when entering an order with 6 lines to around 0.64 seconds with 149,196 reads, where as for the same exact order in R12 it takes 8.91 seconds with 3,337,124 reads (we have attached trace files for 11i and R12). Now fixing the hints in the R12 and also ordering the tables to match 11i code results in a much improved performance. After the fix and entering the same exact order the cursor takes 0.57 seconds with only 137,018 reads.

Expect performance to be as good as in 11i.

The issue can be reproduced at will with the following steps:
1. Order Management Super User
2. Orders, Returns
3. Sales Order
4. Enter an order with 6 lines.
5. Performance is 9 seconds versus 1 second in 11i.




To view full details, sign in with your My Oracle Support account.

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

In this Document

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