OracleConnections

Oracle Community Network

Oracle performance on Connect/Disconnect for each transaction

Currently trying to optimize some code and noticed that there is a connect being done before each transaction and disconnect is called at the end of the transaction.

In my previous experience I did notice that calling connect is slow. Doing this repetitively is likely to be very costly... Or is it ?

0 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Julian Caroll on August 19, 2013 at 12:43pm

Yes, it's likely to be costly if you're actually creating and tearing down a physical connection for every transaction unless you have extremely long-running transactions. If your transaction is a batch load of a million rows, the cost of opening and closing the connection is probably rather minimal. If, on the other hand, your transaction consists of a single-row insert into a table, the vast majority of your time would be spent opening and closing the connection.

Most of the time, however, you've got a three-tier application where the middle tier maintains a connection pool and when the middle tier code opens and closes a connection, they're really operating on a logical connection rather than a physical connection. Opening the connection takes a connection from the pool, the business layer performs a transaction, and then closing the connection merely returns the connection to the pool. From the database perspective, no physical connection is either opened or closed. If you are actually working with connections from a pool, opening and closing the connection on every transaction is perfectly normal and shouldn't generate much overhead assuming your connection pool is reasonably sized.