RE: Database link performance
Title: Message just checking. I've had good luck with the view existing on the db_B machine. Tom Mercadante Oracle Certified Professional -Original Message-From: Michael Rosenblum [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 2:24 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database link performance Tom, I tried both ways: - creating the view on db_B and query it from db_A - creating view of db_A using link to db_B No difference. Michael Rosenblum, Dulcian Inc. -Original Message-From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:07 PMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: Database link performance Mike, you created the view on the db_B machine and called it from db_A, right? "To Tom Mercadante: creating of a view, that does filtering, did not help. Absolutely the same results!" Tom Mercadante Oracle Certified Professional -Original Message-From: Michael Rosenblum [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 12:03 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Database link performance Hi, all! Thanks for your help and suggestions. Here is some feedback on what I have found on the performance problem of the querying data between two databases using DB links. To John Kanagaraj: filter by ROWNUM does not make any difference – I have absolutely the same speed, when querying table with 5000 rows and filtering ‘where ROWNUM <5000’. To Tom Mercadante: creating of a view, that does filtering, did not help. Absolutely the same results! Just remind you the case: declare v_record B%rowtype; cursor cB is select * from [EMAIL PROTECTED] where rownum < 5000; begin for c in cB loop v_record :=c; end loop; end; My next step has been to analyze session-level statistics. When using link we have physically two sessions – from a client to db_A (opened explicitly) and from db_A to db_B (opened by Oracle automatically). After execution of a script: - in the session to db_A we have 1 SQL*Net roundtrips to/from db_link - in the session to db_B we have 1 SQL*Net roundtrips to/from client. But when I am running the same script directly (from client to db_B) – there is only ONE SQL*Net roundtrip to/from the client. I asked Paul Dorsey to check it out with Tom Kyte at yesterday’s NYOUG meeting. Tom told us that Oracle 8.* does single record fetches when using database links. In general, database links have been tuned to support database replication. It means, that ‘create table new_A as select … from table_A@db_B’ runs quickly but querying from new_A is quite slow. The remaining question: has anybody tried to use database links in Oracle9i? Does the same single fetch limitation apply? Thank you, Michael Rosenblum Dulcian Inc.
RE: Database link performance
Title: Message
Tom,
I
tried both ways:
-
creating the view on db_B and query it from db_A
-
creating view of db_A using link to db_B
No
difference.
Michael Rosenblum,
Dulcian Inc.
-Original Message-From: Mercadante,
Thomas F [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06,
2002 1:07 PMTo: '[EMAIL PROTECTED]'Cc:
'[EMAIL PROTECTED]'Subject: RE: Database link performance
Mike,
you created the view on the db_B machine and called it from db_A,
right?
"To Tom
Mercadante: creating of a view, that does filtering, did not help. Absolutely
the same results!"
Tom Mercadante Oracle Certified Professional
-Original Message-From: Michael Rosenblum
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002
12:03 PMTo: Multiple recipients of list
ORACLE-LSubject: Re: Database link performance
Hi, all!
Thanks for your help and suggestions. Here is
some feedback on what I have found on the performance
problem of the querying data between two databases using DB links.
To John Kanagaraj: filter by
ROWNUM does not make any difference – I have absolutely the same speed, when
querying table with 5000 rows and filtering ‘where ROWNUM
<5000’.
To Tom Mercadante: creating
of a view, that does filtering, did not help. Absolutely the same
results!
Just remind you the
case:
declare
v_record
B%rowtype;
cursor cB
is
select *
from
[EMAIL PROTECTED]
where rownum <
5000;
begin
for c in cB
loop
v_record
:=c;
end loop;
end;
My next step has been to
analyze session-level statistics.
When using link we have
physically two sessions – from a client to db_A (opened explicitly) and from
db_A to db_B (opened by Oracle automatically). After execution of a script:
- in the session to db_A we have 1 SQL*Net roundtrips to/from
db_link
- in the session to db_B we
have 1 SQL*Net roundtrips to/from client.
But when I am running the
same script directly (from client to db_B) – there is only ONE SQL*Net
roundtrip to/from the client.
I asked Paul Dorsey to check
it out with Tom Kyte at yesterday’s NYOUG meeting.
Tom told us that Oracle 8.*
does single record fetches when using database links. In general, database
links have been tuned to support database replication. It means, that
‘create table new_A as select … from table_A@db_B’ runs quickly but querying
from new_A is quite slow.
The remaining question: has
anybody tried to use database links in Oracle9i? Does the same single fetch
limitation apply?
Thank you,
Michael
Rosenblum
Dulcian
Inc.
RE: Database link performance
Title: Message Mike, you created the view on the db_B machine and called it from db_A, right? "To Tom Mercadante: creating of a view, that does filtering, did not help. Absolutely the same results!" Tom Mercadante Oracle Certified Professional -Original Message-From: Michael Rosenblum [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 06, 2002 12:03 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Database link performance Hi, all! Thanks for your help and suggestions. Here is some feedback on what I have found on the performance problem of the querying data between two databases using DB links. To John Kanagaraj: filter by ROWNUM does not make any difference – I have absolutely the same speed, when querying table with 5000 rows and filtering ‘where ROWNUM <5000’. To Tom Mercadante: creating of a view, that does filtering, did not help. Absolutely the same results! Just remind you the case: declare v_record B%rowtype; cursor cB is select * from [EMAIL PROTECTED] where rownum < 5000; begin for c in cB loop v_record :=c; end loop; end; My next step has been to analyze session-level statistics. When using link we have physically two sessions – from a client to db_A (opened explicitly) and from db_A to db_B (opened by Oracle automatically). After execution of a script: - in the session to db_A we have 1 SQL*Net roundtrips to/from db_link - in the session to db_B we have 1 SQL*Net roundtrips to/from client. But when I am running the same script directly (from client to db_B) – there is only ONE SQL*Net roundtrip to/from the client. I asked Paul Dorsey to check it out with Tom Kyte at yesterday’s NYOUG meeting. Tom told us that Oracle 8.* does single record fetches when using database links. In general, database links have been tuned to support database replication. It means, that ‘create table new_A as select … from table_A@db_B’ runs quickly but querying from new_A is quite slow. The remaining question: has anybody tried to use database links in Oracle9i? Does the same single fetch limitation apply? Thank you, Michael Rosenblum Dulcian Inc.
Re: Database link performance
Title: Message Hi, all! Thanks for your help and suggestions. Here is some feedback on what I have found on the performance problem of the querying data between two databases using DB links. To John Kanagaraj: filter by ROWNUM does not make any difference – I have absolutely the same speed, when querying table with 5000 rows and filtering ‘where ROWNUM <5000’. To Tom Mercadante: creating of a view, that does filtering, did not help. Absolutely the same results! Just remind you the case: declare v_record B%rowtype; cursor cB is select * from [EMAIL PROTECTED] where rownum < 5000; begin for c in cB loop v_record :=c; end loop; end; My next step has been to analyze session-level statistics. When using link we have physically two sessions – from a client to db_A (opened explicitly) and from db_A to db_B (opened by Oracle automatically). After execution of a script: - in the session to db_A we have 1 SQL*Net roundtrips to/from db_link - in the session to db_B we have 1 SQL*Net roundtrips to/from client. But when I am running the same script directly (from client to db_B) – there is only ONE SQL*Net roundtrip to/from the client. I asked Paul Dorsey to check it out with Tom Kyte at yesterday’s NYOUG meeting. Tom told us that Oracle 8.* does single record fetches when using database links. In general, database links have been tuned to support database replication. It means, that ‘create table new_A as select … from table_A@db_B’ runs quickly but querying from new_A is quite slow. The remaining question: has anybody tried to use database links in Oracle9i? Does the same single fetch limitation apply? Thank you, Michael Rosenblum Dulcian Inc.
RE: Database link performance
Michael, Did you try this with the DRIVING_SITE hint? (May not help, but worth trying). My understanding is that where a STOPKEY such as rownum is involved, the table (or the result of the query) would be brought across the dblink into the local TEMP tablespace and the STOPKEY applied lare on. What you are seeing is the time for the whole table to be brought across via the DBlink and the condition applied after that. A look at 'V$MYSTAT' for that session should normally show an inordinately large value for 'bytes received via SQL*Net from dblink', and you should also see corresponding waits on 'SQL*Net message from dblink. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- Sent: Tuesday, June 04, 2002 9:18 AM To: Multiple recipients of list ORACLE-L 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 < 2; 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database link performance
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 PMTo: Multiple recipients of list ORACLE-LSubject: 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 < 2; 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.
RE: Database link performance
Title: Message Level 8? I was using level 10 for all events. -Original Message-From: Cary Millsap [mailto:[EMAIL PROTECTED]]Sent: Tuesday, June 04, 2002 1:33 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Database link performance Use a 10046 level-8 trace, and the resulting trace file will show what is significantly impacting your time. I would expect it’s either the time tallied to “SQL*Net message from dblink” or a difference in execution plans. Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Michael RosenblumSent: Tuesday, June 04, 2002 11:18 AMTo: Multiple recipients of list ORACLE-LSubject: 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 < 2; 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.
RE: Database link performance
Title: Message Use a 10046 level-8 trace, and the resulting trace file will show what is significantly impacting your time. I would expect it’s either the time tallied to “SQL*Net message from dblink” or a difference in execution plans. Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Michael Rosenblum Sent: Tuesday, June 04, 2002 11:18 AM 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 < 2; 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.
Database link performance
Title: Message 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 < 2; 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.
