Dear Experts,
I have an issue related to DBMS_SCHEDULER performance when I ran a PL/SQL procedure parallelly with DBMS_SCHEDULER.create_job. The procedure does process lot for DMLs for processing each staging record.
When I run the procedure serially, It was able to process 900 staging records for minute and the same procedure is processing 450 staging records (each job) for minute when it runs with 5 parallel jobs. The jobs are distributed unevenly on different nodes on RAC environment.
1) What could be the reason for low performance when I run with 5 parallel threads (I am expecting the 5 threads to process 5 * 900 records for minute) ?.
2) Are There Parallel threads processing less no of records for Minute because of resource usage or because of threads accessing same DB tables ?.
3) Can I improve These 5 parallel threads processing if I push them to run on same node in RAC environment ?.
Please suggest some recommendations to improve the parallel threads processing with DBMS_SCHEDULER jobs.
Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
Thanks in Advance.
Herndon.