hello gurus,
i have two databases: A and B.
database link from A to B is called TO_B.
database link from B to A is called TO_A.
today one of developer came to me with this interesting query claiming that it was working too slow(30 sec) , while query similar to this was working pretty fast (1sec).
i've done some reaserch with both querys, modified them and came to this two querys:
(i shrinked it as max as i could, the differanse between this two querys will be BOLD)
query1 executed from A database: (1 sec)
SELECT f.Firms_Name
FROM Bill.Vi_Adsl_Service@TO_B a,
Bill.Firms@TO_B f,
Dedicated_Line.Info i
WHERE a.Tel_Payer = 43103656
AND a.Tel_Id = i.Tel_Id(+)
AND a.Firms_Id = f.Firms_Id(+)
query2 executed from A database: (30 sec)
SELECT f.Firms_Name
FROM Bill.Vi_Adsl_Service@TO_B a,
Bill.Firms@TO_B f,
Dedicated_Line.Info i
WHERE f.Firms_Identcode = 202177205
AND a.Tel_Id = i.Tel_Id(+)
AND a.Firms_Id = f.Firms_Id(+)
first, i thought that as it were different tables, probably first one was indexed and second was not. that is not case, neither of the mare indexed.
"Vi_Adsl_Service" is actually view which consists of many tables butit has 62550 rows.
"Firms" is a table and it has only 4699 rows.
if the results were visa version it would be understandable but the query that lasts 30 seconds includes table with only 4699 rows, and one which lasts 1 seconds includes 62550 rows...
don't know if it helps but
as i examined it further i have realised that if i change databases, for example if i erase @TO_B link from "Vi_Adsl_Service" or from "Firms" it will work in 1 second for both querys and size is not the case cause size of "vi_adsl_service and "firms on A is greater then size of "vi_adsl_service and "firms on B example and neither of them are indexed
example:
(this works in 1 sec instead of 30)
SELECT f.Firms_Name
FROM Bill.Vi_Adsl_Service a, --erased @TO_B
Bill.Firms@TO_B f,
Dedicated_Line.Info i
WHERE f.Firms_Identcode = 202177205
AND a.Tel_Id = i.Tel_Id(+)
AND a.Firms_Id = f.Firms_Id(+)
but ofcourse this is different data from what i want.
and the fact most interesting. if i swap databases it works in 1 sec instead of 30 again. for example:
query1 executed from B database now: (1 sec)
SELECT f.Firms_Name
FROM Bill.Vi_Adsl_Service a,
Bill.Firms f,
Dedicated_Line.Info@TO_A i
WHERE a.Tel_Payer = 43103656
AND a.Tel_Id = i.Tel_Id(+)
AND a.Firms_Id = f.Firms_Id(+)
query2 executed from B database now: (1 sec)
SELECT f.Firms_Name
FROM Bill.Vi_Adsl_Service a,
Bill.Firms f,
Dedicated_Line.Info@TO_A i
WHERE f.Firms_Identcode = 202177205
AND a.Tel_Id = i.Tel_Id(+)
AND a.Firms_Id = f.Firms_Id(+)
so in this selects which include " WHERE f.Firms_Identcode = 202177205" the data are same but databases from where statement is executed are different and db_links which are used are different and for some reason it gives me 30x differense.
if i execute query from A to B with TO_B link it takes 30 seconds and if i execute query from B to A with TO_A link it takes 1 second. can not understand why.
can anyone explain to me why is this happening and exactly how is this statement executed via db_link?
or can anyone suggest how else should i look into this, what should i do? how should i trace it, if i must...
waiting for your response impatiently.
thank you vary much.