2

I have a setup where 180 jobs try to access oracle at the same time, i.e. they don't close connections quickly because they may be waiting for e.g. an ftp connection to open.

From oracle, I get the error: 

Listener refused the connection with the following error:
ORA-12520, TNS:listener could not find available handler for requested type of server

I believe this is due to too many connections open. This also affects the GUI of the application, which refuses login.

How can I determine a safe number of jobs to execute at the same time?

How should I handle the error? I tried to handle it by sleeping and retrying, but the only effect of this seems to be a that the TNS listener is always saturated and that the number of concurrently running jobs increases to 180.

Adder
  • 5,708
  • 1
  • 28
  • 56
  • 1
    Why are you opening a connection to the database before the job can run? Check to see if an FTP connection is available before querying Oracle and only run the job if an FTP connection is secured. – Nathan Feb 10 '17 at 16:11
  • That is a good suggestion, although the connection data for the ftp server come from the database. – Adder Feb 10 '17 at 16:20
  • connection pooling might be a good idea to regulate your app's usage of db connections. Your current setup is basically a DoS attack on your own db. – tbone Feb 10 '17 at 18:20

1 Answers1

1

This is a very good question. In general as a rule of thumb, we recommend the number of connections to Oracle be somewhere between 1x and 10x the number of CPU cores. Have a look at this video put together by the Oracle Real-World Performance team on this subject:

https://www.youtube.com/watch?v=Oo-tBpVewP4&t=39s

BobC
  • 4,208
  • 1
  • 12
  • 15
  • I think the question is about finding out which value was set as maximum and not what is the best setting for it. – user85421 Feb 10 '17 at 17:00
  • @CarlosHeuberger Yes, I realise that, but I still think that my response is useful, since the OP asked "How can I determine a safe number of jobs to execute at the same time?" – BobC Feb 10 '17 at 17:02