Home » Developer & Programmer » Precompilers, OCI & OCCI » It's better keep connected to Oracle or it's better reconnect after work?
It's better keep connected to Oracle or it's better reconnect after work? [message #545169] Mon, 27 February 2012 15:01 Go to next message
Messages: 1
Registered: February 2012
Junior Member

I'm a beginner in the PRO*C and I have this issue

I have to maintain a multithreaded C++ PRO*C application and has the following structure

1) Application runs waiting for requirements
2) When detects a requirement it creates a Thread to work
3) The thread connects to Oracle with a EXEC SQL CONNECT :uid IDENTIFIED BY :pwd; instruction
4) Calls some anonymous calls to execute a stored procedure

:Status:=stored_procedure(:param1, :param2);

5) If anything went wrong then issues a EXEC SQL ROLLBACK WORK RELEASE; then reconnects and the thread die
6) If everything was OK then the thread dies, but never called a EXEC SQL COMMIT WORK RELEASE; to disconnect from Oracle

I don't know why never the application disconnects from Oracle, maybe is for performance, but I don't have anyone to ask

Sometimes the application crashes and I believe that maybe the error is for never freeing the connections, the PROC compile line is this

proc parse=full sqlcheck=full user=system/manager cpp_suffix=cpp iname=source.pc include="..\Hdr" include="C:\ORACLE\ORA81\oci\include" include="C:\ORACLE\ORA81\precomp\public"

Which is the way to implement the connections?, connect and disconnect when done? or just keep the connection alive?

Thanks in advance
Which way has better performance, connect and disconnect or just keep the opened connection?

Re: It's better keep connected to Oracle or it's better reconnect after work? [message #560337 is a reply to message #545169] Thu, 12 July 2012 04:37 Go to previous message
Messages: 15
Registered: July 2012
Junior Member
If what you say is correct and step 3 is unconditional then the application leaks connections. The crash could well be down to that. If it creates a connection at the start of processing, it should remove the connection at the end. Or if it wants to leave the connection open, then the EXEC SQL CONNECT should be conditional (e.g. if (!connection_exists) EXEC SQL CONNECT...; )

Opening a connection, doing some processing then closing that connection is one way to use connections and possibly the simplest at a conceptual level. It's not necessarily the "best", but what is best depends critically on how you define "best". Is best="the application gets through its work in the shortest possible time", or "has the least impact on the DB", or "simplest code to maintain", or "most optimal code", etc etc etc.....

So a starting point could be to modify the code to work that way. As you have nobody to ask, you will have to rediscover any problems the original coder discovered (and you could consider documenting them in the code, not only for others' benefit but also for your own when in 6 months time you're thinking "why did I do that?"). You may even discover that this simple way of working makes your job easier on account of simplifying the code, AND works within the business requirements for the application, in which case that's a result.

If you find the app doesn't meet its requirements then you will need to start looking into why. There are two things that are absolutely essential at this point: you need to find out what the performance requirements are so that you have a quantifiable means of answering whether or not the problem is solved, and profiling. "Best" and "as fast as possible" are not valid because they are not detailed enough; on the other hand "must be able to process 1000 messages per minute" is. Profiling will give you the extent of the problem; if for instance it's processing 100 messages per minute, then you know it needs a 1000% performance boost, and profiling (done properly) will also show you where the holdup is. On the other hand, if you're getting through 1000 messages in 15 minutes, job done! Go do something more interesting. But presumably you have to fix this crash, so a stack trace would be helpful at this point.

[Updated on: Thu, 12 July 2012 04:38]

Report message to a moderator

Previous Topic: Recompile required when upgrading from Oracle 9i to 11g
Next Topic: precompiler pro*COBOL ends with fatal error no prev errors
Goto Forum:

Current Time: Mon Aug 21 22:41:20 CDT 2017

Total time taken to generate the page: 0.10378 seconds