CODEWITHSUNDEEP
database-connectiondelayoracle9i
RufusSC2
RufusSC2

Reputation: 273

Is there a maximum number of connections allowed on Oracle9i DB?

This is my very first question and I hope it's well explained and so I can find an answer.

I work at the website project for a delivery company that has all the data in an Oracle9i server. Most of the web user just want to know when they're going to get their package but I'm sure there's also robots that query that info several times a day to update their systems. I'm working on a code to stop those robots (asking for a captcha after the 3rd query in 15min, for example) because we have some web services they can use to query all the data in bulk.

Now, my problem is that peak hours 12.00-14.00 the database starts to answer very slowly. Here is some data I've parsed from the web application. I don't have logs at this level for the web services, but there was also a lot of queries there.

enter image description here

It shows the timestamp when I request a connection from the datasource, the Integer.toHexString(connection.hashCode()), the name of the datasource, the timestamp when I close the connection and the difference between both timestamps. Most of the time the queries end in less than a second but yesterday I had this strange delay for more than 2minutes.

Is there some kind of maximun number of connections allowed on the database so when it surpass that limit the database queues my query for sometime before trying again?

Thanks in advance.

Upvotes: 0

Views: 1083

Answers (2)

RufusSC2
RufusSC2

Reputation: 273

Thanks for the edit vape.

I've also found the real problem. I had the method that asks for a connection to the datasource synchronized and it caused locks while requesting connections at peak hours. I've had it removed and everything is working fine.

Upvotes: 0

Ben
Ben

Reputation: 52893

Is there some kind of maximun number of connections allowed on the databas

Yes.

SESSIONS is one of the basic initialization parameters and

specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system.

The default value is derived from the PROCESSES parameter (1.5 times this plus 22); therefore if you didn't change the PROCESSES parameter (default 100) the maximum number of sessions to your database will be 172.

You can determine the value by querying V$PARAMETER:

SQL> select value
  2    from v$parameter
  3   where name = 'sessions';

VALUE
--------------------------------

480

so when it surpass that limit the database queues my query for sometime before trying again?

No.

When you attempt to exceed the value of the SESSIONS parameter the exception ORA-00018: maximum number of sessions exceeded will be raised.

Something may well be queuing your query but it will be within your own code and not specified by Oracle.

It sounds as though you should find out more information. If not at the maximum number of sessions then you need to capture the query that's taking a long time and profile it; this would, I think, be the more likely scenario. If you're at the maximum number of sessions then you need to look at your (companies) code to determine what's happening.

You haven't really explained anything about your application but it sounds as though you're opening a session (or more) per user. You might want to reconsider whether this is the correct approach.

Upvotes: 3

Related Questions