SQL Diagnostic Report: Oracle 23ai Feature Now Available in 19.28

 

Introduction

Oracle Database 23ai introduced a powerful new diagnostic capability through the DBMS_SQLDIAG.REPORT_SQL function, which generates comprehensive HTML diagnostic reports for SQL statements. The great news for Oracle 19c users is that this feature has been backported to Oracle Database 19.28, allowing you to leverage advanced SQL analysis without upgrading to 23ai.

Before this feature, DBAs typically relied on the awrsqrpt.sql script to analyze SQL execution plans and performance metrics. The new REPORT_SQL function provides a more comprehensive, graphical HTML report with deeper performance insights.

What Does SQL Diagnostic Report Provide?

The REPORT_SQL function generates a comprehensive HTML diagnostic report that includes:

Prerequisites

Before using this feature, ensure you have:

  1. Oracle Database 19.28 or later (or Oracle 23ai)
  2. ADVISOR role granted to your user
  3. A directory object created for report output

Step-by-Step Implementation

Step 1: Create a Directory Object

First, create a directory object where the HTML reports will be stored:

SQL> CREATE OR REPLACE DIRECTORY DR_DIAGNOSTIC AS '/oracle/Diagnostic';

Directory created.

Step 2: Grant Permissions

Ensure your user has the necessary privileges:

SQL> GRANT READ, WRITE ON DIRECTORY DR_DIAGNOSTIC TO your_username;

Step 3: Identify the SQL_ID

Find the SQL_ID of the statement you want to analyze:

SQL> SELECT sql_id, sql_text, executions, elapsed_time
FROM v$sql
WHERE sql_text LIKE '%your_query_pattern%'
AND sql_text NOT LIKE '%v$sql%';

Step 4: Describe the REPORT_SQL Function

Let's examine the function signature:

SQL> DESC DBMS_SQLDIAG.REPORT_SQL

Parameter    Type        Mode  Default?
------------ ----------- ----- --------
(RESULT)     CLOB
SQL_ID       VARCHAR2    IN
DIRECTORY    VARCHAR2    IN    Y
LEVEL        VARCHAR2    IN    Y

Step 5: Generate the SQL Diagnostic Report

Execute the following PL/SQL block to generate the report:

SQL> DECLARE
  rpt1 CLOB;
BEGIN
  rpt1 := DBMS_SQLDIAG.report_sql(
    sql_id    => 'a0na7qgw1zw98',  -- Replace with your SQL_ID
    directory => 'DR_DIAGNOSTIC',
    level     => 'ALL'
  );
END;
/

PL/SQL procedure successfully completed.

Report Level Options

The LEVEL parameter controls the amount of detail in the report:

Level Description
BASIC Minimal report with only essential details
TYPICAL Standard report with basic and advanced sections (default)
ALL Comprehensive report with maximum detail

Example with Different Levels

Basic Level Report

DECLARE
  rpt1 CLOB;
BEGIN
  rpt1 := DBMS_SQLDIAG.report_sql(
    sql_id    => 'gtckcpxmp3ry7',
    directory => 'DR_DIAGNOSTIC',
    level     => 'BASIC'
  );
END;
/

Typical Level Report (Default)

DECLARE
  rpt1 CLOB;
BEGIN
  rpt1 := DBMS_SQLDIAG.report_sql(
    sql_id    => 'gtckcpxmp3ry7',
    directory => 'DATA_PUMP_DIR',
    level     => 'TYPICAL'
  );
END;
/

All Details Report

DECLARE
  rpt1 CLOB;
BEGIN
  rpt1 := DBMS_SQLDIAG.report_sql(
    sql_id    => 'gtckcpxmp3ry7',
    directory => 'DATA_PUMP_DIR',
    level     => 'ALL'
  );
END;
/

Locating and Viewing the Report

Step 1: Navigate to the Directory

[oracle@server ~]$ cd /oracle/Diagnostic
[oracle@server Diagnostic]$ ll
-rw-r--r--. 1 oracle asmadmin 22637 Aug 27 18:20 SQLR_a0na7qgw1zw98_202508271820.zip

Step 2: Extract the ZIP File

[oracle@server Diagnostic]$ unzip SQLR_a0na7qgw1zw98_202508271820.zip
Archive: SQLR_a0na7qgw1zw98_202508271820.zip
  inflating: SQLR_a0na7qgw1zw98_202508271820.html

Step 3: View the HTML Report

[oracle@server Diagnostic]$ ll
-rw-r--r--. 1 oracle oinstall 125063 Aug 27 18:20 SQLR_a0na7qgw1zw98_202508271820.html
-rw-r--r--. 1 oracle asmadmin  22637 Aug 27 18:20 SQLR_a0na7qgw1zw98_202508271820.zip

Open the HTML file in a web browser to view the comprehensive diagnostic report.

Report File Naming Convention

The generated report follows this naming pattern:

SQLR_<SQL_ID>_<YYYYMMDDHH24MI>.html

Where:

  • SQL_ID - The SQL identifier provided
  • YYYYMMDDHH24MI - Timestamp when the file was created

Generating Report Without Directory (Return as CLOB)

You can also return the report as a CLOB without writing to disk:

SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 32767

DECLARE
  rpt1 CLOB;
BEGIN
  rpt1 := DBMS_SQLDIAG.report_sql(
    sql_id => 'a0na7qgw1zw98'
  );
  DBMS_OUTPUT.PUT_LINE(rpt1);
END;
/

Practical Use Cases

Use Case 1: Investigating Performance Degradation

When a SQL statement suddenly performs poorly:

-- Generate comprehensive report
DECLARE
  rpt CLOB;
BEGIN
  rpt := DBMS_SQLDIAG.report_sql(
    sql_id    => '<problematic_sql_id>',
    directory => 'DR_DIAGNOSTIC',
    level     => 'ALL'
  );
END;
/

The report will show execution plan changes, statistics changes, and historical performance metrics.

Use Case 2: Comparing Execution Plans Across Time

Generate reports for the same SQL_ID at different times to compare:

-- Today's report
DECLARE
  rpt CLOB;
BEGIN
  rpt := DBMS_SQLDIAG.report_sql(
    sql_id    => 'abc123xyz',
    directory => 'DR_DIAGNOSTIC',
    level     => 'ALL'
  );
END;
/

Use Case 3: Baseline Performance Analysis

Create reports for critical SQL statements as part of your baseline documentation:

-- Generate baseline reports for top 10 SQL by elapsed time
BEGIN
  FOR rec IN (
    SELECT sql_id
    FROM (
      SELECT sql_id, elapsed_time
      FROM v$sql
      WHERE parsing_schema_name NOT IN ('SYS','SYSTEM')
      ORDER BY elapsed_time DESC
    )
    WHERE ROWNUM <= 10
  )
  LOOP
    DECLARE
      rpt CLOB;
    BEGIN
      rpt := DBMS_SQLDIAG.report_sql(
        sql_id    => rec.sql_id,
        directory => 'DR_DIAGNOSTIC',
        level     => 'TYPICAL'
      );
    END;
  END LOOP;
END;
/

Key Benefits

  1. Comprehensive Analysis - Single report contains multiple diagnostic dimensions
  2. Graphical HTML Format - Easy to read and share with team members
  3. Historical Tracking - View evolution of execution plans and statistics
  4. No Additional Tools - Built-in functionality, no external scripts needed
  5. Backported Feature - Available in 19.28 without upgrading to 23ai

Troubleshooting

Issue: ORA-20000: Directory does not exist

Solution: Verify the directory object exists and is accessible:

SELECT directory_name, directory_path 
FROM dba_directories 
WHERE directory_name = 'DR_DIAGNOSTIC';

Issue: Insufficient Privileges

Solution: Ensure you have the ADVISOR role:

GRANT ADVISOR TO username;

Issue: No Output File Generated

Solution: Check directory permissions on the OS level:

ls -ld /oracle/Diagnostic
chmod 755 /oracle/Diagnostic

Comparison with awrsqrpt.sql

Feature awrsqrpt.sql DBMS_SQLDIAG.REPORT_SQL
Output Format Text HTML (Graphical)
Ease of Use Interactive script Single function call
Customization Limited Level parameters
Report Sections Fixed Comprehensive
Integration Manual Programmatic

Conclusion

The DBMS_SQLDIAG.REPORT_SQL function represents a significant improvement in SQL diagnostics capabilities. With its backport to Oracle 19.28, DBAs can now leverage this powerful 23ai feature without waiting for a major database upgrade.

This feature streamlines SQL performance analysis by providing comprehensive, easy-to-read HTML reports that consolidate multiple diagnostic dimensions into a single view. Whether you're troubleshooting performance issues, documenting baselines, or analyzing execution plan changes, this tool should become a standard part of your diagnostic toolkit.

Additional Resources

Comments

Post a Comment

Popular posts from this blog

Resolving 19c Oracle Database Performance Woes: Unraveling the Mystery of 'Free Buffer Waits'

Image
  Introduction:   A recent upgrade from version 19.21 left a customer grappling with a perplexing performance decline, marked by a surge in "free buffer waits." Tasks that once took a manageable two hours were suddenly stretching beyond the tolerable 10-hour mark, prompting a closer examination of the underlying issues. The customer had implemented a multi-tenant architecture utilizing a single Pluggable Database (PDB).   Unveiling the Culprit:   The initial investigation pinpointed a conspicuous increase in "free buffer waits," casting a shadow on system efficiency and causing disruptions in the normal flow of operations. Recognizing the critical nature of this bottleneck, the focus shifted to conducting a thorough hanganalyze to unravel the intricacies of the problem. Chains most likely to have caused the hang:   [a] Chain 1 Signature: 'rdbms ipc message'<='free buffer waits'       Chain 1 Signature Hash: 0x8e1d98d...
Read more

Investigating a CPU Spike and Resource/cluster Hang in Oracle RAC Environment

Introduction: In a complex Oracle Real Application Clusters (RAC) environment, unexpected issues can arise, leading to system instability and performance degradation. In this blog post, we will delve into a specific incident involving a CPU spike and resource hang that occurred during the execution of a high parallelism SQL job. We will analyze the root causes and explore potential solutions to mitigate such problems in the future. The Incident: On a particular day between 2:00 PM and 6:30 PM IST, an SQL job with a Degree of Parallelism (DoP) set to 200 was initiated on Node#3 around 2:12 PM IST. Soon after, the CPU Idle% dropped to single digits, reaching as low as 1% at 3:10 PM IST. This performance degradation led to Node#6 being evicted following a dirty detach reconfiguration. During the incident, it was observed that the shutdown process remained hung for an extended period, and the system was only restarted at 4:44 PM as per the Syslog. The Cluster Ready Services (CRS) stack cam...
Read more

Unveiling the Mystery Behind a Critical Downtime Incident: Default TRANSMIT_MODE Behavior in Oracle Database 19c's Log Archive Destinations

Image
Recently, one of our valued customers experienced a significant downtime event when their complete 6-node RAC cluster fell into a hang state due to a massive pile-up of sessions, exceeding 6000. The gravity of the situation was immediately apparent as the downtime persisted intermittently for nearly four hours, from 2:10 PM to 5:30 PM, impacting critical transactions and leading to declines. Upon their reaching out to us for assistance, our troubleshooting journey commenced, aiming to uncover the root cause and provide recommendations to rectify the issue. Problem Statement: The 6-node cluster entered a hang state despite attempted reboots, resulting in a prolonged outage with severe implications for transaction processing. Unraveling the Culprit: The issue presented itself as a perplexing anomaly, requiring thorough analysis of diagnostic data within the live system. Initial investigations revealed a high occurrence of GC and logfile sync wait events amidst other contention factors. N...
Read more