Performance problems when large jobs are run

We are an Oracle Financials/Mfg site running Oracle apps 10.7/prod 16 (HP-UX 10.20)and we are experiencing ongoing performance problems when large jobs are run. Some examples are:

Cost rollups for large part volumes never complete due to poor performance (runs for 12 hours and we terminate job.) Smaller volume jobs (less number of parts) run in a reasonable amount of time (usually 4-6 hours.) The significant point here is that in virtually all cases, if the large job is terminated and split into smaller jobs, the smaller jobs complete in reasonable amount of time. The relationship between size of job and length of execution time is not linear and a cliff in performance is hit at some point along the way.

The cost rollup scenario above is echoed in other areas where data is inserted/updated in the database but we have also seen examples where jobs have exhibited the same poor performance when large datasets have been involved but NO UPDATES are made (read this as a report only -- no data changes.) Again, in these cases, we have seen acceptable performance when the jobs have been broken up into smaller pieces and executed separately.

One additional observation we have made is in the read only scenario above, we have found that performance can be dramatically improved by running the job with virtually no other users on the system. This fact leads us to believe the job is competing for a shared resource, although it is unclear what resource is required.

We currently have our shared pool size set at 150M and DB buffers at 25K. I am inclined to bump up the shared pool size but based on gut feel only. If anyone has any insight into our scenario and can suggest potential solutions.... I'd also like to know if there are any guidelines into how to determine whether shared pool size (and overall SGA) are set correctly.

Thanks for any help.

Mr. Dana S. Smith Received on Fri Nov 20 1998 - 03:21:54 CST