Re: Controlling load

On 2/24/23 06:45, Mark W. Farnham wrote:
>
> Another thought, since you know the source of the flood is
> “prog_batch”, is to have prog_batch query the current number of
> sessions, submit a maximum of <your decided limit of sessions> -
> <current number of sessions> when that result is positive, go to sleep
> for a while (a few seconds? your average batch_prog emitted job run
> time?), and repeat, reverting to your current sleep time or invocation
> method when the queue of jobs not yet submitted reaches zero.
>
> Starting sessions is relatively expensive. Avoiding piling on load
> often results in all the jobs being complete sooner even though some
> start later. And if I understand you your GOAL is to minimize the
> chances that jobs submitted by “prog_batch” will slow down the results
> of interactive users.
>
> Whether this approach or the valid suggestion of Ilmar achieves your
> goal depends a lot on whether the load of the bursty job submission
> causes the visible delays or ongoing resource consumption of the jobs
> submitted by “prog_batch.” IF memory serves, resource control doesn’t
> begin until the session is established, so if the delays being noticed
> are caused by the burst of logons resource control could make that
> worse. On the other hand if it IS the resource consumption of the
> running jobs that is the problem figuring out how to implement Ilmar’s
> suggestion is a superior gate to hoping that a count of sessions limit
> will be enough.
>
> From what you have described though, it seems to me that “prog_batch”
> is implemented as a denial of service attack against yourself,
> slamming the database with session initiation overhead.
>
> mwf
>

Mark, creating database resident connection pool ("DRCP" for the lovers of abbreviations) would probably resolve the problem. This is probably the best description of the feature:

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/database-resident-connection-pooling.html#GUID-CF5E3FF4-8BA1-4A83-B64B-1CF8F4AD23C0

If there is a properly maintained connection pool, chances of a problem are very slim.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l