Oracle dbms_job.submit: Mixing Synchronous and Asy

Question

I have 7 Materialized Views that need to be refreshed on a schedule.

Five of them are data source independent and could be rebuilt asynchronously. I'd like do use something like Tom described here

PROCEDURE refresh_Independent_MViews AS
l_job       BINARY_INTEGER;
BEGIN
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView1'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView2'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView3'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView4'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''IndependentMView5'', method => ''C'') ;') ;
END refresh_Independent_MViews;

Two of them are dependent on some of the first five MViews and need to wait until those have been refreshed. These last two are independent of each other and could be run at the same time.

PROCEDURE refresh_Dependent_MViews AS
l_job       BINARY_INTEGER;
BEGIN
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''DependentMView1'', method => ''C'') ;') ;
  dbms_job.submit (l_job, 'DBMS_MVIEW.REFRESH(list => ''DependentMView2'', method => ''C'') ;') ;
END refresh_Dependent_MViews;

The problem: Calling "refresh_Independent_MViews" returns very quickly after spinning up async jobs to do the work but I can't tell when the individual async jobs are all done with their work.

The question: Is there a way to know when the async jobs spun up by dbms_job.submit are all done so I can know when to start the "refresh_Dependent_MViews" procedure?

Answer 1

The simplest possible approach would be to take the l_job output parameters from dbms_job.submit and then write a loop that checks how many of those job values are in dba_jobs, exits when the count is 0, and otherwise sleeps via a call to dbms_lock.sleep for a reasonable period of time. Obviously, you'd need to avoid overwriting the current l_job variable in order to capture all five jobs. Something like

CREATE TYPE num_tbl
    AS TABLE OF NUMBER;

PROCEDURE refresh_all_MViews AS
  l_job       BINARY_INTEGER;
  l_jobs      num_tbl;
BEGIN
  l_jobs.extend(5);
  dbms_job.submit (l_job, ...) ;
  l_jobs(1) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(2) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(3) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(4) := l_job;
  dbms_job.submit (l_job, ...) ;
  l_jobs(5) := l_job;

  loop
    select count(*)
      into l_cnt
      from dba_jobs
     where job in (select column_value from table(l_jobs));

    if( l_cnt = 0 )
    then
      exit;
    end if;

    dbms_lock.sleep( 10 ); -- Sleep for 10 seconds
  end loop;

  refresh_Dependent_MViews;

END refresh_all_MViews;

Now, you could obviously modify the refresh_Independent_MViews procedure to return the collection of job numbers that need to be monitored so that the refresh_all_mviews procedure calls refresh_independent_mviews, implements the loop, and then calls refresh_dependent_mviews.

You could get more sophisticated by having your jobs write to a table that records success or failure or sending a message via Oracle AQ that another process listens to in order to kick off the dependent mview refresh. That's probably not needed in this case but might be if your dependencies get more sophisticated. Undoubtedly, you could also create a dbms_scheduler chain that would do this for you.

Answer 2

Using DBMS_SCHEDULER chain, steps, and named programs is a much better way to synchronously and asynchronously refresh MViews. For one thing, it provides exact timing control.

What is a better way to do synchronous and asynchronous MView refreshes in Oracle 11g?