Oracle 23ai Funktionalität in Oracle 19c: SQL Diagnostic Report

updated on

Ebenfalls mit dem RU auf 19.28 steht der SQL Diagnostic Report zur Verfügung. Im Package DBMS_SQLDIAG gibt es eine neue Funktion mit dem Namen REPORT_SQL. Diese Funktion liefert alle relevanten Informationen zu einem SQL-Statement, gesammelt in einem CLOB, das ein HTML File mit allen Infos enthält.

Gehen wir einmal Schritt für Schritt vor. Wir benötigen ein SQL-Statement samt dessen SQL_ID – am einfachsten direkt im SQLPlus:

set tab off
set feedback on sql_id
SELECT d.department_id, d.department_name, 
       trunc(avg(e.salary)) AS avg_sal, max(e.salary) AS max_sal
  FROM departments d join employees e ON d.department_id=e.department_id
 GROUP BY d.department_id, d.department_name;

DEPARTMENT_ID DEPARTMENT_NAME                   AVG_SAL    MAX_SAL
------------- ------------------------------ ---------- ----------
           10 Administration                       4400       4400
           20 Marketing                            9500      13000
           30 Purchasing                           4150      11000
           40 Human Resources                      6500       6500
           50 Shipping                             3475       8200
           60 IT                                   5760       9000
           70 Public Relations                    10000      10000
           80 Sales                                8955      14000
           90 Executive                           19333      24000
          100 Finance                              8601      12008
          110 Accounting                          10154      12008

11 rows selected.

SQL_ID: gdzcjxv6gzw02

Für diese SQL_ID erzeugen wir jetzt einen Report als CLOB

set feedback on
var report clob;
exec :report := dbms_sqldiag.report_sql('gdzcjxv6gzw02'); 

PL/SQL procedure successfully completed.

Und den Inhalt des CLOBs schreiben wir jetzt in ein HTML File:

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

spool my_sqldiag.html replace
SELECT :report report FROM dual;
spool off

Jetzt einfach im Browser der Wahl das my_sqldiag.html File öffnen:

In diesem Report findet man jetzt alle relevanten und interessanten Details zu dem SQL Statement, den betroffenen Tabellen und deren Indizes, ob das Statement schon in Auto Tuning Tasks aufgefallen ist, etc.

Hinweise und weitere Infos

Wenn man versuchen würde, denn Report direkt in eine File zu schreiben, endet das mit einem Fehler:

SELECT dbms_sqldiag.report_sql('gdzcjxv6gzw02') FROM dual;
ERROR:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 1111
ORA-06512: at "SYS.DBMS_SQLDIAG_INTERNAL", line 1651
ORA-06512: at "SYS.DBMS_SQLDIAG", line 2650
ORA-06512: at line 1

Der Grund ist, dass in der Funktion REPORT_SQL das CLOB mittels DML zusammengebaut wird – und das ist in einem SELECT nicht erlaubt.

Für die Nutzung von DBMS_SQLDIAG benötigt man die Rolle ADVISOR. Da Informationen ausgegeben werden, auf die man nur mit einer Lizenz für das Diagnostic Management Pack zugreifen darf – beispielsweise Active Session History sowie DBA_HIST Views – ist davon auszugehen, dass für die Nutzung ebenfalls diese Management Pack Lizenz benötigt.

Referenzen