Search Results set_diagnosis_task_parameter

Overview

SYS.DBMS_SQLDIAG is the Oracle-supplied SQL Diagnostic and SQL Test Case Builder package that underpins the Automatic SQL Tuning and SQL Repair Advisor infrastructure in Oracle E-Business Suite Release 12.1.1 and 12.2.2. Within the EBS technical stack, this package provides the diagnostic engine that analyzes poorly performing SQL statements, produces structured diagnosis reports, and generates reproducible SQL Test Cases that Oracle Support can replay in a controlled environment. Although the package is owned by SYS and classified as an "OTHER" API in the ETRM repository, it is a foundational component of the database-side tuning toolset that EBS administrators rely upon when investigating performance regressions, plan instability, and optimizer anomalies.

The package's status is recorded as VALID, and it depends upon SYS-owned types and structures including SQLSET_ROW, SQL_BINDS, and STANDARD. It is a heavily referenced object: eleven distinct bodies reference it, including DBMS_SQLDIAG itself (PUBLIC and SYS synonyms), DBMS_SQLTCB_INTERNAL, DBMS_SQLTUNE_INTERNAL, DBMS_STATS, DBMS_STATS_INTERNAL, and DBMS_XPLAN. This dependency graph explains why a search for "dbms_sqltune_internal" surfaces DBMS_SQLDIAG, since the two packages are tightly coupled in the SQL Tuning Advisor and SQL Test Case Builder workflow.

Key Procedures and Functions

The ETRM metadata documents 34 procedures and functions within the package. The principal groups are summarized below.

• SQL Test Case Builder: EXPORT_SQL_TESTCASE, EXPORT_SQL_TESTCASE_DIR_BY_INC, EXPORT_SQL_TESTCASE_DIR_BY_TXT, IMPORT_SQL_TESTCASE, REPLAY_SQL_TESTCASE, and EXPLAIN_SQL_TESTCASE. These routines capture the SQL text, execution plan, bind values, and supporting metadata required to reproduce a problem statement and subsequently replay it.
• Incident Extraction: INCIDENTID_2_SQL and GETSQL, which translate an incident identifier or SQL identifier into the underlying SQL text for diagnostic processing.
• Trace Control: SET_TCB_TRACING, used to enable or disable tracing during test case generation.
• Diagnosis Task Lifecycle: CREATE_DIAGNOSIS_TASK, EXECUTE_DIAGNOSIS_TASK, INTERRUPT_DIAGNOSIS_TASK, CANCEL_DIAGNOSIS_TASK, RESET_DIAGNOSIS_TASK, RESUME_DIAGNOSIS_TASK, DROP_DIAGNOSIS_TASK, and REPORT_DIAGNOSIS_TASK. Together these provide the full create-execute-report-drop cycle that mirrors the SQL Repair Advisor workflow.
• Task Configuration: SET_DIAGNOSIS_TASK_PARAMETER, which adjusts diagnostic behavior prior to task execution.
• SQL Patch Management: ACCEPT_SQL_PATCH and DROP_SQL_PATCH, which apply or remove a SQL patch as a workaround for a problematic execution plan.

Tables Accessed

The ETRM metadata does not enumerate base tables accessed through APPS synonyms for this package; diagnostic persistence is handled internally through SYS-owned dictionary and advisor repository structures. Diagnosis tasks, findings, and generated test cases are written to the SQL Diagnostic and SQL Tuning Advisor repository tables (for example, the SQLDIAG and SQLTUNE-prefixed dictionary tables), while bind metadata is carried through the SQL_BINDS type referenced in the dependency list. EBS does not supply application-level synonyms for these objects, and DBAs should treat the underlying tables as Oracle-managed and not for direct DML.

Usage Notes

DBMS_SQLDIAG is not invoked directly from EBS forms or concurrent programs. It is typically exercised through the database-side SQL Tuning Advisor and SQL Repair Advisor, through Enterprise Manager, or through ad hoc DBA sessions using DBMS_SQLDIAG and DBMS_SQLTUNE_INTERNAL calls. Custom EBS code should not call this package directly; the supported path for plan remediation is to generate a SQL Test Case for Oracle Support or to accept a SQL patch through the documented advisor interface. Because the package is referenced by DBMS_STATS, DBMS_XPLAN, and the SQLTCB internal packages, changes to it can ripple across the tuning toolchain, so its use should be reserved for diagnostic scenarios on EBS 12.1.1 and 12.2.2 environments with a tested backup and rollback plan.