Search Results dbms_sqltune_util1

Overview

SYS.DBMS_SQLTUNE_UTIL1 is an internal Oracle Database PL/SQL package that forms part of the SQL Tuning Advisor and SQL Plan Management (SPM) infrastructure. It is owned by the SYS schema and holds a VALID status in both Oracle E-Business Suite 12.1.1 and 12.2.2 environments. Although EBS does not invoke this package directly, its functionality underpins many of the database performance utilities that EBS administrators rely upon, including SQL Tuning Advisor, SQL Access Advisor, SQL Performance Analyzer, and SQL Plan Management.

The package provides low-level helper routines used by the higher-level public packages DBMS_SQLTUNE, DBMS_SPM, DBMS_SQLPA, DBMS_SQLDIAG, and DBMS_AUTO_REPORT_INTERNAL. Its responsibilities include resolving SQL set identifiers, validating tuning task status, retrieving view text, managing session parameter changes, and manipulating the workload objects associated with SQL Performance Analyzer and SQL Tuning Set operations. Within EBS, the Automatic Workload Repository (AWR) and Automatic SQL Tuning components leverage this layer whenever scheduled advisor jobs execute in the background.

Key Procedures and Functions

The ETRM documentation lists fifteen documented program units within this package. Their functions include:

GET_SQLSET_IDENTIFIER — returns the identifier of a named SQL Tuning Set.
GET_SQLSET_CON_DBID — retrieves the database identifier associated with a SQL set connection context.
GET_SQLSET_NB_STMTS — obtains the number of statements contained in a SQL Tuning Set.
GET_VIEW_TEXT — returns the text of a database view used by the tuning infrastructure.
VALIDATE_TASK_STATUS — verifies that a tuning task is in a state appropriate for the requested operation.
GET_EXECUTION_TYPE — determines the execution type associated with a tuning or SPA task.
INIT_TASK_WKLDOBJ — initialises the workload object for a tuning task.
INIT_TASK_SPAOBJ — initialises the SQL Performance Analyzer object for a task.
GET_WKLDTYPE_NAME — returns the descriptive name of a workload type.
VALIDATE_NAME — validates a supplied object or task name.
ALTER_SESSION_PARAMETER — temporarily modifies a session-level parameter during advisor execution.
RESTORE_SESSION_PARAMETER — restores a previously altered session parameter.
GET_CURRENT_TIME — returns the current database time for task bookkeeping.
GET_DBID_FROM_CONID — derives a database identifier from a connection identifier.
IS_RUNNING_FAKE_CC_TEST — indicates whether a simulated consolidation/concurrency test is running.

Tables Accessed

The ETRM metadata records no direct table references through APPS synonyms for this package. It operates primarily against SYS-owned dictionary and AWR repository views and tables, including those whose owners appear in the dependency list: WRI$_ADV_SQLTUNE, WRI$_REPT_SPMEVOLVE, WRI$_REPT_SQLPI, and WRI$_REPT_SQLT. These store tuning task definitions, SQL Plan Management evolution results, SQL Performance Analyzer reports, and SQL Tuning Set data respectively.

Usage Notes

SYS.DBMS_SQLTUNE_UTIL1 is not intended for direct invocation by EBS developers or DBAs. It is referenced by sixteen other packages, including DBMS_SQLTUNE_INTERNAL, DBMS_SQLTUNE_UTIL2, DBMS_SPM_INTERNAL, PRVT_SQLPA, PRVT_SQLADV_INFRA, and PRVT_SQLSET_INFRA. Within EBS 12.1.1 and 12.2.2 these packages are exercised by the Automatic SQL Tuning Advisor and related scheduled jobs, as well as by the Database Administrator responsibilities accessed via Oracle Applications Manager (OAM). Administrators interacting with the SQL Tuning Advisor or SQL Plan Management through OAM or Enterprise Manager are, indirectly, driving procedures within this package. Custom code should call the documented public APIs such as DBMS_SQLTUNE and DBMS_SPM rather than this internal package, since Oracle does not guarantee backward compatibility for internal interfaces.

PACKAGE: SYS.DBMS_SQLTUNE_UTIL1 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL1 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL1 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL1 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL1, status:VALID,
LIBRARY: SYS.DBMS_SQLTUNE_LIB 12.1.1
owner:SYS, object_type:LIBRARY, object_name:DBMS_SQLTUNE_LIB, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLSET_INFRA 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLSET_INFRA, status:VALID,
PACKAGE: SYS.DBMS_SPM_INTERNAL 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SPM_INTERNAL, status:VALID,
PACKAGE: SYS.DBMS_SPM_INTERNAL 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SPM_INTERNAL, status:VALID,
TYPE BODY: SYS.WRI$_ADV_SQLTUNE 12.2.2
owner:SYS, object_type:TYPE BODY, object_name:WRI$_ADV_SQLTUNE, status:VALID,
PACKAGE: SYS.PRVT_SQLPA 12.1.1
owner:SYS, object_type:PACKAGE, object_name:PRVT_SQLPA, status:VALID,
PACKAGE BODY: SYS.DBMS_AUTO_REPORT_INTERNAL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_AUTO_REPORT_INTERNAL, status:VALID,
TYPE BODY: SYS.WRI$_ADV_SQLTUNE 12.1.1
owner:SYS, object_type:TYPE BODY, object_name:WRI$_ADV_SQLTUNE, status:VALID,
LIBRARY: SYS.DBMS_SQLTUNE_LIB 12.2.2
owner:SYS, object_type:LIBRARY, object_name:DBMS_SQLTUNE_LIB, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL_MONITOR 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL_MONITOR, status:VALID,
PACKAGE: SYS.DBMS_SQLPA 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLPA, status:VALID,
PACKAGE: SYS.DBMS_SQLPA 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLPA, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL2 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL2, status:VALID,
TYPE BODY: SYS.WRI$_REPT_SQLPI 12.1.1
owner:SYS, object_type:TYPE BODY, object_name:WRI$_REPT_SQLPI, status:VALID,
TYPE BODY: SYS.WRI$_REPT_SPMEVOLVE 12.2.2
owner:SYS, object_type:TYPE BODY, object_name:WRI$_REPT_SPMEVOLVE, status:VALID,
PACKAGE: SYS.PRVT_SQLPA 12.2.2
owner:SYS, object_type:PACKAGE, object_name:PRVT_SQLPA, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL0 12.1.1
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL0, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLADV_INFRA 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLADV_INFRA, status:VALID,
PACKAGE BODY: SYS.DBMS_AUTO_REPORT_INTERNAL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_AUTO_REPORT_INTERNAL, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLSET_INFRA 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLSET_INFRA, status:VALID,
PACKAGE BODY: SYS.DBMS_SQL_MONITOR 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQL_MONITOR, status:VALID,
TYPE BODY: SYS.WRI$_REPT_SQLPI 12.2.2
owner:SYS, object_type:TYPE BODY, object_name:WRI$_REPT_SQLPI, status:VALID,
TYPE BODY: SYS.WRI$_REPT_SQLT 12.2.2
owner:SYS, object_type:TYPE BODY, object_name:WRI$_REPT_SQLT, status:VALID,
TYPE BODY: SYS.WRI$_REPT_SPMEVOLVE 12.1.1
owner:SYS, object_type:TYPE BODY, object_name:WRI$_REPT_SPMEVOLVE, status:VALID,
PACKAGE BODY: SYS.DBMS_SPM 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SPM, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLDIAG 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLDIAG, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLADV_INFRA 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLADV_INFRA, status:VALID,
PACKAGE BODY: SYS.DBMS_SPM_INTERNAL 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SPM_INTERNAL, status:VALID,
VIEW: SYS.DBA_ADVISOR_PARAMETERS 12.2.2
owner:SYS, object_type:VIEW, object_name:DBA_ADVISOR_PARAMETERS, status:VALID,
VIEW: SYS.DBA_ADVISOR_PARAMETERS 12.1.1
owner:SYS, object_type:VIEW, object_name:DBA_ADVISOR_PARAMETERS, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLTUNE_UTIL2 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLTUNE_UTIL2, status:VALID,
TYPE BODY: SYS.WRI$_REPT_SQLT 12.1.1
owner:SYS, object_type:TYPE BODY, object_name:WRI$_REPT_SQLT, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLPA 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLPA, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLDIAG 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLDIAG, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLPA 12.1.1
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLPA, status:VALID,
PACKAGE: SYS.DBMS_SQLTUNE_UTIL0 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_SQLTUNE_UTIL0, status:VALID,
PACKAGE BODY: SYS.DBMS_SQLPA 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SQLPA, status:VALID,
PACKAGE BODY: SYS.DBMS_SPM 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SPM, status:VALID,
VIEW: SYS.DBA_ADVISOR_EXECUTIONS 12.2.2
owner:SYS, object_type:VIEW, object_name:DBA_ADVISOR_EXECUTIONS, status:VALID,
PACKAGE: SYS.PRVT_ADVISOR 12.1.1
owner:SYS, object_type:PACKAGE, object_name:PRVT_ADVISOR, status:VALID,
TABLE: SYS.WRI$_SQLSET_DEFINITIONS 12.1.1
owner:SYS, object_type:TABLE, object_name:WRI$_SQLSET_DEFINITIONS, status:VALID,
VIEW: SYS.DBA_ADVISOR_EXEC_PARAMETERS 12.1.1
owner:SYS, object_type:VIEW, object_name:DBA_ADVISOR_EXEC_PARAMETERS, status:VALID,
PACKAGE: SYS.PRVT_ADVISOR 12.2.2
owner:SYS, object_type:PACKAGE, object_name:PRVT_ADVISOR, status:VALID,
TABLE: SYS.WRI$_SQLSET_DEFINITIONS 12.2.2
owner:SYS, object_type:TABLE, object_name:WRI$_SQLSET_DEFINITIONS, status:VALID,
PACKAGE BODY: SYS.DBMS_SPM_INTERNAL 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:DBMS_SPM_INTERNAL, status:VALID,
PACKAGE BODY: SYS.PRVT_SQLPA 12.2.2
owner:SYS, object_type:PACKAGE BODY, object_name:PRVT_SQLPA, status:VALID,