Oracle Database 19c - SQL_DIAGNOSE_AND_REPAIR

In Oracle Database 19c, a new function SQL_DIAGNOSE_AND_REPAIR is introduced to diagnose a given SQL statement for a given SQL for the given problem type. This function creates an incident, populate incident metadata with required information like, SQL ID, SQL text, compilation environment, and so on. It also creates a diagnostic task, executes it and accepts recommendation for a given SQL.

For example:

SQL> select max(col3) from tbl1 where col1=10000 and col2=10000;

MAX(COL3)

------------------------------

A10000

SQL> column sql_id new_value sql_id

SQL> select prev_sql_id as sql_id from v$session where sid = userenv('sid');

 

SQL> var incident_id number;

SQL> exec :incident_id := dbms_sqldiag.sql_diagnose_and_repair(sql_id => '&sql_id',scope=>DBMS_SQLDIAG.SCOPE_COMPREHENSIVE,time_limit=>DBMS_SQLDIAG.TIME_LIMIT_DEFAULT,problem_type=>DBMS_SQLDIAG.PROBLEM_TYPE_PERFORMANCE,auto_apply_patch=>'NO');

PL/SQL procedure successfully completed.

SQL>

SQL> select DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK(:incident_id) from dual;

SQL> set autotrace off

Reference : 

1)  19c New Feature - New Automatic Diagnostics and Repair Function SQL_DIAGNOSE_AND_REPAIR (Doc ID 2644400.1)