My Oracle Support Banner

How To Use DBMS_SQLDIAG To Diagnose Query Performance Issues (Doc ID 1386802.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Goal

This article describes how to use DBMS_SQLDIAG to see if we can use PROBLEM_TYPE_PERFORMANCE to understand the performance problem of a SQL statement and implement any generated patch.
For other ways of using DBMS_SQLDIAG see:

<Document 1509192.1> How To Use DBMS_SQLDIAG To Diagnose Various Query Issues

This article is not intended to describe how to implement workarounds using the 'SQL Patch' functionality. It is intended to explore performance problem of a SQL statement using DBMS_SQLDIAG package.
For details of how to apply 'SQL Patch' refer to the DBMS_SQLDIAG.ACCEPT_SQL_PATCH procedure. See:

Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
Part Number E10577-04
Chapter 137 DBMS_SQLDIAG
Running the SQL Repair Advisor
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqldiag.htm#CEGFIEIF

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 Example Diagnostic Script
 Create a Diagnostic Task
 Check the Task Created
 Execute the Diagnostic Task
 Generate a Report Output
 Execute the recommendation, when available
 Verify the SQL Patch Implemented
 Verify if implementing the recommended SQL patch helped to get better performance or not
 Cleanup the Task Name
 Drop the SQL Patch
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.