Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_SCHEDULER performance issues

HerndonJul 11 2016 — edited Jul 11 2016

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 8 2016
Added on Jul 11 2016
21 comments
6,518 views