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.
184.108.40.206 in Production
Notes for Update of 18-MAY-2015:
- In 220.127.116.11 the SQL ID reported to be slow is 0759rngjm76wj
- Also reported in VCP 18.104.22.168.1 - Same customer moved from 22.214.171.124 up to latest patch level and new SQL ID was reported as slow - 9upyh96sv6b2a
- See Patch information at the bottom of the solution for more information.
After upgrade to 126.96.36.199 Plus PSU1 on 04-OCT -- The workbench is very slow.
Moving from one line down to the next line is taking more than 30 seconds.
MSC_FULL_PEGGING WHERE PLAN_ID = :b1 AND TRANSACTION_ID = :b2
They rebuilt the indexes, but this did not help.
Then in SQLT - sqlt_s33705_main.html
we see two plans for this SQL and the one marked as worst performance is the one that uses the N3 index
3185041944 [W] - avg elapsed 3.184 -- this uses the N3 index
1909992140 [B] - avg elapsed - 1.309 -- this uses N1 index
And on 22-OCT - we implemented PHV 1909992140 via SQL profile so this would always choose the best Plan 1909992140 to execute.
Also had them start running Gather Table Stats on key tables each day
We ran Gather Table Stats @100 % on
Table owner - MSC
table name - [table_name]
estimate Percent - 100
parallel degree - 4
partition name (null)
backup flag - NOBACKUP
granularity - ALL
history mode - LASTRUN
Invalidate dependent cursors - Y
Summary on 13-NOV
The above has improved performance but it is still slower than it was before the upgrade.
1. Changing focus between supply records takes 8-12 secs
2. Click on Supply Pegging or Demand Pegging Icon on PWB while on a supply records takes again 8-12 secs
3. Closing and opening the workbench is taking 8-12 secs
open workbench and query supply/demand
Then try moving from one line down to the next line or other standard steps like checking pegging, select record for release, etc.
will move between lines almost immediately - in a couple of seconds at most
Big user impact cannot move around and perform normal activities in the applications.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms