Performance Caused by Code of MSC_MANAGE_PLAN_PARTITIONS AND MSC_ANALYSE_TABLES_PK (Doc ID 2239902.1)

Last updated on JUNE 28, 2017

Applies to:

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

Symptoms

Customer has VCP 12.2.5.

Two packages in VCP is causing performance issues for number of seeded and
custom programs in VCP ( the seeded programs are mostly related to ASCP
Plan):
Archive Plan Summary
Generate Plan Summary
Loader Worker With Dir
Memory Based Planner 6
Memory-Based Snapshot

Issue identified by DBA who is highly experienced and quite knowledgeable on
Oracle RDBMS.

DBA found the root cause of the poor performance is part of code in two
packages where he used ¿dbms_stats.auto_sample_size¿ instead of a
hard-coded 10 for the ¿percent¿ parameter when calling
fnd_stats.gather_table_stats

1. Package: MSC_MANAGE_PLAN_PARTITIONS

The two lines DBA changed in MSC_MANAGE_PLAN_PARTITIONS are
lines 1515 and 1520:
(in calls to ¿fnd_stats.gather_table_stats¿)
, from this:
...percent =>10,degree=>4)
to this:
...percent => dbms_stats.auto_sample_size,degree=>4);

2. Package: MSC_ANALYSE_TABLES_PK

The two lines we changed in MSC_ANALYSE_TABLES_PK are the lines that included
a hard-coded ¿10¿ in the calls to fnd_stats.gather table_stats,
from this:
...fnd_stats.gather_table_stats(v_applsys_schema,var_table_name,10,4);
to this:
...fnd_stats.gather_table_stats(v_applsys_schema,var_table_name,dbms_stats.aut
o_sample_size,4);

Customer needs official patch for the changes.

WORKAROUNDS
===========
a manual work-around; they can re-calculate statistics using
"auto_sample_size".

 



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