Skip to Main Content
  • Questions
  • MultiThreaded Extproc Agent - max_sessions, max_task_threads and max_dispatchers parameters

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 03, 2021 - 5:00 am UTC

Last updated: June 16, 2021 - 7:11 am UTC

Version: 12.1.0.2

Viewed 100+ times

You Asked

Hello Team,

Thanks for all the good work AskTOM is doing.

Can you please help us to better understand about max_sessions, max_task_threads and max_dispatchers configuration parameters of MultiThreaded ExtProc agent and share tips on how to determine optimum values of these parameters?

We have multiple clients on multiple DB servers. Each server has different H/W capacity and different load. Our understanding is that the final optimum values will depend on H/W configuration and number of external procedure calls. However, we are trying to arrive at an initial parameter configuration that can be fine tuned further based on actual situation.

Thanks,
AB

and we said...

There is a good section in the latest doc set about this

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnoci/multithreaded-extproc-agent.html#GUID-94F87818-35D8-46F6-BDC9-C032782D3874

max_dispatchers = CEIL(x/y)
tcp_dispatchers = CEIL(x_tcpip/y)
max_task_threads = CEIL(x/max_sessions)

Where:

x is the maximum number of sessions that can be connected to extproc concurrently.
y is the maximum number of connections that the system can support for each dispatcher.
x_tcpip is the maximum number of sessions that can be connected to extproc concurrently by TCP/IP.
(x - x_tcpip is the maximum number of sessions that can be connected to extproc concurrently by IPC.)


Of course, that same section says

"There is no formula for computing the optimum value of max_sessions" :-)

But I would approach it from two directions and see if you can find a balance in the middle

Direction 1 ( app focus): What is the maximum number of *concurrent* usages of your extproc configuration. For example, if your database session count is (say) 500, then worst case would be all of those sessions issuing external requests at the same time. That would be unlikely, but its a worst case. Probably a better estimate would be to look at the maximum value recorded of instantaneous *active* sessions.

Direction2 (server focus): Given the number of cores on your machine, what is the highest number of active sessions (database *or* external) that can you accommodate without the box melting down. Let's say you have 32 cores, which (roughly) means you can have ~64 cpu-intensive active sessions. If sessions are CPU active around 20% of their duration, that means 5x64 = 320 sessions on the database. If you typically have 250 database sessions from all of your apps, then that would leave 70 for extproc before you are heading into risky territory.

Find the balance between direction 1 and 2 to come up with a good starting point, and then monitor from there.

Rating

  (1 rating)

Comments

Max_Sessions - Programmer's Guide vs Oracle Doc

AB, June 10, 2021 - 4:32 pm UTC

Thank you so much for the reply, Connor.

I checked the shared programmer's guide before submitting this question. However, there is difference between the definition of max_sessions as specificed in this document vs as speciied in the Oracle Doc ID 387503.1.

The guide says that it is "Maximum number of sessions for each task thread". Whereas, the above mentioned doc ID defines it as "total number of concurrent application sessions that have used or is using extproc.".

Our tests indicate that it is working as defined in the Doc ID. For example, with the following configuration, we can at a time connect 2 sessions to the extproc. It doesn't matter that max_task_threads are set to 10:

agtctl set max_dispatchers 1 PLSExtProc
agtctl set max_task_threads 10 PLSExtProc
agtctl set max_sessions 2 PLSExtProc

Can you please share any pointers with us ? Any idea what are we missing ?

Our extproc configuration is exactly as described in the specified Doc ID.

Also, Regarding the formulas to calculate the optimum values, Any idea how to identify Y "the maximum number of connections that the system can support for each dispatcher". It depends on the OS or on H/W or on something else?

Thanks,
AB
Connor McDonald
June 16, 2021 - 7:11 am UTC

I read max sessions as exactly that. Maximum number of *sessions* you can create on the database. Not "per thread" or per anything. I read it a cap of the number of extprocs that will be able to active concurrently.

For "y", it is typically an OS limit (a dispatcher is a single process).

I'm not sure if extproc dispatches appear in v$queue, but if they do, that can be monitored for excessive waits (which is an indication to add more dispatches)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database