Paralel queries in separate threads.

Milan Bacik · Post by **Milan Bacik** » Wed 18 Oct 2006 09:47

I have problem after switching from BDE to ODAC. On BDE we used to exec slow queries in separate thread. After switching to ODAC this query in separate thread probably block main thread. All DB components use one TOraSesion. I tried to set nonblocking to true but the main thread is still blocked. The most of informations on main form are gathered quickly in main thread. Some informations have to execute query whing may long 1 minute. After this query returns result i write it to one control on form. Now user must wait 1 minute to display anything on that form.

Challenger · Post by **Challenger** » Fri 20 Oct 2006 06:59

Multithreaded application should initialize OCI in threaded mode. In this case: "if two threads using OCI calls go to the same connection, then one them could be blocked while the other finishes processing at the server" (OCI documentation). So you should use separate sessions for queries that are executed in the threads.

Milan Bacik · Post by **Milan Bacik** » Mon 23 Oct 2006 13:36

I have created two sessions. One is used normaly in main thread. Second is exclusively used in second thread. When I run query conected to this second session it runs without problems in background. But when i try to execute any query conected to main session it is blocked, until query on second session end. Both sessions have set ThreadSafety = true. Both queries have set nonblocking to default.

Challenger · Post by **Challenger** » Wed 25 Oct 2006 12:08

Please specify the version of Oracle client you use.

Milan Bacik · Post by **Milan Bacik** » Wed 25 Oct 2006 13:19

Oracle client 8.1.7
Oracle server 9.2.0.6

Challenger · Post by **Challenger** » Wed 25 Oct 2006 14:12

This is a known behavior of Oracle 8 Client. There are two global variables, OCIThreaded and OCIMutexed, declared in modue OraCall. By default they are set to True. You can set them to False in initialization section of your main unit. This will solve the problem but can cause other errors.

Milan Bacik · Post by **Milan Bacik** » Wed 25 Oct 2006 15:31

Can help if i use Oracle 9 client ?

Challenger · Post by **Challenger** » Thu 26 Oct 2006 14:03

Yes, this should help.

Milan Bacik · Post by **Milan Bacik** » Thu 26 Oct 2006 14:33

Thank you for your help. After upgrading Oracle Client to 9.2 everything works OK.