Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Problems Across Database Link

Re: Query Problems Across Database Link

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 24 Oct 2006 13:36:37 GMT
Message-ID: <J7n751.K86@igsrsparc2.er.usgs.gov>


joel garry wrote:

> Brian Peasland wrote:

>> DDayDawg wrote:
>>> I am working with two Oracle 9i databases. One we are using as a data
>>> warehouse to hold selective information from the other (which of course
>>> is being pulled over the database link). The problem is my programmers
>>> are telling me that they cannot pull the data over as the normally
>>> would in a single database environment because the system is hanging.
>>>
>>> The primary table they are pulling from is large, roughly 750,000,000
>>> rows. That is on the foriegn side of the link. So if D1 is the Data
>>> Warehouse and D2 is the Production system the command they are using
>>> is:
>>>
>>> select a.*
>>> from table_a a, /*(in D1)*/
>>> table_b_at_d2 b
>>> where a.active_ind = 1
>>> and b.row_id = a.row_id
>>> and b.updt_dt_tm != a.updt_dt_tm
>>>
>>>
>>> What it looks like it's doing to me is it seems to be trying to pull
>>> the entire table_b across the database link into temp space so that it
>>> can do comparisons. I'm really hoping this isn't how the database link
>>> is supposed to work or we are going to have some real problems. Anyone
>>> have a clue of what we are doing wrong?
>>>
>> Is table A pretty small? If so, then instead of making the local
>> database the driving site for this distributed query, you might want to
>> consider making "d2" the driving site. On my web site (URL below), you
>> will find a white paper titled "Tuning Distributed Queries and the
>> DRIVING_SITE Hint" which may be useful to you.
>>
> 
> Excellent paper, I almost hate to post
> https://metalink.oracle.com/metalink/plsql/f?p=130:10:7968161258662401359::::alltext,bug,numHits:driving_site,TRUE,100
> 
> But we all need to keep our eyes open when the code is like walking
> around in a Halloween funhouse.
> 
> jg
> --
> @home.com is bogus.
> When precision kills:
> http://blogs.law.harvard.edu/philg/2006/10/06/mid-air-collision-in-brazil-when-precision-kills/
> 

Very interesting as we have never experienced any of these bugs and we do use the DRIVING_SITE hint...on occasion. Mostly, I've used the contents of the white paper as a means of figuring out exactly *what* is   being sent to the remote db and tuning that query on the remote db.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Received on Tue Oct 24 2006 - 08:36:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US