ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Provides an API to the SQL Diagnosability functionality used by the OEM Diagnostic Pack
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
SQLDiag Advisor Name
ADV_SQL_DIAG_NAME
VARCHAR2(18)
'SQL Repair Advisor'
Task Scopes
SCOPE_LIMITED
VARCHAR2(7)
'LIMITED'
SCOPE_COMPREHENSIVE
VARCHAR2(13)
'COMPREHENSIVE'
Advisor Time Limit
TIME_LIMIT_DEFAULT
NUMBER
1800
Report Types
TYPE_HTML
VARCHAR2(4)
'HTML'
TYPE_TEXT
VARCHAR2(4)
'TEXT'
TYPE_XML
VARCHAR2(3)
'XML'
Report Levels
LEVEL_ALL
VARCHAR2(3)
'ALL'
LEVEL_BASIC
VARCHAR2(7)
'BASIC'
LEVEL_TYPICAL
VARCHAR2(7)
'TYPICAL'
Report Sections
SECTION_ALL
VARCHAR2(3)
'ALL'
SECTION_ERRORS
VARCHAR2(6)
'ERRORS'
SECTION_FINDINGS
VARCHAR2(8)
'FINDINGS'
SECTION_INFORMATION
VARCHAR2(11)
'INFORMATION'
SECTION_PLANS
VARCHAR2(5)
'PLANS'
SECTION_SUMMARY
VARCHAR2(7)
'SUMMARY'
Script Sections
REC_TYPE_ALL
VARCHAR2(3)
'ALL'
REC_TYPE_INDEXES
VARCHAR2(7)
'INDEXES'
REC_TYPE_SQL_PROFILES
VARCHAR2(8)
'PROFILES'
REC_TYPE_STATS
VARCHAR2(10)
'STATISTICS'
Capture Sections
MODE_REPLACE_OLD_STATS
NUMBER
1
MODE_ACCUMULATE_STATS
NUMBER
2
Problem Types
PROBLEM_TYPE_PERFORMANCE
NUMBER
1
PROBLEM_TYPE_WRONG_RESULTS
NUMBER
2
PROBLEM_TYPE_COMPILATION_ERROR
NUMBER
3
PROBLEM_TYPE_EXECUTION_ERROR
NUMBER
4
PROBLEM_TYPE_ALT_PLAN_GEN
NUMBER
5
Findings Filters
SQLDIAG_FINDINGS_ALL
NUMBER
1
SQLDIAG_FINDINGS_VALIDATION
NUMBER
2
SQLDIAG_FINDINGS_FEATURES
NUMBER
3
SQLDIAG_FINDINGS_FILTER_PLANS
NUMBER
4
SQLDIAG_FINDINGS_CR_DIFF
NUMBER
5
SQLDIAG_FINDINGS_MASK_VARIANT
NUMBER
6
SQLDIAG_FINDINGS_OBJ_FEATURES
NUMBER
7
SQLDIAG_FINDINGS_BASIC_INFO
NUMBER
8
Mask Mode for Filtering Findings
SQLDIAG_MASK_NONE
NUMBER
1
SQLDIAG_MASK_COST
NUMBER
2
Dependencies
ALL_USERS
DBMS_SQLDIAG_INTERNAL
DBMS_XPLAN
ANYDATA
DBMS_SQLPA
DBMS_XPLAN_INTERNAL
DBMS_ADVISOR
DBMS_SQLTCB_INTERNAL
DUAL
DBMS_ASSERT
DBMS_SQLTUNE
PLITBLM
DBMS_AUTO_INDEX_INTERNAL
DBMS_SQLTUNE_INTERNAL
PRVT_ADVISOR
DBMS_LOB
DBMS_SQLTUNE_UTIL0
PRVT_SQLADV_INFRA
DBMS_MANAGEMENT_PACKS
DBMS_SQLTUNE_UTIL1
PRVT_SQLPROF_INFRA
DBMS_PDB
DBMS_SQLTUNE_UTIL2
SQLSET_ROW
DBMS_PDB_CHECK_LOCKDOWN
DBMS_STANDARD
SQL_BINDS
DBMS_REDEFINITION
DBMS_STATS
V_$SQL
DBMS_SMB
DBMS_STATS_INTERNAL
XMLSEQUENCE
DBMS_SMB_INTERNAL
DBMS_STATS_INTERNAL_AGG
XMLTYPE
DBMS_SPM
DBMS_SYS_ERROR
XQSEQUENCE
Documented
Yes
Exceptions
Error Code
Reason
ORA-13830
SQL profile or patch with category DEFAULT already exists for this SQL statement
This procedure accepts a SQL patch as recommended by the specified SQL tuning task
Overload 1
dbms_sqldiag.accept_sql_patch(
task_name IN VARCHAR2,
object_id IN NUMBER := NULL,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
task_owner IN VARCHAR2 := NULL,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE)
RETURN VARCHAR2;
TBD
Overload 2
dbms_sqldiag.accept_sql_patch(
task_name IN VARCHAR2,
object_id IN NUMBER := NULL,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
task_owner IN VARCHAR2 := NULL,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE);
Creates a diagnostic task in order to diagnose a single SQL statement
Overload 1
dbms_sqldiag.create_diagnosis_task(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
user_name IN VARCHAR2 := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE)
RETURN VARCHAR2;
See Demo below
Overload 2
dbms_sqldiag.create_diagnosis_task(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE)
RETURN VARCHAR2;
TBD
Overload 3
dbms_sqldiag.create_diagnosis_task(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
rank1 IN VARCHAR2 := NULL,
rank2 IN VARCHAR2 := NULL,
rank3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
sqlset_owner IN VARCHAR2 := NULL,
problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE)
RETURN VARCHAR2;
Creates a SQL patch based on a set of user specified hints for specific statements identified by the SQL text.
A SQL patch is usually created automatically by the SQL Repair Advisor to prevent any errors during the compilation or execution of a SQL statement.
This procedure provides a way to manually create a SQL patch based on a set of hints that resolves the issue.
Overload 1
dbms_sqldiag.create_sql_patch(
sql_text IN CLOB,
hint_text IN CLOB,
name IN VARCHAR2 := NULL,
decription IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN := TRUE)
RETURN VARCHAR2;
TBD
Creates a SQL patch based on a set of user specified hints for specific statements identified by the SQL id.
A SQL patch is usually created automatically by the SQL Repair Advisor to prevent any errors during the compilation or execution of a SQL statement.
This procedure provides a way to manually create a SQL patch based on a set of hints that resolves the issue.
Overload 2
dbms_sqldiag.create_sql_patch(
sql_id IN VARCHAR2,
hint_text IN CLOB,
name IN VARCHAR2 := NULL,
decription IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN := TRUE)
RETURN VARCHAR2;
dbms_sqldiag.dump_trace(
p_sql_id IN VARCHAR2,
p_child_number IN NUMBER DEFAULT 0,
p_component IN VARCHAR2 DEFAULT 'Optimizer', -- alt. value 'Compiler'
p_file_id IN VARCHAR2 DEFAULT NULL);
SELECT /* DUMP_TRACE */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%DUMP_TRACE%';
exec dbms_sqldiag.dump_trace('gk3bbazk1jj3h', 0);
-- the file was dumped as:
/app/oracle/product/diag/rdbms/orabeta/orabeta/trace/orabeta_dbrm_18833.trc
Export a SQL test case to a directory. This variant of the API has to be provided with the SQL information
Overload 1
dbms_sqldiag.export_sql_testcase(
directory IN VARCHAR2,
sql_text IN CLOB,
user_name IN VARCHAR2 := NULL,
bind_list IN sql_binds := NULL,
exportEnvironment IN BOOLEAN := TRUE,
exportMetadata IN BOOLEAN := TRUE,
exportData IN BOOLEAN := FALSE,
samplingPercent IN NUMBER := 100,
ctrlOptions IN VARCHAR2 := NULL,
timeLimit IN NUMBER := 0,
testcase_name IN VARCHAR2 := NULL,
testcase IN OUT NOCOPY CLOB,
preserveSchemaMapping IN BOOLEAN := FALSE,
version IN VARCHAR2 := 'COMPATIBLE');
TBD
Export a SQL test case to a directory. This API extract the SQL information from an incident file.
Overload 2
dbms_sqldiag.export_sql_testcase(
directory IN VARCHAR2,
incident_id IN VARCHAR2,
exportEnvironment IN BOOLEAN := TRUE,
exportMetadata IN BOOLEAN := TRUE,
exportData IN BOOLEAN := FALSE,
samplingPercent IN NUMBER := 100,
ctrlOptions IN VARCHAR2 := NULL,
timeLimit IN NUMBER := 0,
testcase_name IN VARCHAR2 := NULL,
testcase IN OUT NOCOPY CLOB,
preserveSchemaMapping IN BOOLEAN := FALSE,
version IN VARCHAR2 := 'COMPATIBLE');
TBD
Export a SQL test case to a directory. This API allow the SQL Testcase to be generated from a cursor present in the cursor cache. Use v$sql to get the SQL identifier and the SQL hash value.
Overload 3
dbms_sqldiag.export_sql_testcase(
directory IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
exportEnvironment IN BOOLEAN := TRUE,
exportMetadata IN BOOLEAN := TRUE,
exportData IN BOOLEAN := FALSE,
samplingPercent IN NUMBER := 100,
ctrlOptions IN VARCHAR2 := NULL,
timeLimit IN NUMBER := 0,
testcase_name IN VARCHAR2 := NULL,
testcase IN OUT NOCOPY CLOB,
preserveSchemaMapping IN BOOLEAN := FALSE,
version IN VARCHAR2 := 'COMPATIBLE');
dbms_sqldiag.export_sql_testcase_dir_by_inc(
incident_id IN NUMBER,
directory IN VARCHAR2,
samplingPercent IN VARCHAR2 := '0',
exportEnvironment IN BOOLEAN := TRUE,
exportMetadata IN BOOLEAN := TRUE,
exportPkgbody IN BOOLEAN := FALSE,
preserveSchemaMapping IN BOOLEAN := FALSE,
version IN VARCHAR2 := 'COMPATIBLE')
RETURN BOOLEAN;
-- go to $ORACLE_BASE/diag/rdbms/orabeta/orabeta/incident and view subdirectory incdir_3809
set serveroutput on
DECLARE
v_inc NUMBER := 3809;
v_dir VARCHAR2(30) := 'CTEMP';
BEGIN
IF sys.dbms_sqldiag.export_sql_testcase_dir_by_inc(v_inc, v_dir) THEN
dbms_output.put_line('Package Created');
ELSE
dbms_output.put_line('Package Creation Failure');
END IF;
END;
/
SQL> DECLARE
2 v_inc NUMBER := 3809;
3 v_dir VARCHAR2(30) := 'CTEMP';
4 BEGIN
5 IF sys.dbms_sqldiag.export_sql_testcase_dir_by_inc(v_inc, v_dir) THEN
6 dbms_output.put_line('Package Created');
7 ELSE
8 dbms_output.put_line('Package Creation Failure');
9 END IF;
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-39087: directory name TCBORA$PIPE$008C07960001 is invalid
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SQLDIAG", line 92
ORA-06512: at "SYS.DBMS_SQLDIAG", line 312
ORA-06512: at line 5
Note these are not the same as ADRCI indicent numbers
The docs are terrible on this
Generates a SQL Test Case corresponding to the SQL passed as an argument
dbms_sqldiag.export_sql_testcase_dir_by_txt(
incident_id IN NUMBER,
directory IN VARCHAR2,
sql_text IN CLOB,
user_name IN VARCHAR2 := NULL,
samplingPercent IN VARCHAR2 := '0',
exportEnvironment IN BOOLEAN := TRUE,
exportMetadata IN BOOLEAN := TRUE,
exportPkgbody IN BOOLEAN := FALSE,
preserveSchemaMapping IN BOOLEAN := FALSE,
version IN VARCHAR2 := 'COMPATIBLE')
RETURN BOOLEAN;
Import a SQL Test case into a schema from a directory and a file name
Overload 1
dbms_sqldiag.import_sql_testcase(
directory IN VARCHAR2,
sqlTestCase IN CLOB,
importEnvironment IN BOOLEAN := TRUE,
importMetadata IN BOOLEAN := TRUE,
importData IN BOOLEAN := FALSE,
importDiagnosis IN BOOLEAN := TRUE,
ignoreStorage IN BOOLEAN := TRUE,
ctrlOptions IN VARCHAR2 := NULL,
preserveSchemaMapping IN BOOLEAN := FALSE);
TBD
Initialize a sql_setrow from an incident ID. Given a valid incident ID this function parses the trace file
and extract as much information as possible about the SQL that causes the generation of this incident (SQL text, user name, binds, etc...).
Overload 2
dbms_sqldiag.import_sql_testcase(
directory IN VARCHAR2,
filename IN VARCHAR2,
importEnvironment IN BOOLEAN := TRUE,
importMetadata IN BOOLEAN := TRUE,
importData IN BOOLEAN := FALSE,
importDiagnosis IN BOOLEAN := TRUE,
ignoreStorage IN BOOLEAN := TRUE,
ctrlOptions IN VARCHAR2 := NULL,
preserveSchemaMapping IN BOOLEAN := FALSE);
dbms_sqldiag.incidentid_2_sql(
incident_id IN VARCHAR2,
sql_stmt OUT SQLSET_ROW,
problem_type OUT NUMBER,
err_code OUT BINARY_INTEGER,
err_mesg OUT VARCHAR2);
SQL patches into the staging table created by the CREATE_STGTAB_SQLPATCH Procedure
dbms_sqldiag.pack_stgtab_sqlpatch(
patch_name IN VARCHAR2 := '%',
patch_category IN VARCHAR2 := 'DEFAULT',
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
dbms_sqldiag.explain_sql_testcase(
directory IN VARCHAR2,
sqlTestCase IN CLOB,
ctrlOptions IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TEXT')
RETURN CLOB;
TBD
Overload 2
dbms_sqldiag.explain_sql_testcase(
directory IN VARCHAR2,
filename IN VARCHAR2,
ctrlOptions IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TEXT')
RETURN CLOB;
dbms_sqldiag.report_diagnosis_task(
task_name IN VARCHAR2,
type IN VARCHAR2 := TYPE_TEXT,
level IN VARCHAR2 := LEVEL_ALL,
section IN VARCHAR2 := SECTION_ALL,
object_id IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
owner_name IN VARCHAR2 := NULL)
RETURN CLOB;
Diagnose a given SQL statement for the given problem type.
-- It creates an incident, populate incident metadata with
-- required information like, sqlid, sql text, compilation env etc,
-- creates a diagnostic task, executes it and accepts SQL PATCH
-- recommendation for a given SQL statement.
Overload 1
dbms_sqldiag.sql_diagnose_and_repair(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE,
auto_apply_patch IN VARCHAR2 := 'YES')
RETURN NUMBER;
Overload 2
dbms_sqldiag.sql_diagnose_and_repair(
sql_id IN CLOB,
bind_list IN sql_binds := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE,
auto_apply_patch IN VARCHAR2 := 'YES')
RETURN NUMBER;
Overload 3
dbms_sqldiag.sql_diagnose_and_repair(
incident_id IN NUMBER,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE,
auto_apply_patch IN VARCHAR2 := 'YES')
RETURN NUMBER;
Unpacks from the staging table populated by a call to PACK_STGTAB_SQLPATCH, using the patch data stored in the staging table to create patches on this system
dbms_sqldiag.unpack_stgtab_sqlpatch(
patch_name IN VARCHAR2 := '%',
patch_category IN VARCHAR2 := '%',
replace IN BOOLEAN,
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);
CREATE TABLE t (
a VARCHAR2(3),
b VARCHAR2(3),
c VARCHAR2(3),
d VARCHAR2(3));
INSERT INTO t VALUES ('a', 'b', 'c', 'd');
INSERT INTO t VALUES ('u', 'v', 'w', 'd');
INSERT INTO t VALUES ('a', 'b', 'c', 'z');
INSERT INTO t VALUES ('w', 'x', 'y', 'd');
SELECT * FROM t;
-- a critical error occurs
DELETE FROM uwclass.t t1
WHERE t1.a = 'a'
AND ROWID <> (
SELECT MAX(ROWID)
FROM uwclass.t t2
WHERE t1.a= t2.a
AND t1.b = t2.b AND t1.d=t2.d);
SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 1;
set long 1000000
set serveroutput on
DECLARE
rep_out CLOB;
t_id VARCHAR2(50);
BEGIN
-- create a diagnosis task
t_id := dbms_sqldiag.create_diagnosis_task(
sql_text => 'DELETE FROM uwclass.t t1 WHERE t1.a = ''a'' AND ROWID <>
(SELECT MAX(ROWID) FROM uwclass.t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND
t1.d=t2.d)', task_name => 'error_task', problem_type =>
DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);
-- run the diagnosis task
dbms_sqldiag.execute_diagnosis_task(t_id);
-- output the report
rep_out := dbms_sqldiag.report_diagnosis_task(t_id,
dbms_sqldiag.type_text);