Search Results init_task_wkldobj
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.