Search Results analyze_all




Overview
Explore in AI Chat

Overview

EDW_ANALYZE_TABLES is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite. Its name and structure indicate that it belongs to the Enterprise Data Warehouse (EDW) family of database objects, a collection of utilities used to support the EBS data warehouse and business intelligence reporting layers. The package is defined with AUTHID CURRENT_USER, meaning that its procedures execute with the privileges of the calling user rather than with the privileges of the package owner. This is significant from a security and administration standpoint, because it allows the package to be invoked in a context where the caller must already possess the necessary object privileges.

The package header (EDWANLZS.pls, version 115.5, last shipped 2002/12/06) is deliberately minimal, exposing a single procedure. Its purpose, as reflected in the name, is to gather optimizer statistics on database tables used by the EDW environment. Statistics gathering is a routine maintenance activity that keeps the Oracle cost-based optimizer supplied with accurate information about table and index data distributions, which in turn supports efficient execution plans for the queries that populate EDW materialized views and reports.

Key Procedures and Functions

  • ANALYZE_ALL — The only documented procedure in the package. It accepts two standard OUT parameters, Errbuf and Retcode, which are the conventional interfacing parameters used by Oracle EBS concurrent programs to return error messages and a completion status code to the Concurrent Manager. The procedure is intended to drive statistics collection across the relevant set of EDW tables in a single call, which is why it is named "Analyze_All" — the "all" scope is reflected in the ETRM documentation and matches the user search term "analyze_all".

No functions or additional overloaded procedures are documented in the package specification.

Tables Accessed

The ETRM metadata for this package lists no tables referenced via APPS synonyms. This is expected for a statistics-gathering utility: the procedure typically operates dynamically, issuing DBMS_STATS or ANALYZE calls against table names assembled at runtime, or against tables identified by querying the data dictionary. Because table names are resolved dynamically rather than through static SQL statements, the dependency information captured by ETRM does not enumerate the underlying tables. Consequently, no fixed read/write table list can be attributed to this package from the documented metadata alone.

Usage Notes

  • Because the procedure signature matches the standard EBS concurrent program convention (Errbuf and Retcode OUT parameters), EDW_ANALYZE_TABLES.ANALYZE_ALL is almost certainly registered as the executable for a concurrent program in the EDW or System Administrator responsibility. It would be scheduled to run periodically, for example during a maintenance window, to refresh optimizer statistics.
  • The application may also be invoked directly from custom PL/SQL or from SQL*Plus by a DBA, provided the executing user holds the required privileges, since AUTHID CURRENT_USER transfers privilege checking to the caller.
  • The ETRM metadata records that the package is referenced by zero other packages, confirming it is a top-level utility with no internal callers within the documented EBS package set.
  • Given the header date (2002) and the 115.5 version, the package is stable legacy code carried forward into EBS 12.1.1 and 12.2.2; administrators should verify that statistics collection remains consistent with current DBMS_STATS preferences rather than relying solely on the original ANALYZE semantics.
  • As with any statistics utility, it should be scheduled carefully to avoid contention with heavy EDW load periods, and its results monitored to confirm the optimizer is receiving fresh statistics.