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

Last updated on JANUARY 23, 2023

Applies to:

Symptoms

Customer has VCP 12.2.5.

Two packages in VCP are causing performance issues for number of seeded and custom programs in VCP (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 value of 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 value of 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.auto_sample_size,4)

Customer needs official patch for the changes.

WORKAROUNDS:
As manual work-around, they can re-calculate statistics using auto_sample_size.

Changes

 <Bug 24653389> CODE OF MSC_MANAGE_PLAN_PARTITIONS AND MSC_ANALYSE_TABLES_PK

Cause

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