Search Results msc_analyse_tables_pk




Overview
Explore in AI Chat

Overview

MSC_ANALYSE_TABLES_PK is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema. It is a component of the Advanced Supply Chain Planning (ASCP) module and provides the database-side machinery for gathering optimizer statistics on the planning tables that make up the MSC schema. In EBS 12.1.1 and 12.2.2, ongoing statistics maintenance is essential: the Oracle cost-based optimizer depends on current object statistics to produce efficient execution plans for the large, densely partitioned tables used by the planning engine. This package encapsulates the logic that identifies candidate tables and invokes the statistics collection utility against them, so that planner performance remains predictable as data volumes change.

The object is documented as VALID in the ETRM repository and its API classification is recorded as OTHER, meaning it is a private, internal utility rather than a published open interface. It is not referenced by any other database object, confirming that it acts as a top-level internal driver rather than a shared library routine.

Key Procedures and Functions

The documented package exposes two program units:

  • ANALYSE — The driver procedure. It coordinates the statistics-gathering activity for the planning schema, determining which objects require analysis and orchestrating the calls that perform the work. It is the entry point that callers invoke when a full analysis run is required.
  • ANALYSE_TABLE — The single-object worker. It performs the analysis for an individual table, providing the granular operation that the driver procedure invokes repeatedly. Separating this logic allows the package to target a specific table when a focused analysis is needed rather than processing the entire set.

Both procedures are internal utilities; no public parameter lists are published in the ETRM metadata, and the units are intended for invocation by the planning application rather than by end users or external integrations.

Tables Accessed

The package works against the ALL_TABLES data dictionary view. ALL_TABLES is used to enumerate the tables visible to the APPS schema so that the analysis routines can determine which objects exist and are eligible for statistics collection. This reflects the package's role as a discovery-and-execution layer: it reads metadata about tables, then hands the identified objects to the underlying statistics engine.

Statistics collection itself is delegated to FND_STATS, the standard EBS utility for gathering and managing optimizer statistics. FND_INSTALLATION is also referenced, indicating that the package checks installation or patch state before proceeding, a common safeguard in EBS utilities to ensure statistics are applied only in a fully installed environment. MSC_MANAGE_PLAN_PARTITIONS is referenced as well, which is consistent with the partitioned nature of the MSC planning tables and the need to analyse partitions correctly. STANDARD supplies the core PL/SQL built-ins used throughout the body.

Usage Notes

MSC_ANALYSE_TABLES_PK is invoked indirectly through the Advanced Supply Chain Planning statistics maintenance flow rather than from a user-facing form. In practice it is executed by concurrent programs and internal planning engine routines that schedule periodic statistics refreshes, and it may also be called by database administrators or custom maintenance scripts that need to analyse MSC planning tables on demand. Because it depends on FND_STATS and FND_INSTALLATION, it must run with the APPS schema context and in an environment where the EBS foundation packages are valid. When tuning planner performance on 12.1.1 or 12.2.2, this package is a relevant object to check for validity and to understand how statistics are being refreshed.