• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle 19c New Feature – SQL_DIAGNOSE_AND_REPAIR (New Automatic Diagnostics and Repair Function)

by admin

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

Filed Under: oracle, Oracle 19c

Some more articles you might also be interested in …

  1. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace
  2. Unable to instantiate disk “ASM_DISK” – error on running ‘oracleasm scandisks’ command
  3. SQL script to find tables that are fragmented
  4. How to Disable AUTOEXTEND Mode on a datafile in Oracle Database
  5. How do stubs work in a WebLogic Server cluster?
  6. How to Install/Uninstall/Upgrade Oracle SQLTXPLAIN (SQLT) Tool
  7. Beginners Guide to Monitoring SQL Statements with Real-Time SQL Monitoring in Oracle Database
  8. Oracle database – How to create pfile or spfile using the current parameters
  9. How to Manage Oracle database Audit File Directory Growth with cron (ASM Instances Only)
  10. How to check and repair ACFS with FSCK

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright