Pricing Engine CURSOR L_SEC_LIST_CUR_CURRENCY Performance Issue

(Doc ID 2226295.1)

Last updated on OCTOBER 05, 2017

Applies to:

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

Symptoms

On : 12.2.5 version, Pricing Engine

ACTUAL BEHAVIOR
---------------
QP engine CURSOR L_SEC_LIST_CUR_CURRENCY performance issue

Plan to go live with upgrade from 11i to R12 at the beginning of Feb 2017. In our tests in the upgraded R12 test env have noticed considerable performance degradation during order entry as compared to 11i instances on the same exact database and apps server infrastructure.

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 ta 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.

EXPECTED BEHAVIOR
-----------------------
Expect performance to be as good as in 11i.

STEPS
-----------------------
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.

EXPENSIVE STATEMENT
-------------------------

 

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