Delphi multiple threads and Oracle db

2
votes

I need to have multiple logins and query executions into an Oracle db, 10 users per process, 10 processes per PC.

I was thinking that I would create 10 threads, one thread per user login.

Is this feasible? Any advice is appreciated.

Very new to threads.

Update:

Thanks for all the comments and answers.

Here are some additional details:

Using Oracle 10.2, Delphi XE, and dbExpress components created on the fly.

Our design is to run 10 processes per machine and simulate 10 user-logins per process. Each login is within its own thread (actually I need to have two logins in each thread, so I am actually creating 200 sessions per machine).

For this simulation exercise, after establishing a connection, each thread retrieves a bunch of data by calling several stored procedures within a loop. For each stored procedure I create a TSQLProcedure object on the fly and close and then free it after using it. Now I am getting ORA1000 Max Cursors exceeded, which I don't understand since I close and free each sp object.

Changing the settings on the server side is out of the question. I saw some documentation that says that on the application side you can set RELEASE_CURSOR=YES. I am guessing that it's an option set at the procedure level.

2
oracledelphithread-safety
If you will run only one client (100 db sessions) it is possible. If you need more clients I think 1000 db sessions will be slower. Maybe you will have better performance if you have active sessions = 2 x cores/threads in server. Also there is limit of simultaneous sessions. – jordani
Which component are you using to connect to Oracle? The thread-safety will depend on the way you are using it. The easiest is to create one connection per thread. You could also use the pool-session feature available in latest OCI versions, but you'll need a direct connection to OCI to handle this. – Arnaud Bouchez
@jordani: the limit on simultaneos sessions is just a server parameters. You can also switch to the "shared server" mode if you have a lot of short sessions. – user160694
Your additional details might get more response if you ask them as a new and more specific question as this one. – Lars Truijens

2 Answers

5
votes

Yes, it is feasible. You may need a thread for each session you need (see here for an explanation), and you have to ensure OCI is called in a thread safe way, how to do it depends on the library you use to call OCI, if you don't call OCI directly.

3
votes

Yes it is feasible. Remember that the UI runs on its own thread and can't be accessed directly by the other threads. Also remember you can't share state between threads unless you secure it. This is a start. Here an example on using threads with databases and the dbGo library. I suggest you give it a try and come back if you have specific questions.