Database access in parallel threads , viable option?

I have the following situation

main()
{

   hnd = CreateTHread( func1 );

   // Call fun2()

   wait(hnd);

   return ;

}


fu开发者_运维技巧n2()
{
  //Access database to perform some read operation on TAble A
}


func1()
{

//Access database to perform some read operation on TAble A
// Recursive operations
}

What I observed is, fun2() takes a longer duration to complete if I use threading approach. Could it be since the THread func and fuc2 are working on the same table. Note, there is only a read operation in both the functions. The AWR report suggested , number of query exectutions increased when the threading approach was used

---

A similar question - Multi-threaded database read access

Oracle JDBC interfaces as well as Oracle native drivers (i suppose) are capable of handling multithreaded requests ( http://www.cs.umbc.edu/portal/help/oracle8/java.815/a64685/tips1.htm )

However when it comes to implementation in the database engine - this is not clear. From the current documentation my understanding is that as long as your request is READONLY with no intent to update no locking would occur and you should be seeing a performance boost (atleast minor).

There are many other factors however that determine whether the engine would use parallelism. The hardware config of the server (multi-core) etc can also determine if the query engine resorts to a parallel or queued approach.

Btw how much of timing difference are your observing in both approaches that you have tried. What was the magnitude of your data ?

---

Try to change isolation level to the READ COMMITTED and make queries with READ ONLY "clause". It should be less restrictive and allow you to read same tables simultaneously.

---

Try to use two different connections. Multi-threading same connection accesing same table would always degrade performance, you are putting a stress in connection itself. You didn't say which driver you use for accessing, but I've tested a similar approach with JDBC driver in Oracle 10 and had performance degradation, I've guess that SQL commands are somewhat pipelined one after other if you use same connection.

---

Concurrency always make you lose performance. You if need concurrency, you will have to live with this; If you don't need it, you shouldn't use it!

Some operations in SQL like "count" perform LOCKs in tables. Can you share your SQL?