Performance Issue Using QP: Bulk Import Of Price List (Doc ID 2469992.1)

Last updated on JANUARY 19, 2021

Applies to:

Symptoms

ACTUAL BEHAVIOR
QP : Bulk import of price list has poor Performance
It is taking more than 1 hour to complete

EXPECTED BEHAVIOR
Expects it to complete in 5-10 minutes as it did before a few days ago.

OBSERVATIONS
QP: Batch Size for Bulk Import = 5000 Site

Uses pattern search engine
QP: Pattern Search = B Site
QP: Pattern Search Path = 2 Site

Has pricing security enabled
QP: Security Control ON Site
QP: Security Default Maintain Privilege GLOBAL Site
QP: Security Default ViewOnly Privilege GLOBAL Site

Table Indexes

Table
Owner Table Name Index Name Index Type Last Analyzed Status
===== ============================== ============================== ========================= ================== ========
QP QP_LIST_LINES QP_LIST_LINES_N10 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N11 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N12 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N13 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N14 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N15 NORMAL 09-SEP-18 11:57:02 VALID
QP QP_LIST_LINES QP_LIST_LINES_N16 NORMAL 09-SEP-18 11:57:02 VALID
QP QP_LIST_LINES QP_LIST_LINES_N17 NORMAL 09-SEP-18 11:57:02 VALID
QP QP_LIST_LINES QP_LIST_LINES_N2 NORMAL 09-SEP-18 11:57:03 VALID
QP QP_LIST_LINES QP_LIST_LINES_N3 NORMAL 09-SEP-18 11:57:03 VALID
QP QP_LIST_LINES QP_LIST_LINES_N4 NORMAL 09-SEP-18 11:57:03 VALID
QP QP_LIST_LINES QP_LIST_LINES_N5 NORMAL 09-SEP-18 11:57:03 VALID
QP QP_LIST_LINES QP_LIST_LINES_N7 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N8 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_N9 NORMAL 09-SEP-18 11:57:01 VALID
QP QP_LIST_LINES QP_LIST_LINES_PK NORMAL 09-SEP-18 11:57:02 VALID
QP QP_LIST_LINES QP_LIST_LINES_U1 NORMAL 09-SEP-18 11:57:01 VALID

QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N1 NORMAL 09-SEP-18 11:57:05 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N10 NORMAL 09-SEP-18 11:57:07 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N11 NORMAL 09-SEP-18 11:57:08 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N12 NORMAL 09-SEP-18 11:57:08 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N13 NORMAL 09-SEP-18 11:57:08 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N14 NORMAL 09-SEP-18 11:57:08 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N15 NORMAL 09-SEP-18 11:57:09 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N2 NORMAL 09-SEP-18 11:57:06 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N3 NORMAL 09-SEP-18 11:57:05 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N4 NORMAL 09-SEP-18 11:57:06 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N5 NORMAL 09-SEP-18 11:57:06 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N6 NORMAL 09-SEP-18 11:57:06 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N7 NORMAL 09-SEP-18 11:57:07 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N8 NORMAL 09-SEP-18 11:57:07 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_N9 NORMAL 09-SEP-18 11:57:07 VALID
QP QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES_PK NORMAL 09-SEP-18 11:57:05 VALID

QP QP_RLTD_MODIFIERS QP_RLTD_MODIFIERS_N1 NORMAL 09-SEP-18 11:57:11 VALID
QP QP_RLTD_MODIFIERS QP_RLTD_MODIFIERS_N2 NORMAL 09-SEP-18 11:57:11 VALID
QP QP_RLTD_MODIFIERS QP_RLTD_MODIFIERS_N3 NORMAL 09-SEP-18 11:57:11 VALID
QP QP_RLTD_MODIFIERS QP_RLTD_MODIFIERS_PK NORMAL 09-SEP-18 11:57:11 VALID


Most expensive statement(s) shown:

STEPS
The issue can be reproduced at will with the following steps:
1. Populate QP interface tables
2. Gather statistics
3. Run bulk import of price lists

Changes

Cause

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