Search Results analyse_table




Overview
Explore in AI Chat

Overview

MSC_ANALYSE_TABLES_PK is a utility package owned by the APPS schema in Oracle E-Business Suite, shipped as part of the Advanced Supply Chain Planning (ASCP) module. Its source header identifier, MSCANTBS.pls, version 115.5, places it within the MSC (Material Planning / Supply Chain) product family. The package is declared with AUTHID CURRENT_USER, meaning its SQL statements execute with the privileges of the calling user rather than the definer, which is significant because it accesses the data dictionary view ALL_TABLES.

The package exists to gather optimizer statistics on planning-related tables. In an ASCP environment, the MSC schema contains large collections of planning data — supplies, demands, item and resource planning tables — whose statistics can become stale and lead the Cost-Based Optimizer (CBO) to generate poor execution plans. Because the SQL was compiled around the Oracle 8i/9i era (the "$Header" comment reflects a 2002 revision), explicit ANALYZE TABLE ... COMPUTE STATISTICS style operations were the standard mechanism before DBMS_STATS became the preferred approach. MSC_ANALYSE_TABLES_PK provides a controlled, packaged entry point for this maintenance activity rather than requiring DBAs to issue manual DDL.

Key Procedures and Functions

The documented public interface exposes two procedures:

  • ANALYSE — The package-level driver. It performs statistics analysis across the set of tables relevant to the planning schema, without requiring the caller to name individual tables. It is the procedure most likely to be invoked from a concurrent program or an administrative script covering the full planning table list.
  • ANALYSE_TABLE — A targeted procedure accepting a table name plus optional instance and plan identifiers. The presence of p_instance_id and p_plan_id parameters indicates that analysis can be scoped to data belonging to a specific planning instance or plan, rather than the entire table. The table name parameter is mandatory, while the instance and plan parameters default to NULL, allowing a caller to analyse the table holistically or restrict the operation to a particular planning context. The literal parameters supplied in the package specification are not reproduced here; only the documented intent.

Both procedures are declared PUBLIC in the specification, making them callable from SQL*Plus, concurrent program definitions, or custom PL/SQL.

Tables Accessed

The package references a single documented table via APPS synonyms: ALL_TABLES. ALL_TABLES is an Oracle data dictionary view listing every table visible to the current user, including ownership, table name, and statistics-related columns. The package uses it to resolve and iterate over the target planning tables before issuing analysis operations. Because the package runs with AUTHID CURRENT_USER, the ALL_TABLES query returns only those tables the invoking user is entitled to see, which naturally constrains the scope of analysis to accessible objects. No application data tables are documented as directly read or written by this package itself; the actual tables subjected to analysis by ANALYSE_TABLE are named dynamically at runtime.

Usage Notes

This package is an administrative utility rather than a transactional API. It is typically invoked in the following circumstances:

  • As a scheduled concurrent program or DBA maintenance script during periods of low planning activity, to refresh statistics on MSC planning tables so the CBO produces efficient execution plans.
  • From SQL*Plus or a wrapper script during troubleshooting, when a planner or DBA suspects stale statistics are causing long-running planning engine queries. Calling ANALYSE_TABLE with a specific table name allows targeted remediation without a full-schema analysis.
  • By other PL/SQL code: ETRM metadata records that MSC_ANALYSE_TABLES_PK is referenced by eleven other packages, indicating it serves as a shared low-level utility within the MSC codebase.

Because the package performs statistics gathering, it should not be executed during critical planning runs; the operation is resource-intensive on large planning tables. In modern EBS 12.1.1 and 12.2.2 environments, DBMS_STATS is generally preferred, and this older package may be retained for backward compatibility. Confirmation of behaviour should be validated against the actual package body in the specific instance, as only the specification is preserved in the documented metadata.