Workbench Is Slow When Changing Lines and Waiting for Pegging To Display

(Doc ID 1948011.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

12.1.3.5 in Production

Notes for Update of 18-MAY-2015:

  1. In 12.1.3.5 the SQL ID reported to be slow is 0759rngjm76wj
  2. Also reported in VCP 12.1.3.9.1 - Same customer moved from 12.1.3.5 up to latest patch level and new SQL ID was reported as slow - 9upyh96sv6b2a
  3. See Patch information at the bottom of the solution for more information.


PROBLEM
-------------
After upgrade to 11.2.0.4 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.

SELECT 1
FROM
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
PHV reported
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
msc_supplies
msc_demands
msc_full_pegging
using
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

STEPS
--------
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.

EXPECTED BEHAVIOR
----------------------------
will move between lines almost immediately - in a couple of seconds at most

BUSINESS IMPACT
----------------------
Big user impact cannot move around and perform normal activities in the applications.

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