Search Results dbms_sqltune_lib




Overview
Explore in AI Chat

Overview

SYS.DBMS_SQLTUNE_UTIL0 is an internal Oracle Database package body that supports the SQL Tuning Advisor and SQL Tuning Set infrastructure. In Oracle E-Business Suite 12.1.1 and 12.2.2, it appears in the ETRM (E-Business Suite Technical Reference Manual) inventory because EBS schemas — principally APPS — hold privileges that allow execution of SQL Tuning Advisor functionality through the DBMS_SQLTUNE family of packages. The object is not an EBS application API; it is a SYS-owned implementation package, classified in ETRM under the generic API classification OTHER.

Its role is to provide the low-level utility layer beneath DBMS_SQLTUNE and DBMS_SQLTUNE_LIB. Where DBMS_SQLTUNE exposes the public tuning interface, DBMS_SQLTUNE_UTIL0 supplies the internal primitives used to identify SQL statements, parse and mask bind variables, and resolve container database identifiers. These primitives are consumed by the SQL Tuning Advisor, the Automatic SQL Tuning component of the Automatic Workload Repository, and SQL Plan Management when they must recognize and compare SQL text.

Key Procedures and Functions

The ETRM metadata documents thirteen procedures and functions within this package body. Their purposes are as follows:

  • SQLTEXT_TO_SIGNATURE — derives the SQL signature, a normalized hash of SQL text used to group logically identical statements that differ only in literal values.
  • SQLTEXT_TO_SQLID — computes the SQL identifier for a supplied SQL text, producing the key by which a statement is registered in tuning repositories.
  • VALIDATE_SQLID — confirms that a given SQL identifier is well formed and corresponds to a recognized statement.
  • EXTRACT_BIND — extracts a single bind variable occurrence from a parsed SQL statement.
  • EXTRACT_BINDS — extracts the complete set of bind variables captured for a statement.
  • IS_BIND_MASKED — reports whether a bind variable has been masked, supporting the bind-masking feature used to hide sensitive literal values.
  • GET_BINDS_COUNT — returns the number of bind variables associated with a statement.
  • CDBCON_DBID_TO_NAME — resolves a container database identifier to its database name.
  • CDBCON_ID_TO_DBID — maps an internal container identifier to its database identifier.
  • CDBCON_NAME2IDS — resolves a container database name to its associated identifiers.
  • CDB_IS_ROOT — indicates whether the current container is the root container of a multitenant database.
  • CDB_IS_PDB — indicates whether the current container is a pluggable database.

Tables Accessed

The ETRM dependency extract for this package body documents no direct references to EBS application tables through APPS synonyms. Its declared dependencies are other SYS-owned objects: the DBMS_SQLTUNE_LIB package, the SQL_BIND and SQL_BIND_SET types, and the STANDARD package. Bind metadata and SQL identifiers are therefore materialized through these PL/SQL types and the SQL tuning repository tables managed by the higher-level DBMS_SQLTUNE packages, rather than through EBS transactional tables.

Usage Notes

DBMS_SQLTUNE_UTIL0 is not intended for direct invocation by EBS application code. The ETRM metadata records that it references no database object beyond its own dependency chain and is itself referenced by fourteen other packages, reflecting its position as an internal utility rather than a public entry point.

In practice it is reached indirectly. Oracle EBS administrators invoke the SQL Tuning Advisor through Enterprise Manager, through the DBMS_SQLTUNE API, or through the SQL Tuning Set and SQL Plan Management workflows that EBS tuning recommendations rely upon. Automatic SQL Tuning jobs scheduled in the maintenance window likewise traverse this layer. Custom code should call DBMS_SQLTUNE rather than DBMS_SQLTUNE_UTIL0, since the internal package is undocumented, unsupported for direct use, and subject to change across database releases.