PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle DBMS_SQLDIAG
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsdiag.sql
First Available 11.1

Constants
Name Data Type Value
Advisor Name
ADV_SQL_DIAG_NAME VARCHAR2(18) 'SQL Repair Advisor'
SQLDIAG advisor task scope parameter values
SCOPE_LIMITED VARCHAR2(7) 'LIMITED'
SCOPE_COMPREHENSIVE VARCHAR2(13) 'COMPREHENSIVE'
SQLDIAG advisor time_limit constants
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(5) '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 Section
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 Section
MODE_ACCUMULATE_STATS NUMBER 2
MODE_REPLACE_OLD_STATS NUMBER 1
Problem Type
PROBLEM_TYPE_ALT_PLAN_GEN NUMBER 5
PROBLEM_TYPE_COMPILATION_ERROR NUMBER 3
PROBLEM_TYPE_EXECUTION_ERROR NUMBER 4
PROBLEM_TYPE_PERFORMANCE NUMBER 1
PROBLEM_TYPE_WRONG_RESULTS NUMBER 2
Findings Filter
SQLDIAG_FINDINGS_ALL NUMBER 1
SQLDIAG_FINDINGS_CR_DIFF NUMBER 5
SQLDIAG_FINDINGS_FEATURES NUMBER 3
SQLDIAG_FINDINGS_FILTER_PLANS NUMBER 4
QLDIAG_FINDINGS_MASK_VARIANT NUMBER 6
SQLDIAG_FINDINGS_OBJ_FEATURES NUMBER 7
SQLDIAG_FINDINGS_VALIDATION NUMBER 2
Mask Mode for Filtering Findings
SQLDIAG_MASK_COST NUMBER 2
SQLDIAG_MASK_NONE NUMBER 1

Dependencies
DBMS_ADVISOR DBMS_SQLTUNE_INTERNAL
DBMS_ASSERT DBMS_SQLTUNE_UTIL0
DBMS_LOB DBMS_SQLTUNE_UTIL1
DBMS_MANAGEMENT_PACKS DBMS_SQLTUNE_UTIL2
DBMS_OUTPUT DBMS_SYS_ERROR
DBMS_PIPE PLAN_TABLE
DBMS_SMB PRVT_SQLADV_INFRA
DBMS_SMB_INTERNAL PRVT_SQLPROF_INFRA
DBMS_SQLDIAG_INTERNAL SQLSET_ROW
DBMS_SQLDIAG_LIB SQL_BINDS
DBMS_SQLTCB_INTERNAL UTL_FILE
DBMS_SQLTUNE UTL_RAW
Security Model Execute is granted to public.

ALTER ANY SQL PATCH, CREATE ANY SQL PATCH, and DROP ANY SQL PATCH must be granted for the corresponding functionality. 
 
SQL Diagnostic Advisor Procedures & Functions
CANCEL_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.cancel_diagnosis_task(task_name IN VARCHAR2);
TBD
 
CREATE_DIAGNOSIS_TASK

Undocumented

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;
TBD
 
DROP_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.drop_diagnosis_task(task_name IN VARCHAR2);
TBD
 
EXECUTE_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.execute_diagnosis_task(task_name IN VARCHAR2);
See Demo below
 
INTERRUPT_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.interrupt_diagnosis_task(task_name IN VARCHAR2);
TBD
 
RESET_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.reset_diagnosis_task(task_name IN VARCHAR2);
TBD
 
RESUME_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.resume_diagnosis_task(task_name IN VARCHAR2);
TBD
 
REPORT_DIAGNOSIS_TASK
Undocumented 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;
See Demo below
 
SET_DIAGNOSIS_TASK
Undocumented dbms_sqldiag.set_diagnosis_task(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER);
TBD
 
SQL Patch Support Functions
ACCEPT_SQL_PATCH

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);
TBD
 
ALTER_SQL_PATCH
This procedure alters specific attributes of an existing SQL patch object dbms_sqldiag.alter_sql_patch(
name           IN VARCHAR2,
attribute_name IN VARCHAR2,
value          IN VARCHAR2);
TBD
 
CREATE_STGTAB_SQLPATCH
Undocumented dbms_sqldiag.create_stgtab_sqlpatch(
table_name IN VARCHAR2,
schema_name IN VARCHAR2 := NULL,
tablespace_name IN VARCHAR2 := NULL);
TBD
 
DROP_SQL_PATCH
This procedure drops the named SQL patch from the database dbms_sqldiag.drop_sql_patch(
name   IN VARCHAR2,
ignore IN BOOLEAN := FALSE);
TBD
 
PACK_STGTAB_SQLPATCH
Undocumented 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);
TBD
 
UNPACK_STGTAB_SQLPATCH
Undocumented 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);
TBD
 
Test Case Builder Procedures & Functions
EXPORT_SQL_TESTCASE

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 );
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 );
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);
TBD
 
EXPORT_SQL_TESTCASE_DIR_BY_INC
Undocumented dbms_sqldiag.export_sql_testcase_dir_by_inc(
incident_id     IN NUMBER,
directory       IN VARCHAR2,
samplingPercent IN VARCHAR2 := '0')
RETURN BOOLEAN;
TBD
 
EXPORT_SQL_TESTCASE_DIR_BY_TXT
Undocumented dbms_sqldiag.export_sql_testcase_dir_by_txt(
FUNCTION 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' )
RETURN BOOLEAN;
TBD
 
GETSQL

load a sql_setrow from the trace file associated with an incident ID		 dbms_sqldiag.getsql(incident_id IN VARCHAR2) RETURN SQLSET_ROW;
SELECT num_incident, check_name, name, timeout
FROM gv$hm_run;

desc sqlset_row

set serveroutput on

DECLARE
 z   VARCHAR2(100);
 ssr sqlset_row;
BEGIN
  ssr := dbms_sqldiag.getsql(1);
  dbms_output.put_line(z);
END;
/
 
IMPORT_SQL_TESTCASE
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);
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);
TBD
 
INCIDENTID_2_SQL
Undocumented 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);
TBD
 
Demo

SQLDIAG Demo		 conn uwclass/uwclass

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);

  dbms_output.put_line('Report : ' || rep_out);
END;
/

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
ORDER BY 1;

-- apply the recommended patch
exec dbms_sqldiag.accept_sql_patch(task_name => 'error_task', task_owner => 'SYS', replace => TRUE);

-- test the patch
DELETE FROM t t1 WHERE t1.a = 'a' AND ROWID <> (select max(rowid) FROM 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;

dbms_sqltune.drop_tuning_task (task_name => 'error_task');
 
Related Topics
DBMS_ADVISOR
DBMS_SQLTUNE
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----