VB app connecting to Oracle database
I have a custom VB application that connects to an Oracle database (9.2.0.5). It inserts records into an interface table then disconnects from Oracle. The VB app is on about 400 PCs with about 5000 inserts into the interface table daily. My question is having this app connect and disconnect so many times all day long hurting the database? Would it be more efficient for these 400 PCs to just remain connected all day without ever logging out? Any opintions?
Thanks,
Sharon
Software/Hardware used:
Software/Hardware used:
ASKED:
November 2, 2005 8:49 AM
UPDATED:
November 22, 2005 5:10 PM
Answer Wiki:
I also do this type of transactions. I don't think it hurts the database to connect/disconnect. I have found that you have to weigh the problems of each option. The problem with connect/disconnect is that there is a certain amount of wait time for the application to make the connection. If the wait time is quite noticable, you can multiply that time by the number of users and by the number of times they connect and you will see the amount of time wasted by connecting/disconnecting all day long. On the other hand, the problem with leaving the connection is that it is holding resources on the Oracle database. Is your database configured to handle that many connections? Does the server have enough resources to allow that many connections at once?
Hope these thoughts help
Randy
To see all answers submitted to the Answer Wiki: View Answer History.
Leaving a connection open “costs” virtually nothing, in terms of system resources. Closing a connection is “cheap”, too. Establishing a connection is time-consuming. Best to leave the connection open.
—
Sheldon Linker
Linker Systems, Inc.
linkersystems.com
sol@linker.com
800-315-1174
+1-949-552-1904 from outside North America
Thanks for yout thoughts. What prompted this was the following chunck from my statspack report. I searched for mathing sql and found out that it something that happens every time VB makes a connection.
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
————— ———— ————– —— ——– ——— ———-
35,708,923 1,273 28,051.0 18.5 413.14 418.99 496609904
Module: WIP_Mover.exe
select * from (select null TABLE_CATALOG, idx.table_owner TABLE_
SCHEMA, idx.table_name TABLE_NAME, null INDEX_CATALOG, idx.owner
INDEX_SCHEMA, idx.index_name INDEX_NAME, null PRIMARY_KEY, deco
de(idx.uniqueness, ‘UNIQUE’, 65535, 0) UNIQUE_,null CLUSTERED,nu
ll TYPE, null FILL_FACTOR, idx.initial_extent INITIAL_SIZE, null
Often, the main controllable drag in this scenario is the dispatcher’s allocation of stubs to handle the task. Pre-allocated stubs saves some effort at the expense of some memory. Try MTS or consider a another tier between the client and server. The new tier would keep a few more or less constant logins, but keep track of which client is doing what. Using the new security facilities in 9i makes this very slick. In your case, the actual number of clients and transactions makes it hard to cost-justify far-sighted solutions. But, you would know whether your environment is growing or staying static. g’luck.