3

Currently one of our new applications is connecting to an Oracle 11g instance over 30,000 daily (at every user request) via their application server, using the same account. (it will be significantly more when its in production) We are trying to convincing the developers to use a connection pool instead of connect/disconnect. However, I want to make sure, I first understand all the overheads associated with creating a connection.

I know the large volume of connections create a lot of overhead, and a lot of process will have to be spawned. Which results in connect being a fairly expensive operation.

However, what steps are exactly happening with a connect that creates the overhead?

0
1

The problem is the locking in the SGA that effectively cause the the concurrency is going down. This kind of apps make the database a close to single user platform instead of a highly scalable and concurrent accessible database. The cause of this is that the connections have to be maintained in a shared memory table where inserting and deleting means locking and causes serialization. There is a nice video series about this kind of real world performance problems connection pooling Implementing connection pooling in the demo app in the video causes a 10 fold performance upgrade ...

The spawning of the processes does cause a little overhead but that is handled well by the OS.

Next to that, the listener log wil grow quickly, causing every next connection to be slower than the previous one.

If session auditing is enabled - a default - the sys.aud$ table will explode and make your system tablespace grow very rapidly. I see many databases where this is the cause of an enormous system tablespace and sometimes even bigger than the rest of the database, because of this connection problem.

2
  • Appending to the end of a log file, in this case the listener log, does in no way get worse performance-wise as the file grows. If you're going to talk about performance, at least get your facts straight. – Philᵀᴹ Jan 26 '15 at 18:57
  • I have seen this happen on a windows system. Maybe this is better on Unix. On Windows the app came close to a standstill when the listener.log reached 2GB. Connection times went up to minutes. – ik_zelf Jan 26 '15 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.