RE: Database link performance

[Mercadante, Thomas F]

Title: Message

Michael,

 

Queries across database link's are notoriusly slow.  I've just been thru this and came to the conclusion that you should create views on the source database (in your case db_B) to limit the records to be queried via the db link.

 

I'm not absolutely sure, but in my opinion, any "where" clauses get processed on the local machine.  Your example provides further evidence.  I would guess that Oracle is bringing all 1.6 million rows across the link first, and then applying the "rownum" clause after the fact. 

 

I've had this exact same scenario, except with a more complicated multi-table query where there are millions of rows in each table.  When I did not use a view, the query literally ran forever (I gave up).  When I created a view, joining all the tables together on the source machine, things worked reasonably well.

 

Hope this helps.

 

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Michael Rosenblum [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 04, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L
Subject: Database link performance

Hi,

 

Could anybody help me with the database link problem?

 

I have two databases db_A and db_B, and link from db_A to db_B.

 

In the database db_B there is table B with 1.6 million records.

 

I created simple test to query data from the table B via database link (I am connected to db_A)

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

declare

   v_record B%rowtype;

 

   cursor cB

   is

   select *

   from [EMAIL PROTECTED]

   where rownum < 20000;

begin

   for c in cB

   loop

     v_record :=c;

   end loop;

end;

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

 

I tried to check performance of data querying through the link, so I had two tests:

- via the link – 19.8 sec

- locally        –   1.5 sec

 

Difference is too large, as far as I can expect! I have been REALLY surprised.

 

At this time there was mostly no other activity on the network (databases are on the same subnet, at the same hierarchical level).

 

Could anybody explain is it normal results?

 

Thanks,

 

Michael Rosenblum,

Dulcian Inc.