MSONWL64 Memory Based Planner 64 Bit Linux - SCO Plan Constrained (With Detailed Scheduling) Or Constrained (Without Detailed Scheduling) Performance Issue. Plan Will Not Complete (Doc ID 1304920.1)

Symptoms

On : 12.1.1 version, Constrained planning in UAT:

Users are on 12.1.1 CU4 patching level and key Linux 64 bit executable file is MSONWL64.ppc 120.3.12010000.42. In their PROD environment they currently run Classic Constrained Plans that run in about 2 hours total time. But now they're looking to run ASCP plans with plan option Constrained Mode = Constrained (With Detailed Scheduling) in the Constraints tab. However this plan does not finish even though they let it run for 15 hours. If they choose Constrained (Without Detailed Scheduling), it also runs and does not complete. The trace shows the following hanging sql:

SQL ID: 2tqd4wxg82v80
Plan Hash: 0
declare dummy boolean;begin fnd_profile.get_specific(:name, :userid, :respid,
:applid, :val, dummy, :org_id,:server_id);end;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 331 0.18 0.17 0 0 0 331
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 331 0.18 0.17 0 0 0 331

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 331 0.00 0.00
SQL*Net message from client 331 20729.71 21436.19

This looks exactly like Bug 9685198. However the fix for this bug is a one off specific for Sun 64 bit executable MSONWS64 and there's no Linux fix. Also there are no other consolidated update patches available for 12.1.1 after CU4.

EXPECTED BEHAVIOR
-----------------------
Expect the plan to complete in less than 4 hours

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Classic Constrained Plan which runs in about 2 hours
2. Set profile MSC: Enable Advanced Constants = Yes
3. Try running an ASCP plan with Constraints tab plan option = Constrained (With or Without Detailed Scheduling) and the plan will not complete

Cause

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