Skip to Main Content
  • Questions

  • Adjust connections/sessions Oracle database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: April 03, 2020 - 3:51 am UTC

Last updated: April 06, 2020 - 12:23 am UTC

Version: 18.6

Viewed 1000+ times

You Asked

Hello, Ask Tom Team.

I have a production database running for several months. These are my connection parameters in the client to connect to the database (RAC 18c). I am using a .NET app.

Min Pool Size=1;Max Pool Size=60;Connection Lifetime=180;Connection Timeout=60;Incr Pool Size=5; Decr Pool Size=1;Pooling=true;Load Balancing=false;HA events=true;

I want to know if these parameters are good. I do not want my database drop connections because a bad sizing here.

Note: I have seen some WARNING: inbound connection timed out (ORA-3136) errors in the alert log and I do not know if it's because of some wrong parameters. No ORA-600 or ORA-7445 in alert log.

1. Are some of these parameters good in general?
2. How can I check that these parameters are good using some session/connection history view and based in a peek time? What values should I look for? And then what should I adjust in my connection string?

Thanks in advanced.

and Connor said...

Min Pool Size=1;Max Pool Size=60

immediately raises alarm bells for me. Because if your server can accomodate 60 (without problems) then

a) why would you start at 1? This means your first 60 sessions will pay a connection price
b) why would we drop sessions? This would only make sense if 60 is too much...and if 60 is too much, then you should not have max=60

Toon from the performance group did an in-depth analysis of this



which I'd recommend a watch. But in a nutshell, connection pools normally are best when Min = Max = 'n', where is 'n' is chosen so that the number of *active* sessions (which might be less than 'n') is the number your server can reasonably cope with without starting to thrash the CPU (typically in the 70-80% mark)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Followup

Geraldo, April 05, 2020 - 4:01 am UTC

Thanks.

1. But how do I calculate n?

2. How do I know the number of active sessions my server can handle to get to 70% or 80% CPU?

3. How can I monitor connection pool?

Note: my session parameter is 7520.

Thanks in advanced.
Connor McDonald
April 06, 2020 - 12:23 am UTC

The database will capture a critical statistic here for you. "Average Active Sessions".

Here's a great slide deck on the background and importance of that statistic

https://www.slideshare.net/jberesni/average-active-sessions-rmoug2007-37554537

Note that "connected sessions" is not the same as "active sessions". Active is someone doing something on the database *right now*, not just sitting there connected.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library