reduce dblink (database link) chatter

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Mon, 01 Aug 2005 22:15:00 GMT
Message-ID: <42ee9dbf.1149611250@localhost>

I have a query that requires a join over the link. It doesn't return a lot of info from the link (5-20 rows), however, the trace file shows plenty of waits on db link.

Is there are any way to reduce the # of roundtrips? Upping the arraysize (at least through sql*plus) didn't seem to modify the behavior.

Elapsed times include waiting on following events:

  Event waited on                     Times   Max. Wait  Total Waited
  --------------------------------   Waited  ----------  ------------
  SQL*Net message to client             117        0.00          0.00
  SQL*Net message from client           117        0.04          0.55
  SQL*Net message to dblink           14218        0.01          0.02
  SQL*Net message from dblink         14218        0.14         26.81
  file open                               8        0.00          0.00
  direct path write                      17        0.02          0.18
  direct path read                       18        0.02          0.08
  db file sequential read                72        0.03          0.87
************************************************************************

call count cpu elapsed disk query current rows
------- ----- ------- -------- ------ ------- -------- --------

Parse      16    0.09     0.38      0       0        0         0
Execute    16    0.27     1.00      0       0        0         0
Fetch     101    5.00    30.05     90    1491       29       158


------- ----- ------- -------- ------ ------- --------  --------

total     133    5.36    31.43     90    1491       29       158

.......
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes remove NSPAM to email Received on Mon Aug 01 2005 - 17:15:00 CDT