Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 13, 2008

Top Ten Mistakes Found in Oracle Systems – Bad connection management –

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 9:14 am

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: