Re: Oracle Gateway suggestions?

I also had great performance improvements when using hints on joins between Oracle and DB2.

John P. Higgins wrote:

> The key to distributed joins is to filter on the remote node (another
> Oracle database or DB2 via the Transparent Gateway).
>
> I had a case where a join between an Oracle table and a DB2 table (via
> Oracle's Transparent Gateway for DRDA) ran for hours. When we looked
> into this, it was almost all network time.
>
> But this was not a network problem! The plan involved nested loops
> with a full table scan of the remote table for each iteration of the
> loop. The only criterea for the remote table was that it had to match
> a column in the Oracle table.
>
> We found that we could apply additional criteria to the remote table
> (in the where clause) that drastically reduced the number of rows
> transmitted from the DB2 table. In short, we got to the desired
> sub-second response time.
>
> Doug Cowles wrote:
>
>> Does anyone know any good pointers, white papers, or even
>> Oracle Reference Books on the Oracle Transparent Gateway
>> for DB/2? I have none, and have noticed the following anomalies
>> when executing queries from Oracle 7.3 (not sure of the gateway
>> version)-
>> to DB/2 3.1(?)
>>
>> Joins of tables, one on one side, one on the other, are extremely
>> time consuming, because Oracle send the entire table from the DB/2
>> side over to the Oracle side. Oracle told me this was a known
>> problem.
>>
>> In general, I have trouble determining what work is being done
>> where.
>> The Platinum tools we have never explain a query that accesses the
>> DB/2
>> tables,
>> instead, it summarizes it as "Remote Access", which doesn't help me
>> to
>> much.
>>
>> I had 6 statements in the form insert into ...(oracle table)...
>> select
>> from (DB/2) table.
>> They ran quickly, but on high volumes, gave me an unable to extend
>> temp
>> segment.
>> We have a 200MB temp space, and aren't accessing that much data. I
>> changes these
>> statements to explicit cursors, but although the sorting problem, or
>>
>> whatever problem it was was solved...it slowed things way down. If
>> I
>> just opened the cursors and closed them - it ran in 2 seconds flat.
>> This contradicts my understanding of how cursors work.
>> I thought when the cursor was open, the entire record set was
>> retrieved. If this is true,
>> than the "fetch" through the cursor is what slows things down. I
>> don't
>> know what gives...
>> but if anyone has any suggestions.. please e-mail me at
>> dcowles_at_i84.net.
>>
>> Thanks in advance...
>>
>> Dc.
>
>
Received on Tue Sep 08 1998 - 11:38:11 CDT