Re: Performance problems when large jobs are run

There are a lot of things which add up to poor performance with Oracle Applications. As you may know, most of the activity for online processing occurs as packages and procedures. If your shared pool is too small, then you may be swapping these packages in and out of memory and you may also suffer because data that could be shared by different processes can't stay in memory very long so you keep reading the same data from disc, over and over.

I usually run the SGA around 500 Mb. I also pin all of the packages into the SGA. With the packages pinned in the SGA, they will not have to parsed over and over, they will already be ready for execution because the parsing will have already been completed during the pinning. Parsing is a very expensive process. This alone will consume a good deal of the SGA, 150+ Mb. The rest can be used by the end user community and concurrent processing. Hopefully some of the data used over and over will remain in longer and not require lots of disk IO.

Next, you should run SQL*Trace on the Cost Rollup. Identify the most costly SQL statements and tune them if possible. The number of items in MTL_SYSTEM_ITEMS, BOM_BILL_OF_MATERIALS, BOM_INVENTORY_COMPONENTS tables can make a difference as well as the number of levels in this large bill. The number of CPU's on your machine, the speed of the CPU's, the amount of memory, the spread of the data across the drives, the number of concurrent processes, and the number of online users play a part in degrading performance.

I have only scrapped the tip of the iceberg but I hope this helps a little. Do increase your SGA and pin those packages. Especially if you are on 10SC.

Dana Smith wrote:

> 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 Tue Nov 24 1998 - 22:26:24 CST