poor performance over local db_links

[jkprice]

We have an ASP web application that experiences very poor performance when running over db links.
I have setup 2 seperate environments to test the setup.
Env 1: a single database with 3 different application schemas where the various queries join tables between the three users.
Env 2: three seperate databases each with its own application user and the queries join tables accross the db_links.

Both of these setups are on the same server(solaris 7) running off the same oracle home(8.1.7) with an identical datafile layout and the same init.ora parameters across all instances. The only difference is the second setup connects the seperate schemas with db_links.
Unfortunately, the response time for the db link setup is about 10-15 seconds per query while the response time for the single db setup is only about 1 or 2 seconds per query. (the times are similar through sql*plus)
I could understand if they were traveling accross a network, but the databases are on the same server!
Any idea why these db_links are performing so poorly?
I couldn't find anything relevent on Metalink.
Thanks in advance.

[sambavan]

If all the schemas are on the same instance, why would you want to create a db_link, instead you can give grant select/insert/update for the other user who ever wants to access the other users schema. To grant these privileges to the other user, you have to be the schema owner.

Sam

[jkprice]

sambavan,
You can't grant privileges on objects in another database without connect to it through a db_link.
Note, my original post says there are 3 seperate databases in the second test environment.

[pangul]

I think that db_link don't use a persistant connection and it connects for every time it is used, correct me if I am wrong, cause I have not tested, I was having same problem using db_link.

Thanks

[justadba]

You need to play with the code on this one. If your driving table for the query is on the local database, I believe that all distributed queries will be done with a full table scan. So depending on the size of your distributed tables, you may want to force the driving table to be a remote table or create a procedure that brings over the remote data first and then works it within the local database. Just an idea.

[sambavan]

Ha ha!! the mis-interpretation. In oracle terminology, I would call different databases to be "instances". When on say different databases, it could be in the same instance or of in the different instance. Did you take a look into replication?

Sam