After Applying 12.1.3.9.1 - Memory Based Snapshot Performance Is Very Bad

(Doc ID 1673643.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

PROBLEM
-------------
After applying the 12.1.3.9.1 ASCP RUP - a new issue raises with performance. when plan runs for all planned items, around 13 hours is needed which is really too long.
We run gather schema stats for all schema's at 50% and around 8 hours is needed for plan runs.

Request ID Program Name Elapsed Time
7165704 Memory Based Snapshot 64 bit Linux 1,293.48

TKPROF shows problem with

SELECT /*+ ordered */ mscd.demand_id,
  mscd.inventory_item_id inventory_item_id,
  mscd.organization_id organization_id,
  mscd.sr_instance_id sr_instance_id,
  NVL(sys.product_family_id, -23453),
....
AND ((:snapshot_mode = 4
  and items.netchange_replan_flag = 1)
 OR (:snapshot_mode = 1))
ORDER BY USING_ASSEMBLY_DEMAND_DATE,inventory_item_id,organization_id,sr_instance_id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.06 0 0 0 0
Execute 1 0.50 0.54 0 0 0 0
Fetch 4860 64871.07 70454.86 863025 3320371375 11 485869
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4862 64871.63 70455.47 863025 3320371375 11 485869

and explain plan shows the following 3 times
Rows (1st) Rows (avg) Rows (max) Row Source Operation
1832567352 1832567352 1832567352 HASH JOIN

STEPS
--------
Run plan after applying the patch

EXPECTED BEHAVIOR
----------------------------
plan would run in normal timing, not be increased

BUSINESS IMPACT
----------------------
cannot use this patch in PROD

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