SRDC - Diagnostic Collection for DataPump Export Performance Issues (Doc ID 1935743.1)

 REM srdc_expdp_performance.sql - Gather Information for EXPDP Performance Issues
define SRDCNAME='EXPDP_PERFORMANCE'
SET MARKUP HTML ON PREFORMAT ON
set TERMOUT off FEEDBACK off verify off TRIMSPOOL on HEADING off
set lines 132 pages 10000
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
set TERMOUT on MARKUP html preformat on 
REM
spool &&SRDCSPOOLNAME..htm
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp:       '||to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine:         '||host_name from v$instance
union all
select '| Version:         '||version from v$instance
union all
select '| DBName:          '||name from v$database
union all
select '| Instance:        '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/

set HEADING on MARKUP html preformat off
REM === -- end of standard header -- ===

set concat "#"
SET PAGESIZE 9999
SET LINESIZE 256
SET TRIMOUT ON
SET TRIMSPOOL ON
Column sid format 99999 heading "SESS|ID"
Column serial# format 9999999 heading "SESS|SER|#"
Column session_id format 99999 heading "SESS|ID"
Column session_serial# format 9999999 heading "SESS|SER|#"
Column event format a50
Column job_name format a35
Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
Column pga_used_mem format 9,999,999,999 
Column pga_alloc_mem format 9,999,999,999
Column status heading 'Status' format a16
Column timeout heading 'Timeout' format 999999
Column error_number heading 'Error Number' format 999999
Column error_msg heading 'Message' format a44 
Column sql_text heading 'Current SQL statement' format a50
Column Number_of_objects format 99999999
Column object_type format a35
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET MARKUP HTML ON PREFORMAT ON

--====================Retrieve sid, serial# information for the active export process(es)===========================
SET HEADING OFF 
SELECT '==================================Determine sid, serial# details for the active DataPump export process(es):=====================================' FROM dual;
SET HEADING ON 
set feedback on
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,  
       s.status, s.username, d.job_name, p.spid, s.serial#, p.pid  
from   v$session s, v$process p, dba_datapump_sessions d 
where  p.addr=s.paddr and s.saddr=d.saddr and
      (UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');  
SET HEADING OFF 
set feedback off
SELECT '=================================================================================================================================================' FROM dual;


--====================Retrieve sid, serial#, PGA details for the active export process(es)===========================
SET HEADING OFF 
SELECT '==================================Determine sid, serial#, PGA details for the active export process(es):=========================================' FROM dual;
SET HEADING ON 
set feedback on
SELECT sid, s.serial#, p.PGA_USED_MEM,p.PGA_ALLOC_MEM
FROM   v$process p, v$session s
WHERE  p.addr = s.paddr and
       (UPPER (s.program) LIKE '%DM0%' or UPPER (s.program) LIKE '%DW0%');
SET HEADING OFF 
set feedback off
SELECT '=================================================================================================================================================' FROM dual;


--====================Retrieve the number of objects per object_type===========================
SET HEADING OFF 
SELECT '==================================Determine the number of objects per object_type:===============================================================' FROM dual;
SET HEADING ON
SELECT count(*) Number_of_objects, object_type 
FROM   dba_objects
GROUP  BY object_type 
ORDER  BY Number_of_objects desc;
SET HEADING OFF 
SELECT '=================================================================================================================================================' FROM dual;


--====================Retrive all wait events and time in wait for the running export process(es)====================
SET HEADING OFF
SELECT '==================================Retrive all wait events and time in wait for the running export process(es):===================================' FROM dual;
SET HEADING ON
set feedback on
select session_id, session_serial#, Event, sum(time_waited) total_waits
from   v$active_session_history
where  sample_time > sysdate - 1 and 
       (UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and 
       session_id in (select sid from v$session where UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and 
       session_state = 'WAITING' And time_waited > 0
group  by session_id, session_serial#, Event
order  by session_id, session_serial#, total_waits desc;
SET HEADING OFF 
set feedback off
SELECT '=================================================================================================================================================' FROM dual;


--====================Export progress - retrieve current sql id and statement====================
SET HEADING OFF
SELECT '==================================Export progress - retrieve current sql id and statement:=======================================================' FROM dual;
SET HEADING ON
set feedback on
select distinct sysdate, a.sid, a.sql_id, a.event, b.sql_text
from   v$session a, v$sql b
where  a.sql_id=b.sql_id and 
       (UPPER (a.program) LIKE '%DM0%' or UPPER (a.program) LIKE '%DW0%')
order  by a.sid desc;
SET HEADING OFF 
set feedback off
SELECT '=================================================================================================================================================' FROM dual;

SET HEADING OFF MARKUP HTML OFF
SET SERVEROUTPUT ON FORMAT WRAP

declare
  table_view_not_exists EXCEPTION; 
  PRAGMA EXCEPTION_INIT(table_view_not_exists, -00942);
  v_ksppinm varchar2(30); 
  CURSOR c_fix IS select v.KSPPSTVL value FROM x$ksppi n, x$ksppsv v WHERE n.indx = v.indx and n.ksppinm = v_ksppinm;
  CURSOR c_count is select count(*) from DBA_OPTSTAT_OPERATIONS where operation in ('gather_dictionary_stats','gather_fixed_objects_stats')
or (operation in ('gather_schema_stats') and target in ('SYS','SYSTEM'));
  CURSOR c_stats is select operation,target, START_TIME, END_TIME from DBA_OPTSTAT_OPERATIONS  
         where operation in ('gather_dictionary_stats','gather_fixed_objects_stats') or (operation in ('gather_schema_stats') and target in ('SYS','SYSTEM')) order by 3 desc;
  CURSOR c_sql_patches_count is select count(*) FROM DBA_REGISTRY_SQLPATCH; 
  CURSOR c_sql_patches IS SELECT PATCH_ID,PATCH_UID,ACTION,STATUS,DESCRIPTION,ACTION_TIME FROM DBA_REGISTRY_SQLPATCH order by ACTION_TIME desc;
  v_long_op_flag number := 0 ;                        
  v_target varchar2(100); 
  v_sid number;
  v_totalwork number;    
  v_opname varchar2(200);
  v_sofar number;                        
  v_time_remain number;  
  stmt varchar2(2000);
  v_fix c_fix%ROWTYPE;
  v_count number;
  v_sql_patches c_sql_patches%ROWTYPE;
  v_sql_patches_count NUMBER;
  v_is_AI number :=0;
  TYPE sessionwait_info_record IS RECORD
  (
  WAITING_SESSION NUMBER,
  EVENT VARCHAR2(64),
  DP_WAITTIME NUMBER,
  DP_SECONDS_IN_WAIT NUMBER,
  DP_STATE_IN_WAIT VARCHAR2(19),
  DP_P1TEXT VARCHAR2(64),
  DP_P1 NUMBER,
  DP_P2TEXT VARCHAR2(64),
  DP_P2 NUMBER,
  DP_P3TEXT VARCHAR2(64),
  DP_P3 NUMBER,
  CON_ID NUMBER
);
  TYPE process_info_record IS RECORD
  (
  CUR_DATE VARCHAR2(19),
  PROGRAM VARCHAR2(84),
  SESSIONID NUMBER,
  STATUS  VARCHAR2(8),
  USERNAME VARCHAR2(128),
  JOBNAME VARCHAR2(128),
  SPID  VARCHAR2(24),
  PROCESSID NUMBER,
  CON_ID NUMBER
);
type sessionwait_info_table is table of sessionwait_info_record;
SESSION_COLLECTOR sessionwait_info_table;
type process_table is table of process_info_record;
PROCESS_COLLECTOR process_table;
begin
  stmt:='select count(*) from v$session_longops where sid in (select sid from v$session where UPPER (program) LIKE '||
        '''%DM0%'''||' or UPPER (program) LIKE '||'''%DW0%'')'||' and totalwork <> sofar';
  DBMS_OUTPUT.PUT_LINE('<pre>');
  dbms_output.put_line('==================================Check v$session_longops - Export pending work:=================================================================');
  dbms_output.put_line(chr(10));
  execute immediate stmt into v_long_op_flag;
  if (v_long_op_flag > 0) then      
    dbms_output.put_line ('The number of long running export processes is:   '|| v_long_op_flag);
    dbms_output.put_line (chr (10));                        
    for longop in (select sid,target,opname, sum(totalwork) totwork, sum(sofar) sofar, sum(totalwork-sofar) blk_remain, Round(sum(time_remaining/60),2) time_remain                        
                   from v$session_longops where sid in (select sid from v$session where UPPER (program) LIKE '%DM0%' or UPPER (program) LIKE '%DW0%') and 
                   opname NOT LIKE '%aggregate%' and totalwork <> sofar group by sid,target,opname) loop
      dbms_output.put_line (Rpad ('Export SID', 40, ' ')||chr (9)||':'||chr (9)||longop.sid); 
      dbms_output.put_line (Rpad ('Object being read', 40, ' ')||chr (9)||':'||chr (9)||longop.target);      
      dbms_output.put_line (Rpad ('Operation being executed', 40, ' ')||chr (9)||':'||chr (9)||longop.opname); 
      dbms_output.put_line (Rpad ('Total blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.totwork);                        
      dbms_output.put_line (Rpad ('Total blocks already read', 40, ' ')||chr (9)||':'||chr (9)||longop.sofar);                        
      dbms_output.put_line (Rpad ('Remaining blocks to be read', 40, ' ')||chr (9)||':'||chr (9)||longop.blk_remain);                        
      dbms_output.put_line (Rpad ('Estimated time remaining for the process', 40, ' ')||chr (9)||':'||chr (9)||longop.time_remain|| ' Minutes'); 
      dbms_output.put_line ('=================================================================================================================================================');
  dbms_output.put_line (chr (10));
    end Loop;
  else
    DBMS_OUTPUT.PUT_LINE ('No export session is found in v$session_longops');
dbms_output.put_line ('================================================================================================================================================='); 
    dbms_output.put_line (chr (10)); 
  end If;

    DBMS_OUTPUT.PUT_LINE ('===========================Have Dictionary,Fixed Objects or Schema statistics for SYS and SYSTEM users been gathered?============================'); 
  dbms_output.put_line (chr (10));
  open c_count;
    fetch c_count into v_count;
    if v_count>0 then
      BEGIN
        DBMS_OUTPUT.PUT_LINE (rpad ('OPERATION', 30)||' '||rpad ('TARGET', 8)||' '||rpad ('START_TIME', 32)||'   '||rpad ('END_TIME', 32));
        DBMS_OUTPUT.PUT_LINE (rpad ('--------------------------', 30)||' '||rpad ('---------', 8)||' '||rpad ('-----------------------------', 32)||'   '||rpad ('-----------------------------', 32));
        FOR v_stats IN c_stats LOOP 
          DBMS_OUTPUT.PUT_LINE (rpad (v_stats.operation, 30)||' '||rpad (nvl(v_stats.target,'         '), 8)||' '||rpad (v_stats.start_time, 32)||'   '||rpad (v_stats.end_time, 32)); 
        END LOOP;
      end;   
    else
      DBMS_OUTPUT.PUT_LINE ('Dictionary,fixed objects or Schema statistics for SYS and SYSTEM users have not been gathered in this database.'); 
    END IF; 
    dbms_output.put_line ('=================================================================================================================================================');
    dbms_output.put_line (chr (10));

  for i in 1..6 loop
    if i = 1 then
      v_ksppinm := 'fixed_date';
    elsif i = 2 then
      v_ksppinm := 'aq_tm_processes';
    elsif i = 3 then
      v_ksppinm := 'compatible';
    elsif i = 4 then
      v_ksppinm := 'optimizer_features_enable';
    elsif i = 5 then
      v_ksppinm := 'optimizer_index_caching';
    elsif i = 6 then
      v_ksppinm := 'optimizer_index_cost_adj';
    end if;
     dbms_output.put_line('==================================Is the '||upper (v_ksppinm)||' parameter set?');
     dbms_output.put_line(chr(10));
    open c_fix; 
    fetch c_fix into v_fix;
    close c_fix;
    if nvl (to_char (v_fix.value), '1') = to_char ('1') then
      DBMS_OUTPUT.PUT_LINE ('No value is found for '||upper (v_ksppinm)||' parameter.');
    else
      DBMS_OUTPUT.PUT_LINE ('The '||upper (v_ksppinm)||' parameter is set for this database and the value is: '||v_fix.value);
    end if;
    dbms_output.put_line('=================================================================================================================================================');
    dbms_output.put_line(chr (10));
  end loop;

  DBMS_OUTPUT.PUT_LINE ('=================================================================SQL database patches============================================================'); 
  open c_sql_patches_count;
  fetch c_sql_patches_count into v_sql_patches_count;
  if v_sql_patches_count>0 then
    BEGIN
      DBMS_OUTPUT.PUT_LINE (rpad ('PATCH_ID', 10)||' '||rpad ('PATCH_UID', 10)||' '||rpad ('ACTION', 14)||' '||rpad ('STATUS', 15)||' '||rpad ('DESCRIPTION', 62)||' '||rpad ('ACTION_TIME', 29));
      DBMS_OUTPUT.PUT_LINE (rpad ('----------', 10)||' '||rpad ('----------', 10)||' '||rpad ('---------------', 14)||' '||rpad ('----------------', 15)||' '||rpad ('-----------------------------------------------------------------', 62)||' '||rpad ('-------------------------------', 29));
      FOR v_sql_patches IN c_sql_patches LOOP 
        DBMS_OUTPUT.PUT_LINE (rpad (v_sql_patches.PATCH_ID, 10)||' '||rpad (v_sql_patches.PATCH_UID, 10)||' '||rpad (v_sql_patches.ACTION, 14)||' '||rpad (v_sql_patches.STATUS, 15)||' '||rpad (v_sql_patches.DESCRIPTION, 62)||' '||rpad (v_sql_patches.ACTION_TIME, 29)); 
      END LOOP;
    end;   
  else
    dbms_output.put_line (chr (10));
    DBMS_OUTPUT.PUT_LINE ('There are no SQL patches installed in this database');     
  END IF; 
  DBMS_OUTPUT.PUT_LINE ('================================================================================================================================================='); 
  dbms_output.put_line(chr (10));

  stmt:='select count(*) from v$version where banner like ''%23ai%''';
  execute immediate stmt into v_is_AI;
  if (v_is_AI > 0) then      
   dbms_output.put_line('====================================Check v$datapump_sessionwait_info - Export waits for each session attached===================================');
   dbms_output.put_line (chr (10));
   v_count:=0;
   DBMS_OUTPUT.PUT_LINE (rpad ('SESS|ID', 7)||' '||rpad ('EVENT', 23)||' '||rpad ('DP_WAITTIME', 12)||' '||rpad ('DP_SECONDS_IN_WAIT', 19)||' '||rpad ('DP_STATE_IN_WAIT', 17)||' '||rpad ('DP_P1TEXT', 12)||' '||rpad ('DP_P1', 12)||' '||rpad ('DP_P2TEXT', 12)||' '||rpad ('DP_P2', 12)||' '||rpad ('DP_P3TEXT', 18)||' '||rpad ('DP_P3', 12)||' '||rpad ('CON_ID', 6));
      DBMS_OUTPUT.PUT_LINE (rpad ('--------', 7)||' '||rpad ('-----------------------', 23)||' '||rpad ('---------------', 12)||' '||rpad ('-------------------', 19)||' '||rpad ('-----------------', 17)||' '||rpad ('-------------', 12)||' '||rpad ('-------------', 12)||' '||rpad ('-------------', 12)||' '||rpad ('-------------', 12)||' '||rpad ('-----------------------', 18)||' '||rpad ('-------------', 12)||' '||rpad ('-------', 6));  
   END if;
   EXECUTE IMMEDIATE  'select WAITING_SESSION,EVENT,DP_WAITTIME,DP_SECONDS_IN_WAIT,DP_STATE_IN_WAIT,DP_P1TEXT,DP_P1,DP_P2TEXT,DP_P2,DP_P3TEXT,DP_P3,CON_ID from V$DATAPUMP_SESSIONWAIT_INFO' BULK COLLECT INTO SESSION_COLLECTOR;    
   for indx in 1..SESSION_COLLECTOR.count loop
     dbms_output.put_line (chr (10));
     dbms_output.put_line(rpad(SESSION_COLLECTOR(indx).WAITING_SESSION,7) || ' ' || rpad(SESSION_COLLECTOR(indx).EVENT,23) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_WAITTIME,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_SECONDS_IN_WAIT,19) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_STATE_IN_WAIT,17) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P1TEXT,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P1,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P2TEXT,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P2,12) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P3TEXT,18) || ' ' || rpad(SESSION_COLLECTOR(indx).DP_P3,12) || ' ' || rpad(SESSION_COLLECTOR(indx).CON_ID,6));
     v_count:=1;
   end loop;

   if (v_count=0) then
    dbms_output.put_line('No sessions attached found');
    dbms_output.put_line (chr (10)); 
   END if;
  dbms_output.put_line ('=================================================================================================================================================');
  dbms_output.put_line (chr (10));

  dbms_output.put_line('=======================================Check v$datapump_process_info - Display information for each process======================================');
  dbms_output.put_line (chr (10));
  v_count:=0;
  DBMS_OUTPUT.PUT_LINE (rpad ('CUR_DATE', 20)||' '||rpad ('PROGRAM', 40)||' '||rpad ('SESSIONID', 9)||' '||rpad ('STATUS', 8)||' '||rpad ('USERNAME', 9)||' '||rpad ('JOBNAME', 25)||' '||rpad ('SPID', 9)||' '||rpad ('PROCESSID', 9)||' '||rpad ('CON_ID', 6));
  DBMS_OUTPUT.PUT_LINE (rpad ('-------------------', 20)||' '||rpad ('----------------------------------------', 40)||' '||rpad ('----------', 9)||' '||rpad ('---------', 8)||' '||rpad ('----------', 9)||' '||rpad ('-------------------------', 25)||' '||rpad ('----------', 9)||' '||rpad ('----------', 9)||' '||rpad ('-------', 6));
   EXECUTE IMMEDIATE  'select CUR_DATE,PROGRAM,SESSIONID,STATUS,USERNAME,JOBNAME,SPID,PROCESSID,CON_ID from V$DATAPUMP_PROCESS_INFO' BULK COLLECT INTO PROCESS_COLLECTOR;
   for indx in 1..PROCESS_COLLECTOR.count loop
     dbms_output.put_line (chr (10));
     dbms_output.put_line(rpad (PROCESS_COLLECTOR(indx).CUR_DATE,20) || ' ' || rpad (PROCESS_COLLECTOR(indx).PROGRAM,40) || ' ' || rpad (PROCESS_COLLECTOR(indx).SESSIONID,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).STATUS,8) || ' ' || rpad (PROCESS_COLLECTOR(indx).USERNAME,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).JOBNAME,25) || ' ' || rpad (PROCESS_COLLECTOR(indx).SPID,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).PROCESSID,9) || ' ' || rpad (PROCESS_COLLECTOR(indx).CON_ID,6));
     v_count:=1;
   end loop;
   if (v_count=0) then
    dbms_output.put_line('No proceses related to Datapump found');
    dbms_output.put_line (chr (10));
   END if;
  dbms_output.put_line ('=================================================================================================================================================');
  dbms_output.put_line (chr (10));
  EXCEPTION
     WHEN table_view_not_exists THEN
     null;      
end;
/


set feedback off
begin
  DBMS_OUTPUT.PUT_LINE ('====================================Current instance parameter values relevant for DataPump Export(EXPDP) issues================================='); 
end;
/

set feedback on
SET HEADING ON
set linesize 150
set pagesize 120
COL name FORMAT A40
COL value FORMAT A30
SELECT n.ksppinm name, v.KSPPSTVL value FROM x$ksppi n, x$ksppsv v
WHERE n.indx = v.indx AND n.ksppinm IN ('aq_tm_processes','__streams_pool_size','streams_pool_size','sga_target','memory_target','memory_size') 
ORDER BY 1;

set feedback off
SET HEADING OFF
SET LINESIZE 256
begin
  dbms_output.put_line ('=================================================================================================================================================');
  DBMS_OUTPUT.PUT_LINE('</pre>');
end;
/

spool off
PROMPT
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm

exit

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more