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

On Jan 4, 2:28 am, joel garry <joel-ga..._at_home.com> wrote:
> 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 andhttp://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_app...
>
> 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

Hi Joel,

Thanks for your reply. I will like to apologize if I have caused some confusion by the word "problem" in my original post. I haven't faced any problem in accessing the local data using the dblink and I want to know more about any possible pitfall while accessing the local data through the dblink.Moreover, I will like to check the important hints or init.ora parameters that should be checked while building a solution like this.

My remote sources will be on Oracle Enterprise Edition 9.2.0.7.0 on redhat Linux and local instance will be on Oracle 10.2.0.2.0 on Red Hat Enterprise Linux AS release 3. My application will read the data from a source over dblink and will try to fetch a portion of the data using a equijoin using bind variables. I haven't gave any distributed hints (like driving_site) till till now and will like to know what the are different hints I should play with.

Could you please comment on the possible performance problem if any or some ways to check them for the local database access using the dblink?

Thanks again,
--surangshu Received on Thu Jan 03 2008 - 21:03:19 CST