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