Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Oracle job submit process problem - help ?!?
We are trying to increase the number of job queues processes to inrease
overall processing times. At present it stands at 1. We have tested the
application to handle four. However the response is not that much better.
Say for example we have 5 procedures P1, P2, P3, P4 and P5 submitted to a Job Queue Process Q1, it takes 40 minutes executing all the procedures sequentially from start to finish.
Submit Start Finish Duration
P1 10:27:12 10:27:55 10:37:54 0:09:59 P2 10:27:33 10:37:54 10:45:28 0:07:34 P3 10:27:56 10:45:28 10:53:01 0:07:33 P4 10:28:24 10:53:01 11:00:33 0:07:32 P5 10:28:55 11:00:33 11:08:02 0:07:29
all 10:27:12 10:27:55 11:08:02 0:40:07
Increasing to 4 background processes we get the following results
Submit Start Finish Duration
P1 11:46:19 11:46:34 12:10:04 0:23:30 P2 11:47:12 11:47:34 12:10:34 0:23:00 P3 11:47:47 11:48:38 12:12:28 0:23:50 P4 11:48:20 12:12:28 12:25:40 0:13:12 P5 11:48:54 12:11:04 12:24:26 0:13:22
all 11:46:19 11:46:34 12:24:26 0:37:52
So it only takes two minutes less. This isn't a sufficient increase in performance, we expected it to take far less time. Clearly more time is being spent per procedure, as it does parts of each whilst doing more than one procedure at a time. We know from using HP measureware that CPU usage did not increase, in fact lower when no of processes increased. Also memory used reamined constant.
So anyone have any ideas as to where I should be digging? Machine or OS bottleneck? Oracle setup with incorrect parameters? Someone suggested look at the parse times, as it could be affected by the use of dynamicc SQL in the procedures with out bind variables - is this right?
Our setup is 7.3.4.3, Solaris 2.6, plenty of CPU and memory resources. And the procedures we submitted as jobs by the same instance. Would it help to submit them from seperate instance (although this is not how our batch procesing is setup to work at the moment).
Thanks for any help.
Haroon
(HKhaliqu_at_Ford.com)
Received on Tue Jan 18 2000 - 09:37:25 CST