Oracle-L: RE: database link tuning

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Fri, 08 Nov 2002 12:39:30 -0800
Message-ID: <F001.004FF459.20021108123930@fatcity.com>

Yes, be particularly careful when accessing tables across database links. The threshold for when a FTS is more efficient than an index access changes.

Also, i found out this week, that if you use in-line views in your query, Oracle can send the whole in-line view across the database link!. In the following example, oracle will execute the inline view on the cust database! Without the inline view, oracle chose a FTS of tablea hash joined to FTS of tableb.

SELECT *
FROM (SELECT *

        FROM   tablea_at_cust, tableb_at_cust
        WHERE  tablea.key = tableb.key) t
      ,tablec

WHERE tablec.key = t.key

-----Original Message-----
Sent: Friday, November 08, 2002 2:43 PM
To: Multiple recipients of list ORACLE-L

Paula

I was hoping someone with more specific experience in this area. Here is a quote from Oracle Performance Tuning 101:

Be particularly when joining a local table to a remote table.

My vote if you only have 60 rows would be to populate a local table with those rows before running your query.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, November 08, 2002 11:40 AM To: Multiple recipients of list ORACLE-L

BTW, Was doing join of views pointing to tables through 2 database links and gateway. Just wondering - faster to move the tables then perform join locally esp. if I can do CTAS each time - hmmmm.

-----Original Message-----
Sent: Friday, November 08, 2002 12:34 PM To: 'ORACLE-L_at_fatcity.com'

Trying to bring over 60 rows with database link and write to table using CTAS. It is taking forever - going through Oracle - going through another Oracle that is hooked up with a gateway to Informix - yuck!!

The waits are:

Event   Waits   Waits/sec       % total Wait Time (ms)  Wait Time/sec   %
total        Average Wait     SQL*Net more data from dblink  1352
80.8709175738725        99.1202346041056        14250   852.374685967221
0       0

Any ideas how to speed up other than getting another gateway, creating ascii file and using direct load. Is there a way to tune related to Net8, database links?

Thanks,
Paula

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Toepke, Kevin M
INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------