Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 23c SQL Diagnostic Report Tips

2023-10-10

Oracle 23c SQL Diagnostic Report Tips

SQL Diagnostic Report

DBMS_SQLDIAG has a new function called REPORT_SQL

  • Generate an HTML deep-level diagnostic report for a SQL statement
  • No requirement to install additional objects/components
  • Plan history, non-default database parameters, stats history, indexes, and more

Test SQL Diagnostic Report

Simple test sql:

drop table t1 purge;
create table t1 as select * from dba_objects;
select /*+ test01 */object_id,object_name,object_type from t1 where object_id = 9527;
create index idx_object_id0 on t1(object_id,0);
select /*+ test01 */object_id,object_name,object_type from t1 where object_id = 9527;
create or replace directory mydir as '/home/oracle';
grant read,write on directory mydir to public;
!ora text2sqlid test01
alter index idx_object_id0 invisible;
select /*+ test01 */object_id,object_name,object_type from t1 where object_id = 9527;
exec dbms_stats.gather_table_stats('system','t1',cascade=>true,no_invalidate=>false);
select /*+ test01 */object_id,object_name,object_type from t1 where object_id = 9527;
declare my_report clob; 
begin  
  my_report := dbms_sqldiag.report_sql('ggyuy60qqy2w2',directory=>'MYDIR',level=>'ALL'); 
end;
/

sql_report01

sql_report02

SQLR_ggyuy60qqy2w2_3rd

 

Referece

DBMS_SQLDIAG has a new function called REPORT_SQL

Have a good work&life! 2023/10 via LinHong


Similar Posts

Comments