Wouldn’t it be good to have all relevant optimizer information for a SQL statement in one place? Helpful optimizer statistics, schema metadata, configuration parameters and other factors that have impacts on the overall performance of a SQL statement. Having a full picture would really help to make sure the environment where those queries are running is in “good shape”! 
In 23ai, the new function REPORT_SQL of the package DBMS_SQLDIAG can provide such functionality and runs in all database environments. It is very easy to use and can be executed in all Oracle Database environments.

Important Update: 
With Oracle Database RU 19.28, it’s also available in 19c. 
If you are interested in features and release updates, you can find more information in the blog post New Features and Release Updates in 19c and 23ai.


REPORT_SQL is the successor of SQLHC, the SQL Tuning Health-Check Script available from My Oracle Support. If you have older database versions and need access to SQLHC you can download it from My Oracle Support (Doc ID 1366133.1).
 

Usage

The usage of REPORT_SQL is very simple. You only need the SQL_ID of a statement, then execute the REPORT_SQL function. It generates an HTML report with the results of a set of health-checks around the SQL statement provided. It can be used in all editions of Oracle databases on premises or in cloud.

In this posting, we illustrate how to use REPORT_SQL in different environments such as autonomous database and other database environments. 

The syntax can be found in PL/SQL Packages and Types Reference for DBMS_SQLDIAG:

DBMS_SQLDIAG.REPORT_SQL(sql_id IN VARCHAR2, directory IN VARCHAR2, level IN VARCHAR2 ) RETURN CLOB
  • Three options for LEVEL  are available: BASIC for minimal report detail, TYPICAL the standard (default) report which contains more advanced report sections, and ALL for a complete report containing all possible report information.
  • By default, the report is returned as a CLOB and not written to disk. If a directory name is specified for the DIRECTORY argument, the file name will be created in the following format SQLR_<SQL_ID>_<YYYYMMDDHH24MI>.html

To find the SQL_ID of a statement, use AWR, ASH or V$SQL or the SQL*PLUS setting “set feedback on sql_id” if available. 
Here is an example for our first test.

SQL> set feedback on sql_id
SQL> SELECT p.prod_id, SUM(quantity_sold) AS sold
     FROM   products p, sales s
     WHERE  p.prod_id = s.prod_id and lower(prod_category) like '%tennis' and prod_code = 1 
     GROUP BY p.prod_id
     union
     SELECT s.prod_id, SUM(s.amount_sold) AS sold
     FROM   sales s, sales p where s.time_id>=sysdate
     GROUP BY s.prod_id;

PROD_ID       SOLD
---------- ----------
       140      14769
       146      12742
   ....
SQL_ID: 6wckp3ksb32m9

Let’s illustrate the usage of REPORT_SQL and return the content into the CLOB variable named report. 

set feedback on
var report clob;
exec :report := dbms_sqldiag.report_sql('6wckp3ksb32m9'); 
PL/SQL procedure successfully completed.

Now use the following format settings and spool the file.

set trimspool on 
set trim on
set pagesize 0
set linesize 32767
set long 1000000
set longchunksize 1000000

spool diagsql1.html
select :report report from dual;
spool off

The result is an HTML report that includes execution plans, plan history, non-default database parameters, stats history, indexes, and more.
Here is the header information of the HTML report. 
DIAG Report

In the second example, we use Autonomous Database Serverless (ADB-S) and the directory DATA_PUMP_DIR to store the HTML file.

First, we list all available directories.

SQL> select directory_name 
     from all_directories;
DIRECTORY_NAME 
-------------- 
SQL_TCB_DIR    
DATA_PUMP_DIR 

Let’s use the following statement with SQL_ID fbr2p8gp9d5dn.

SELECT p.prod_id, sum(quantity_sold) AS sold
FROM sh.products p, sh.sales s
WHERE p.prod_id = s.prod_id and lower(prod_category) =’photo’
GROUP BY p.prod_id
union
SELECT s.prod_id, sum(s.amount_sold) AS sold
FROM sh.sales s, sh.sales p where s.time_id>=sysdate
GROUP BY s.prod_id;

In this case, we generate the HTML report within the directory DATA_PUMP_DIR.

declare
my_report clob;
begin
 my_report := dbms_sqldiag.report_sql('fbr2p8gp9d5dn', directory=>'DATA_PUMP_DIR', level=>'ALL');
end;
/

After a short time, you should get the message “PL/SQL procedure successfully completed”.
Now we use the package DBMS_CLOUD to receive the HTML file.


The output file can be listed using DBMS_CLOUD.LIST_FILES.

SQL> select * 
     from table(dbms_cloud.list_files('DATA_PUMP_DIR'));
OBJECT_NAME                                BYTES CHECKSUM   CREATED                             LAST_MODIFIED                      
------------------------------------- ---------- ---------- ----------------------------------- -----------------------------------
SQLR_fbr2p8gp9d5dn_202411201540.html      278497            20-NOV-24 03.40.41.533979000 PM GMT 20-NOV-24 03.41.04.139479000 PM GMT

In the next step, let’s transfer a copy of the BLOB file from Autonomous Database to the Cloud Object Store. For this, you can use DBMS_CLOUD.PUT_OBJECT.
Let’s verify the credential information.

SQL> select * from dba_credentials;
OWNER CREDENTIAL_NAME 
----- --------------- 
ADMIN CREDENTIAL_US1   

Now we use  DBMS_CLOUD.PUT_OBJECT as follows:

BEGIN
 DBMS_CLOUD.PUT_OBJECT(credential_name =>'CREDENTIAL_US1', 
                        object_uri     => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/tenant_name
                                           /b/bucket_name/o/SQLR_fbr2p8gp9d5dn_202411201540.html', 
                       directory_name  => 'DATA_PUMP_DIR',
                       file_name       => 'SQLR_5yaurap8h97nx_202411181751.html');
END;

You will find now the file in Cloud Object Store and can download it to your environment to receive the HTML deep-level diagnostic report information generated for this SQL statement.

Summary

The new function REPORT_SQL is built into Oracle Database and serves the purpose of enabling the user to have a deep-level diagnostic view of information related to a particular SQL statement. It is intended to replace (and expand on) the functionality provided by SQLHC. It is very easy to use and can be executed in all Oracle Database environments.
 

Further readings