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