Re: Accessing local database using a dblink in Oracle 9i/10gR2

On Jan 3, 11:10 am, saurangshu <saurang..._at_gmail.com> wrote:
> Hi DB Gurus,
>
> I have an application which tries to access data from more than one
> identically structured databases. Each of the databases has the same
> table with different data which the application reads and tries to
> store in a single repository. Moreover, the source tables can be big
> at times having three millions or more rows. Now, the application
> reads data from the remote databases using a dblink created in the
> runtime. The problem comes when the data source is in the target
> database instance where the application will have to create a dblink
> pointing to the local instance itself.
>
> Does anyone knows about any performance problem in accessing the local
> data using the dblink? I have tried to check the explain plan but the
> cost seems to be identical for a dblink or a direct access for small
> tables. Is there any way to check the performance in this case?
>
> Thanks,
> Saurangshu

Have you tried the driving_site hint?

Remember that the cost isn't really what is important, rather the cardinalities. Are they correct? Are your statistics and histograms correct and appropriate? Everywhere?

What is your version (exact!) and platform? Are they the same everywhere? What is the plan? Are there views involved? Outer joins? Bind variables? Have you checked for bugs?

See metalink Note:33838.1 and http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_appdev004.htm

Are you saying the problem _only_ occurs on the local version? Why don't you just not use the link there, you would not have to send everything through the whole tcp stack conversions. Not all optimizer capabilities can be used over a link.

Have you checked waits? It could be something like the local db isn't sorting as well as the distributed ones, or some other local difference in performance. Perhaps you'll see something like waiting for sqlnet response.

jg

--
@home.com is bogus.
What, no mention of Oracle?  http://www.cio.com/article/print/168401