SQL & PL/SQL

Hi

I am running a large update using dbms_parallel_execute to create chunks and then scheduling jobs to call a procedure which does the update.

job_queue_processes parameter value is 40

dbms_parallel_execute.create_chunks_by_number_col(task_name    => lv_task_name

                                                    ,table_owner  => 'USER'

                                                    ,table_name   => 'TAB_NAME'

                                                    ,table_column => 'APPLICATION_ID'

                                                    ,chunk_size   => 2000);

ln_count :=0;

ln_thread_cnt :=48;

FOR i IN 1 .. ln_thread_cnt

   LOOP

      ln_count := ln_count + 1;

      dbms_scheduler.create_job(job_name   => lv_task_name || '_' ||

                                              ln_count

                               ,job_type   => 'PLSQL_BLOCK'

                               ,job_action => 'BEGIN upd_tab(''' ||

                                              lv_task_name || ''',' ||

                                              ln_count || ', ' ||

                                              ln_thread_cnt ||

                                              '); END;'

                               ,enabled    => TRUE

                               ,auto_drop  => TRUE);

      dbms_lock.sleep(1);

   END LOOP;

I see that after running for couple of hours, the assigned chunk counts remains 8 however it was 48 initially. The job queue processes parameter is still 40 and I can see no other jobs are currently running.

select count(*) from user_scheduler_jobs where state = 'RUNNING';  -- returns 8

STATUS           COUNT(*)

ASSIGNED       8

UNASSIGNED 8380

PROCESSED  49742

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production