Search Results estimate_tiling_time

Overview

MDSYS.SDO_TUNE is the Oracle Spatial tuning and spatial statistics package shipped with the MDSYS schema as part of the base Oracle Database spatial option. Within Oracle E-Business Suite 12.1.1 and 12.2.2 it is installed and validated as a dependency of the SDO_GEOMETRY object model rather than as an EBS-owned application object. It carries no EBS business logic of its own; instead it serves the underlying spatial data infrastructure used by EBS components that store geometric information, notably Oracle Inventory, Oracle Warehouse Management, and any localization or ISV module that persists SDO_GEOMETRY columns for geographic coordinates, warehouse zone geometry, or delivery routing. The ETRM 12.2.2 classification marks the package as OTHER, reflecting that Oracle does not expose it as a public application programming interface for event or concurrent-process integration. It is fundamentally an administrative and diagnostic utility: its purpose is to help DBAs and spatial developers measure the quality of spatial indexing structures, estimate resource costs before creating or rebuilding a spatial index, and quantify the degradation of R-tree indexes over time as the DML workload grows. The package is declared VALID in the ETRM registry, confirming it compiles cleanly against the database version installed with the EBS technology stack.

Key Procedures and Functions

ETRM documents 25 procedures and functions in total; twelve of the principal entry points are catalogued below. None of the parameter lists are reproduced here.

• ESTIMATE_TILING_LEVEL — Estimates an appropriate tiling level for a spatial index before index creation, reducing trial-and-error sizing.
• ESTIMATE_TILING_TIME — Projects the elapsed time required to build a spatial index at a given tiling configuration.
• ESTIMATE_TOTAL_NUMTILES — Returns the number of tiles a spatial index would generate for the supplied geometry set, useful for capacity planning.
• ESTIMATE_INDEX_PERFORMANCE — Estimates expected index performance, allowing comparison of candidate indexing strategies.
• ESTIMATE_RTREE_INDEX_SIZE — Estimates the storage footprint of an R-tree spatial index prior to creation.
• EXTENT_OF — Returns the minimum bounding rectangle of a geometry, used for extent and envelope calculations.
• AVERAGE_MBR — Computes the average minimum bounding rectangle across a geometry layer, supporting tiling and partitioning decisions.
• HISTOGRAM_ANALYSIS — Produces a histogram of geometric distribution, exposing skew in spatial data.
• MIX_INFO — Reports on the mix of geometry types present in a layer, informing index strategy.
• QUALITY_DEGRADATION — Measures the degradation of an existing R-tree index as insert, update, and delete activity accumulates.
• ANALYZE_RTREE — Analyzes an R-tree index structure and reports on its internal statistics and quality.
• RTREE_QUALITY — Returns a quality metric for an R-tree index, indicating whether a rebuild or optimization is warranted.

The remaining procedures address related spatial statistics and index assessment tasks and are invoked under the same administrative circumstances.

Tables Accessed

Through EBS APPS synonyms the package is documented as referencing ALL_INDEXES, DBMS_OUTPUT, and PLITBLM. ALL_INDEXES is queried to obtain data dictionary information about existing spatial and R-tree indexes, which underpins the estimation and quality-reporting routines. DBMS_OUTPUT is the standard Oracle-supplied buffered output package; SDO_TUNE-directed diagnostic output is written here on request, requiring the caller to have enabled server output in the client session. PLITBLM is the classic PL/SQL internal table support unit used for associative-array handling. Beyond these, the package depends on MDSYS, SDO_GEOMETRY, and STANDARD, and is self-referenced within MDSYS schema; no other package is recorded as referencing it. It does not read or write any EBS application tables such as FND, INV, or WSH objects.

Usage Notes

SDO_TUNE is not invoked by delivered EBS forms, reports, or concurrent programs. It is called through SQL*Plus return-code scripts, SQL Developer worksheets, or custom PL/SQL procedures executed by a database administrator logged in with EXECUTE privilege on MDSYS.SDO_TUNE. Typical invocation windows are prior to initial creation of a spatial index, periodically to assess R-tree quality after significant geometry maintenance, and during performance investigations of spatial queries embedded in EBS customizations. Because output is directed to DBMS_OUTPUT, the calling session must enable server output. Results should be treated as guidance rather than as contractual commitments; the routines are estimation utilities, and any index rebuild triggered as a consequence must be scheduled during maintenance windows. Because the metadata available in ETRM is limited to dependency and naming information, administrators should consult the Oracle Spatial Developer's Guide for the exact parameter conventions before use.