Performance over a DB link!! [message #127223] |
Sun, 10 July 2005 06:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ankurgodambe
Messages: 45 Registered: March 2005
|
Member |
|
|
Hi Experts,
A query when run on database gives results in less than a second. When I run the same query from a different database over a database link the same query goes on for hours. Now I know that in case of database link all the data is fetched on the site where the query is invoked and then joins are applied. But to avoid this I am using DRIVING_SITE hint so that only the result set is transfered. Yet the query is not working.
Both the databases are on the same server so there is no question of network traffic. Also the tables refered in the query are all from one database .
I have attached the query and its explain plan. Plz let me know if some has and clue on whats going wrong.
Thanks,
Ankur
-
Attachment: report.txt
(Size: 28.06KB, Downloaded 1854 times)
|
|
|
Re: Performance over a DB link!! [message #127396 is a reply to message #127223] |
Mon, 11 July 2005 09:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ankurgodambe
Messages: 45 Registered: March 2005
|
Member |
|
|
To add on to this, I have a question for use of DB links.
If i have a view that is created on remote database which selects some 1000 rows on select * from viewname. Now if i give the statement select * from viewname where condition='some condition'. Now can someone tell me how much data is transferred over the wire, in the sense how many rows. Will it be all 1000 rows or just the result set.
I am asking this question, because I am of the opinion that all the 1000 rows would be brought over to the local DB and then processed here for the where condition, no matter you are accessing a view or a table over a DB link(thats why there is DRIVING_SITE hint). But I am proved wrong when for a view fetcing 6000000 records , only result is transfered over the link. Please let me know if my understanding on DB links is wrong.
Thanks.
|
|
|
Re: Performance over a DB link!! [message #127427 is a reply to message #127396] |
Mon, 11 July 2005 11:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
srinivas4u2
Messages: 66 Registered: June 2005
|
Member |
|
|
Hi Ankur,
It depends on how you're running the query to determine how many rows w'd be transferred over the network. That is, if the query is run making use of driving site and the query is processed at remote site, then, only as many rows filtered by the where conditon w'd be transferred.
Hope that helps!
-Sri
|
|
|
|
Re: Performance over a DB link!! [message #127863 is a reply to message #127762] |
Wed, 13 July 2005 14:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
srinivas4u2
Messages: 66 Registered: June 2005
|
Member |
|
|
Hi,
Am not able to open the attachment (even the previous one!). How fast is query getting executed when executed on the remote database? If that is slow then you have to tune the query.
If you're sure the query is fine..then try using temporary tables to load the result and try to fetch the data from the temporary table.
Hope that helps!
-Sri
|
|
|
|
Re: Performance over a DB link!! [message #128019 is a reply to message #127900] |
Thu, 14 July 2005 11:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
srinivas4u2
Messages: 66 Registered: June 2005
|
Member |
|
|
Hints are just directives!
Did you try giving hint in all the select statements you are using?
There are 5 tables or views you are referring to, I don't know the table details, hint the smallest table. And just try different combinations (without executing). And above all links are crazy!
I have one better option though (in my previous response) which I think must resolve this issue.
|
|
|
Re: Performance over a DB link!! [message #128131 is a reply to message #128019] |
Fri, 15 July 2005 05:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ankurgodambe
Messages: 45 Registered: March 2005
|
Member |
|
|
Hi Sri,
Yes I have tried all possible combinations of putting hints in different select statements, but the complete data is fetched and the joins are being performed on the local end. I am unable to understand why are the hints not working....Is there a seperate XML engine to handle XML queries in Oracle? and if so does it uses hints? I dont know how to take care of this. One more thing that I want to mention is that I just have 1 table on the remote and all other are views on remote.
Temporary table is one option that I can go for, but that would defeat the dataextraction framework here which I have already discussed with the architect..... So making this query work is the only option thats left for me!!
|
|
|
|
|
|