Oracle-DB: New SQL Diagnostic Report in rel. 19.28

One of the new features backported from 23ai into release 19.28 is the SQL Diagnostic Report.
This shows a comprehensive summary of diagnostic information for a certain SQL statement. Included are execution plan info, optimizer statistics, object info, ASH info, SQL Monitor reports and lots more.
The documentation for 19c already includes the new method DBMS_SQLDIAG.Report_SQL.

Which edition or option pack license is needed

Including ASH, SQL Monitor etc. raises the question: What about licensing limitations for use of this function.
As of current documentation there is no Enterprise Addition or additional management pack license needed for execution of DBMS_SQLDIAG.Report_SQL.
The package DBMS_SQLDIAG and it's subroutines are often named as "SQL Repair Advisor".
Looking at Features and Licensing there is no limitation for usage of SQL Repair Advisor and there's also a section for SQL Diagnostic Report confirming no limitation:
The note "How To Use DBMS_SQLDIAG To Diagnose Various Query Issues" (Doc ID 1509192.1) tells "DBMS_SQLDIAG is shipped with the standard Oracle Database Software and usage requires no additional license.", but last update of this note was in 2022.

As a last test I used a fresh 23.9 DB to check if something is recorded in DBA_Feature_Usage_Statistics after use of DBMS_SQLDIAG.Report_SQL.
exec dbms_feature_usage_internal.exec_db_usage_sampling(SYSDATE, test_name => 'hugo');
CREATE TABLE before_diag AS SELECT * FROM DBA_Feature_Usage_Statistics where detected_usages != 0;
-- call DBMS_SQLDIAG.Report_SQL
exec dbms_feature_usage_internal.exec_db_usage_sampling(SYSDATE, test_name => 'hugo');
CREATE TABLE after_diag AS SELECT * FROM DBA_Feature_Usage_Statistics where detected_usages != 0;
The only difference in DBA_Feature_Usage_Statistics is a use of "Oracle Utility Metadata API" that is expected for this function.
Not any other additional feature was marked as used.

Using SQL Diagnostic Report

Executing 
DBMS_SQLDIAG.Report_SQL(SQL_ID => 'my SQL-ID', Level => 'ALL')
returns a CLOB with the generated HTML content.

Using the free analysis tool Panorama for 19.28 or newer there is also a button "Diag. report" now at the page for SQL details which generates this SQL Diagnostic Report for the choosen SQL in a new browser window.

An example for the resulting report you'll find here.
Thanks to Claudia Hüffer for the support in investigation of licensing issues

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo...
Read more

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet...
Read more