Performance Issues When Creating Simple Promotion (Doc ID 2483215.1)

Last updated on APRIL 18, 2019

Applies to:

Symptoms

After creating a simple promotion in Retail Price Management (RPM) conflict check is running for more than 2 days without ending.

Zone and location characteristics are as below:

select count(*) from rpm_item_loc where item in (select item from item_master where dept in (X,Y);
--14456033
select count(*) from rpm_zone_location where zone_id=Z;
--510

Database Specs:
sga_max_size | 20G
pga_aggregate_limit | 12032M
sga_target | 0
memory_target | 16G
memory_max_target | 20G
pga_aggregate_target | 0

Recalculating or deleting stats on RPM_BULK_CC*, did not showed visible improvement.

The issue can be reproduced at will with the following steps:

1. Log in to RPM.
2. Select Promotions -> Create Promotion.
3. Enter data in promo header.
4. Select Add Component.
5. Enter data in promotion component, Component Type -> Simple.
6. Press Next.
7. Select two departments X,Y. Item level = Merchandise Hierarchy.
8. Press Select.
9. In location zone select one zone - Z
10. Enter promo detail, Change Type = Percent off, Change Percent =20, Apply to= Regular and Clearance.
11. Select Apply.
12. Press Select All.
13. Form Action select Approve and press Update button.
14. On the informing screen press OK.

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.