In the documentation this is mentioned as: The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. Additionally, simultaneous connections from the same client are also a waste of system and network resources.
Until now I have never experienced a performance problem example similar to this case, but last week one my friends asked me a question and yes as mentioned above I saw how it can make a huge difference. He was doing nearly 20-30 SQL calls over JDBC without connection pooling for each of the business actions, and I advised him to package these calls inside a database function for each business action and call these PL/SQL packages as Callable Statements.
Below is a simple example I tried to set up in order to demonstrate this kind of a case: in test1 we will do 1000 times DUAL calls inside a PL/SQL block where as we will open a connection for each of 1000 DUAL calls in test2.
[oracle@tonghost tmp]$ date ; ./test1.ksh ; date ; Sat Sep 13 11:40:18 EEST 2008 Sat Sep 13 11:40:19 EEST 2008 [oracle@tonghost tmp]$ cat test1.log Session altered. Elapsed: 00:00:00.00 Session altered. Elapsed: 00:00:00.01 PL/SQL procedure successfully completed. Elapsed: 00:00:00.42
in test1 1000 DUAL calls is done under a second inside the database,
[oracle@tonghost tmp]$ date ; ./test2.ksh ; date ; Sat Sep 13 11:50:29 EEST 2008 Sat Sep 13 11:52:27 EEST 2008 [oracle@tonghost tmp]$ cat test2.log Session altered. Elapsed: 00:00:00.00 Session altered. Elapsed: 00:00:00.00 PL/SQL procedure successfully completed. Elapsed: 00:00:00.00
where as in test2 it takes nearly two minutes to do the same task.
Above tests were done on a OEL5 11.1 instance: Bad Connection Management Cost Demo Source Codes
