My Oracle Support Banner

PATTERN ENGINE LIMITATION Using Qualifiers Defined as Source Type = PL/SQL API Multi-Record (Doc ID 1642322.1)

Last updated on MARCH 12, 2018

Applies to:

Oracle Advanced Pricing - Version 11.5.10 to 12.2.3 [Release 11.5 to 12.2]
Information in this document applies to any platform.
Advanced Pricing Pattern Engine

Symptoms

On : 11.5.10.2 version, Reports & Concurrrent Requests

ACTUAL BEHAVIOR
---------------
Order Entry has severe performance issues while entering lines when there are pricing engine calls.

The following query generates 3Gb of Undo Table Space


SQL_TEXT
----------------------------------------------------------------

INSERT INTO QP_NPREQ_LINE_PATRNS_TMP(LINE_INDEX, PATTERN_ID, HAS
H_KEY) SELECT /*+ ORDERED index(attr qp_preq_line_attrs_tmp_n7)
*/ ATTR.LINE_INDEX, QPP.PATTERN_ID, SYS_CONNECT_BY_PATH(ATTR.VAL
UE_FROM, '|') HASH_KEY FROM (SELECT /*+ index(qpp1 qp_pattern_ph
ases_n1) */ * FROM QP_NPREQ_PATRN_PHASES_TMP QPP1 WHERE QPP1.PRI
CING_PHASE_ID = :B1 ) QPP, QP_NPREQ_LINE_ATTRS_TMP ATTR WHERE AT
TR.SEGMENT_ID = QPP.SEGMENT_ID AND ATTR.SEGMENT_ID IS NOT NULL A
ND LEVEL = QPP.SEGMENT_COUNT START WITH QPP.SEGMENT_INDEX = 1 CO
NNECT BY PRIOR QPP.SEGMENT_INDEX = QPP.SEGMENT_INDEX - 1 AND PRI
OR QPP.PATTERN_ID = QPP.PATTERN_ID AND PRIOR ATTR.LINE_INDEX = A
TTR.LINE_INDEX



EXPECTED BEHAVIOR
-----------------------
Not to generate huge temp space and performance to be very fast.


DISCOVERED
----------------

The pattern that is causing the problem centers around segment_id 100017 which maps to the buying group.
The first 8 segments are for that segment_id. The customers exhibiting the problems are assigned to multiple buying groups.
In this test customer, it is assigned to 7 buying groups, 6 of which have context with these qualifiers.
It appears that the combination of 11 segments, 8 of which are buying groups combined with a customer having 6 buying groups
to be considered is causing this massive result on the connect by.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Order Management Superuser
2. Navigate to: Orders, Returns > Enter Orders

 

NOTES
--------

Has already applied <patch 7364167> - MODIFIERS DO NOT WORK WHEN THE SAME 'NOT =' CONDITION QUALIFIERS ARE DUPLICATED

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, the order entry and shipping is completely stopped.

Cause

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
Symptoms
Cause
Solution
References


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