Handy commands for Oracle scheduler jobs

 





Things to  verify while creating JOb  :

-- Job is  not auto Drop 
-- Instance stickiness of job 
-- jOBS IS restart able after stop 
 



Known Issues : 

1) NEXT_RUN_DATE is in the past.   to Fix  we  need to alter start date attribute of job and disable and  enable job  
2)  Scheduler jobs are not triggering .  For that we can reset job queue process and   disable/enable scheduler 




Disable Scheduler :

  alter system set job_queue_processes=0 sid='*';
 exec dbms_scheduler.set_scheduler_attribute ('SCHEDULER_DISABLED','TRUE');





Dropping  job :

exec DBMS_SCHEDULER.drop_JOB (job_name => ‘DEMO.HELOS_TEST_JOBS’,force=>true);




Stooping Jobs 

exec DBMS_SCHEDULER.stop_JOB (job_name => 'SERV_STG.HRI_USER_JOB' , force=>true) ;
exec dbms_job.broken(42, TRUE);





Manually Running Job :

   BEGIN
    DBMS_SCHEDULER.run_job(job_name => 'SQLHC.TEST_JOB8',use_current_session => FALSE);
    END;
    /





Disable and Enable Job :

    begin
      dbms_scheduler.disable(name => 'SQLHC.TEST_JOB8');
      end;
      /

      begin
      dbms_scheduler.enable(name => 'SQLHC.TEST_JOB8');
      end;
      /



BEGIN
  -- Enable programs and jobs.
  DBMS_SCHEDULER.enable (name => 'test_stored_procedure_prog');
  DBMS_SCHEDULER.enable (name => 'test_full_job_definition');

  -- Disable programs and jobs.
  DBMS_SCHEDULER.disable (name => 'test_stored_procedure_prog');
  DBMS_SCHEDULER.disable (name => 'test_full_job_definition');
END;
/





Generate DDL of existing Scheduler Job :

set pagesize 299
set long 999
SELECT dbms_metadata.get_ddl('PROCOBJ','YOURJOBNAME', 'YOURJOBOWNER') from dual;





Log to   Capture  and troubleshooting steps  :

1) Collect output of script as per  Doc ID 2077187.1  or Doc 2077182.1 
2) Check alert log and  *cjq* cjq trace files
3) Set 10046    tarce for  job runing 
4) restart scheduler process and reset job queue process
5) set  27402 event trace 
6) try changing attribute of  job 
7) Try mnaually  stopping / runing job 
8)  try disabling/ enabling / re-creating  job .




Create  scheduler  job using a schedule 


-- TO schedule a job, first create a schedule, then a program and then a job

--Create a schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
Schedule_name => 'DAILYBILLINGJOB',
Start_date => SYSTIMESTAMP,
Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=30',
Comments => 'DAILY BILLING JOB'
);
END;
/

-- Create a program
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'DAILYBILLINGJOB',
program_type => 'STORED_PROCEDURE',
program_action => 'DAILYJOB.BILLINGPROC'
number_of_arguments =>0,
enabled => TRUE,
comments => 'DAILY BILLING JOB'
);
END;
/

-- Now create the job:

DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILYBILLINGJOB_RUN',
program_name => 'DAILYBILLINGJOB',
schedule_name => 'DAILYBILLINGJOB_SCHED',
enabled => FLASE,
comments => 'daily billing job'
);
END;
/


-- ENABLE THE JOB

DBMS_SCHEDULER.ENABLE('DAILYBILLINGJOB_RUN');
/




Scheduler shell script in dbms_scheduler using credential store:


-- This feature in available from oracle 12c onward

-- Create an credential store:

BEGIN
dbms_credential.create_credential (
CREDENTIAL_NAME => 'ORACLEOSUSER',
USERNAME => 'oracle',
PASSWORD => 'oracle@98765',
DATABASE_ROLE => NULL, WINDOWS_DOMAIN => NULL,
COMMENTS => 'Oracle OS User',
ENABLED => true
);
END;
/

-- Create the job:
exec dbms_scheduler.create_job(-
job_name=>'myscript4',-
job_type=>'external_script',-
job_action=>'/export/home/oracle/ttest.2.sh',-
enabled=>true,-
START_DATE=>sysdate,-
REPEAT_INTERVAL =>'FREQ=MINUTELY; byminute=1',-
auto_drop=>false,-
credential_name=>'ORACLEOSUSER');






Sample of creating Job:

SQL> exec dbms_scheduler.copy_job('SYS.CLEANUP_ONLINE_IND_BUILD','DBACLASS.CLEANUP_ONLINE_IND_BUILD');



  set echo on;
  set time on;
   begin
     dbms_scheduler.create_job (
     job_name => 'SQLHC.TEST_JOB8',
     job_type => 'PLSQL_BLOCK',
     job_action => 'BEGIN dbms_output.put_line(''Hello''); END;',
     start_date => systimestamp,
     end_date =>NULL,
     repeat_interval=> 'FREQ=MINUTELY; INTERVAL=05',
     auto_drop => FALSE,
     enabled => TRUE);
    commit;
    end;
    /


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_procedure; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/




Schedule Job as below.
   
BEGIN  
DBMS_SCHEDULER.create_job (  
job_name    => ‘GATHER_TABLE_STATS_EMP’,  
job_type    => ‘PLSQL_BLOCK’,  
job_action   => ‘begin dbms_stats.gather_table_stats(ownname =>”USERNAME”,tabname =>”EMP”,estimate_percent => 35,degree => 8,CASCADE=>TRUE,force => TRUE); END;’,  
start_date   => ’29-NOV-11 11.00.00AM US/eastern’,  
repeat_interval => ‘FREQ=WEEKLY;INTERVAL=2;BYDAY=SAT;BYHOUR=11;BYMINUTE=0’,  
end_date    => NULL,  
enabled     => TRUE,  
comments    => ‘GATHER STATS ON EMPTABLE’);  
END;  
/


-->  scheduling stats job 

--Procedure
create or replace 
PROCEDURE p_manual_gather_table_stats AS
TYPE ttab
IS
    TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
    ltab ttab;

BEGIN
    ltab(1) := 'TAB1';
    ltab(2) := 'TAB2';
    FOR i IN ltab.first .. ltab.last
    LOOP
        dbms_stats.gather_table_stats(ownname => USER, tabname => ltab(i) , estimate_percent => dbms_stats.auto_sample_size, 
        method_opt => 'for all indexed columns size auto', degree =>
        dbms_stats.auto_degree ,CASCADE => TRUE );
    END LOOP;
END p_manual_gather_table_stats;

--Scheduled Job
BEGIN
    -- Job defined entirely by the CREATE JOB procedure.
    DBMS_SCHEDULER.create_job ( job_name => 'MANUAL_GATHER_TABLE_STATS', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'BEGIN p_manual_gather_table_stats; END;', 
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=12;BYMINUTE=45;BYSECOND=0', 
    end_date => NULL, 
    enabled => TRUE, 
    comments => 'Job to manually gather stats for tables: TAB1,TAB2. Runs at 12:45 Daily.');
END;
/


Change Attribute of  Jobs :

select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
-- seconds 
 begin
dbms_scheduler.set_attribute (
name => 'my_job',
attribute => 'start_date',
value => sysdate+300/86400);
end;
/



BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'my_emp_job1',
attribute => 'repeat_interval',
value => 'FREQ=MINUTELY; INTERVAL=14');
END;
/


BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'my_emp_job1',
attribute => 'repeat_interval',
value => 'FREQ=MINUTELY; BYMINUTE=0,15,30,45;BYSECONDS=0');
END;
/



EXEC DBMS_SCHEDULER.SET_ATTRIBUTE ('TESTME', 'START_DATE',  TO_TIMESTAMP_TZ('20-01-2011 11:30:00 AM +11','DD-MM-YYYY HH:MI:SS AM TZR'));


DECLARE
 v_new_next_date TIMESTAMP WITH TIME ZONE;
BEGIN
  SELECT TO_TIMESTAMP_TZ(TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY')||' 08:00:00 PM +00:00') 
    INTO v_new_next_date
    FROM dba_scheduler_jobs
   WHERE job_name = 'RMAN_BACKUP'; 
  DBMS_SCHEDULER.SET_ATTRIBUTE(NAME=>'RMAN_BACKUP', ATTRIBUTE=>'START_DATE',VALUE=>v_new_next_date);
END;
/



BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'test_full_job_definition' ,
attribute => 'RESTARTABLE' ,
value => TRUE
);
END;
/



BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE
    ( NAME      => '<job_name>'
     ,attribute => 'AUTO_DROP'
     ,VALUE     => FALSE);
END;
/ 



exec dbms_scheduler.set_attribute('SQLHC.TEST_JOB8', 'INSTANCE_STICKINESS', FALSE);


 
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'test_full_job_definition' ,
attribute => 'raise_events' ,
value => dbms_scheduler.job_stopped
);
END;
/



BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'test_full_job_definition' ,
attribute => 'max_run_duration' ,
value => numtodsinterval(13,'minute')
);
END;
/



BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'test_full_job_definition' ,
attribute => 'schedule_limit' ,
value => numtodsinterval(13,'minute')
);
END;
/


BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'test_full_job_definition' ,
attribute => 'service' ,
value => 'db_app'
);
END;
/





Grants  needed to  schedule  jobs in scheduler :


grant create job to ABD_JOBS ;
grant execute on DBMS_SCHEDULER to ABD_JOBS;
grant create session to ABD_JOBS ;
grant EXECUTE on SYS.DBMS_JOB to ABD_JOBS;
grant AQ_ADMINISTRATOR_ROLE to ABD_JOBS;
grant MANAGE SCHEDULER to ABD_JOBS;




Define Job Classes 


-- Display the current resource consumer groups.
SELECT consumer_group FROM dba_rsrc_consumer_groups;



-- Create a job class.
BEGIN
  DBMS_SCHEDULER.create_job_class (
    job_class_name          =>  'test_job_class',
    resource_consumer_group =>  'low_group');
END;
/


-- Display job class details.
SELECT job_class_name, resource_consumer_group FROM dba_scheduler_job_classes;


BEGIN
  -- Job defined by an existing program and schedule and assigned to a job class.
  DBMS_SCHEDULER.create_job (
    job_name      => 'test_prog_sched_class_job_def',
    program_name  => 'test_plsql_block_prog',
    schedule_name => 'test_hourly_schedule',
    job_class     => 'test_job_class',
    enabled       => TRUE,
    comments      => 'Job defined by an existing program and schedule and assigned toa job class.');

  DBMS_SCHEDULER.set_attribute (
    name      => 'test_prog_sched_job_definition',
    attribute => 'job_class',
    value     => 'test_job_class');
END;
/





Create Event Based Jobs :

bEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS_TEST'
,start_date => TO_TIMESTAMP_TZ('2023/02/06 00:05:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => FREQ=MINUTELY;INTERVAL=3'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'logger.read_message;'
,comments => NULL
,enabled =>TRUE ,
auto_drop => false
);
dbms_scheduler.set_attribute('SYS_TEST','RESTARTABLE',true );
dbms_scheduler.set_attribute( name => 'SYS_TEST', attribute => 'schedule_limit', value => numtodsinterval(2, 'minute'));
dbms_scheduler.set_attribute( name => 'SYS_TEST', attribute => 'max_run_duration', value => numtodsinterval(2, 'minute'));
dbms_scheduler.set_attribute( name => 'SYS_TEST', attribute => 'raise_events', value => dbms_scheduler.job_stopped);
DBMS_SCHEDULER.set_attribute (name=> 'SYS_TEST',attribute => 'instance_stickiness', value => false);
dbms_scheduler.add_event_queue_subscriber('DATA_EVENT');
dbms_scheduler.create_job('logger_kill',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_SCHEDULER.stop_job(''SYS_TEST'',TRUE); END;',
event_condition => 'tab.user_data.object_name = ''SYS_TEST'' and tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
queue_spec => 'sys.scheduler$_event_queue,DATA_EVENT',
enabled => true ,
auto_drop => false);
dbms_scheduler.set_attribute('SYS_TEST_STOP_JOB','RESTARTABLE',true );
DBMS_SCHEDULER.set_attribute (name=> 'SYS_TEST_STOP_JOB',attribute => 'instance_stickiness', value => false);
END;
/

bEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'GRTLOGGER'
,start_date => TO_TIMESTAMP_TZ('2023/02/06 00:05:00.000000 America/New_York','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=15'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'logger.read_message'
,comments => NULL
,enabled =>TRUE ,
auto_drop => false
);
dbms_scheduler.set_attribute('LOGGER','RESTARTABLE',true );
dbms_scheduler.set_attribute( name => 'LOGGER', attribute => 'schedule_limit', value => numtodsinterval(14, 'minute'));
dbms_scheduler.set_attribute( name => 'LOGGER', attribute => 'max_run_duration', value => numtodsinterval(14, 'minute'));
dbms_scheduler.set_attribute( name => 'LOGGER', attribute => 'raise_events', value => dbms_scheduler.job_stopped);
DBMS_SCHEDULER.set_attribute (name=> 'LOGGER',attribute => 'instance_stickiness', value => false);
dbms_scheduler.add_event_queue_subscriber('DATA_EVENT');
dbms_scheduler.create_job('LOGGER_KILL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_SCHEDULER.stop_job(''GRTLOGGER'',TRUE); END;',
event_condition => 'tab.user_data.object_name = ''LOGGER'' and tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
queue_spec => 'sys.scheduler$_event_queue,DATA_EVENT',
enabled => true ,
auto_drop => false);
dbms_scheduler.set_attribute('LOGGER_KILL','RESTARTABLE',true );
DBMS_SCHEDULER.set_attribute (name=> 'LOGGER_KILL',attribute => 'instance_stickiness', value => false);
END;
/




Check Current Running  Jobs :

select sid , job ,   this_date , instance , failures  from dba_jobs_running ; 

SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

select    jr.job,    s.username,   s.sid,    s.lockwait,    s.logon_time from 
  dba_running_scheduler_jobs  jr,    v$session s where    jr.sid = s.sid order by    jr.job ;


select
   jr.job,
   s.username,
   s.sid,
   s.lockwait,
   s.logon_time
from 
   dba_jobs_running jr,
   v$session s
where
   jr.sid = s.sid
order by
   jr.job ;


exec dbms_output.put_line('========== Session Details ==========');
set feedback on
set lines 300
set pages 1000
column TERMINAL format a30
column USERNAME format a15
column OSUSER format a20
column MACHINE format a20
column PROGRAM format a30
select inst_id,sid,serial#,username,osuser,last_call_et , machine,terminal,program,status,to_char(logon_time, 'dd-mon-yy hh24:mi:ss') , event, blocking_session, final_blocking_session , sql_id 
from gv$session 
where PROGREAM LIKE '%J00%'
order by LOGON_TIME,machine,program;






Check Scheduled Jobs :

SELECT repeat_interval  , job_name,  START_DATE, LAST_START_DATE, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS  WHERE job_name = 'RUN_REFRESH'; 


select OWNER, JOB_NAME,  state, ENABLED,INSTANCE_STICKINESS , auto_drop , restart_on_failure ,TO_CHAR(NEXT_RUN_DATE ,'DD-MON-YYYY HH24:MI:SS')      from dba_scheduler_jobs where owner='ABC';

select OWNER, JOB_NAME, ENABLED,SCHEDULE_TYPE,LAST_START_DATE, NEXT_RUN_DATE,JOB_STYLE, auto_drop, repeat_interval from dba_scheduler_jobs where JOB_NAME='TEST_JOB8';

select owner ,   job_action   from dba_scheduler_jobs   where  job_name='JOB_NAME';


select state, restart_on_failure, fail_on_script_error, run_count, failure_count, retry_count, next_run_date from DBA_scheduler_jobs  ;

 select OWNER,JOB_NAME,INSTANCE_STICKINESS,INSTANCE_ID from dba_scheduler_jobs where JOB_NAME like 'TEST_JOB';


 set pagesize 200
set lines 299
col START_DATE for a45
col REPEAT_INTERVAL for a45
col schedule_name for a34
select schedule_name, schedule_type, start_date, repeat_interval from
dba_scheduler_schedules;






Check    Job  History :

set pagesize 299
set lines 299
col JOB_NAME for a24
col actual_start_date for a56
col RUN_DURATION for a34
SELECT  job_name, OWNER , status, SESSION_ID , TO_CHAR(log_date ,'DD-MON-YYYY HH24:MI:SS') FROM DBA_scheduler_job_run_details  where job_name='JOB_NAME'   ORDER BY 2 ;

select JOB_NAME,STATUS,REQ_START_DATE,output from dba_scheduler_job_run_details where job_name='TEST_JOB8' order by 3 desc
 

select job_name , log_date , status , actual_start_date , run_duration , error# , additional_info  from 
 DBA_scheduler_job_run_details   where actual_start_date   like '%AUG%'   ORDER by actual_start_date    ; 



col owner     for a10
col job_name  for a20
col job_class for a20
col operation for a16
col status    for a10
col log_date  for a40
select owner      , job_class      , job_name      , operation      ,log_date,status  from dba_scheduler_job_log  where job_name = 'DO_WIN_CMD'  order by 1,2,3;




select a.session , a.sql_id , a.username from v$active_session_history a , dba)users b where a.user_id=b.user_id and a.module='DBMS_SCHEDULER'  AND B.USERNAME='XYZ'  
AND   TO_CHAR(SAMPLE_TIME ,'DD-MON-YYYY HH24:MI:SS')  between '28-JUN-2024 09:36:00' and '29-JUN-2024 09:36:00';


select a.session , a.sql_id , a.username from v$active_session_history a , dba_users b where a.user_id=b.user_id and a.module='DBMS_SCHEDULER'  AND B.USERNAME='XYZ'  
and a.action='JOB_NAME'
AND   TO_CHAR(SAMPLE_TIME ,'DD-MON-YYYY HH24:MI:SS')  between '28-JUN-2024 09:36:00' and '29-JUN-2024 09:36:00';


SELECT  TO_CHAR(A.SAMPLE_TIME ,'DD-MON-YYYY HH24:MI:SS')  , A.SESSION_ID , A.SQL_ID , A.ACTION , B.USERNAME FROM v$active_session_history a,  dba_users b  where a.user_id=b.user_id 
and a.module='DBMS_SCHEDULER' and a.action='JOB_NAME' order by 1 ;




Generate  Report for job :

set markup html on spool on
spool scheduler_check.html
col rdate head "Run Time"
set echo on
alter session set nls_timestamp_tz_format='DD-MON-RR HH.MI.SS AM TZR';
alter session set nls_date_format='DD-MON-RR HH.MI.SS AM';
select sysdate rdate from dual;
show parameter job_queue_processes
select * from dba_scheduler_jobs;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_details where log_date >= sysdate-5 order by log_date desc;
select * from dba_scheduler_window_log order by log_date desc;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details where log_date >= sysdate-5 order by log_date desc;
select * from dba_scheduler_job_log where log_date >= sysdate-5 order by log_date desc;
spool off
set markup html off





SET event to capture more details of Job failure 


You can set the following event which will capture more diagnostic information when the error occurs next.  This will create a trace file in the user_dump_dest or diagnostic_dest location.

alter system set events '27402 trace name context forever, level 255';

Reproduce the issue (or wait till the issue repeats). Once the required trace file is generated, you can disable the event using:

alter system set events '27402 trace name context off';






Purge Job Log : 


BEGIN
  DBMS_SCHEDULER.purge_log (
    log_history => 5,
    which_log   => 'JOB_AND_WINDOW_LOG',
    job_name    => 'my_text_job');

  DBMS_SCHEDULER.purge_log (
    log_history => 15,
    which_log   => 'JOB_LOG',
    job_name    => 'my_text_job_class');

  DBMS_SCHEDULER.purge_log (
    log_history => 0,
    which_log   => 'WINDOW_LOG',
    job_name    => 'my_text_job');
END;
/





References :

Scheduler Jobs Getting Dropped And Not Getting Enabled After Manual Run After Upgrade To 12.2. (Doc ID 2430704.1)


https://oracle-base.com/articles/10g/scheduler-10g



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