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, populates 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 recommendations 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_ID ------------- 9at61axfvkraj 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; DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK(:INCIDENT_ID) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GENERAL INFORMATION SECTION --------------------------------------------------------------------------------------------- Task Information: --------------------------------------------- Task Name : 31202 Task Owner : W5 Execution Name : 31202 Execution Type : SPM EVOLVE Scope : COMPREHENSIVE Status : COMPLETED Started : 03/01/2020 07:36:24 Finished : 03/01/2020 07:36:26 Last Updated : 03/01/2020 07:36:26 Global Time Limit : 1800 Per-Plan Time Limit : UNUSED Number of Errors : 0 --------------------------------------------------------------------------------------------- DETAILS SECTION --------------------------------------------------------------------------------------------- Object ID : 2 Test Plan Name : SQL_PLAN_crsrytw0bmgphf2a85508 Base Plan Name : Cost-based plan SQL Handle : SQL_cbe2fecf00b9beb0 Parsing Schema : W5 Test Plan Creator : W5 SQL Text : select max(col3) from tbl1 where col1=10000 and col2=10000 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The baseline plan matches the non-accepted plan being verified. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => '31202', object_id => 2, task_owner => 'W5'); --------------------------------------------------------------------------------------------- Object ID : 3 Test Plan Name : SQL_PLAN_crsrytw0bmgphf2a85508 Base Plan Name : Cost-based plan SQL Handle : SQL_cbe2fecf00b9beb0 Parsing Schema : W5 Test Plan Creator : W5 SQL Text : select max(col3) from tbl1 where col1=10000 and col2=10000 FINDINGS SECTION --------------------------------------------------------------------------------------------- Findings (1): ----------------------------- 1. The baseline plan matches the non-accepted plan being verified. Recommendation: ----------------------------- Consider accepting the plan. Execute dbms_spm.accept_sql_plan_baseline(task_name => '31202', object_id => 3, task_owner => 'W5'); --------------------------------------------------------------------------------------------- SQL> Execute dbms_spm.accept_sql_plan_baseline(task_name => '31202', object_id => 3, task_owner => 'W5'); PL/SQL procedure successfully completed. SQL> set autotrace on SQL> select max(col3) from tbl1 where col1=10000 and col2=10000; MAX(COL3) ------------------------------ A10000 Execution Plan ---------------------------------------------------------- Plan hash value: 3346416757 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 18 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 18 | | | |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL1 | 1 | 18 | 4 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX1 | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("COL2"=10000) 3 - access("COL1"=10000) Note ----- - dynamic statistics used: statistics for conventional DML - SQL plan baseline "SQL_PLAN_crsrytw0bmgphf2a85508" used for this statement Statistics ---------------------------------------------------------- 21 recursive calls 12 db block gets 3297 consistent gets 0 physical reads 3592 redo size 555 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> set autotrace off