OraFAQ Forum: Performance Tuning » Performance over a DB link!!

Home » RDBMS Server » Performance Tuning » Performance over a DB link!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance over a DB link!! [message #127223]

Sun, 10 July 2005 06:06

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 1737 times)

Report message to a moderator

Re: Performance over a DB link!! [message #127396 is a reply to message #127223]

Mon, 11 July 2005 09:23

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.

Report message to a moderator

Re: Performance over a DB link!! [message #127427 is a reply to message #127396]

Mon, 11 July 2005 11:18

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

Report message to a moderator

Re: Performance over a DB link!! [message #127762 is a reply to message #127427]

Wed, 13 July 2005 03:35

ankurgodambe
Messages: 45
Registered: March 2005

Member

Hi,

Yes agree with you and thats what I have mentioned in my earlier post.
I will now explain to you my original problem. I have one 1 table and a view on the remote database. All the two objects are on the remote database. The query is something like this

select .... from tab1@remote,view1@remote
where
tab1.id=view1.id and
tab1.status=0
and view1.id2 in ( select id2 from tab2@remote where id1=3200);

The actual query and its explain plan is attached.

Now the problem is, the view is completely fetched through the DB link on the local site and the joins are being done. This query ideally results in 1 or 2 rows. Can I make the processing happen on th remote site and just the result set to be fetched over the link? DRIVING_SITE hint is not making any difference as the waits I see in v$session_wait and v$sesstat is for bringing the data over the DB link.

Attachment: explain.txt
(Size: 29.33KB, Downloaded 1578 times)

Report message to a moderator

Re: Performance over a DB link!! [message #127863 is a reply to message #127762]

Wed, 13 July 2005 14:18

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

Report message to a moderator

Re: Performance over a DB link!! [message #127900 is a reply to message #127863]

Thu, 14 July 2005 00:58

ankurgodambe
Messages: 45
Registered: March 2005

Member

The query works absolutely fine on the source DB and returns rows in less than a second. I am attaching the query and the explain plan. After going through all the docs on DB link working, it says that CBO uses the local or remote destination for processing the rows depending upon the cost. And when it has all the rows comming in from a single remote destination only the result set is fetched on the local side. But this is not happening in my case. The data is gettin fetched on the local site and joins are being performed. The result set is not more that a couple of rows. I am unable to force the processing destination even after using he DRIVING_SITE hint. I have attached the query and its explain plan .

Thanks..

Attachment: explain.txt
(Size: 29.33KB, Downloaded 1494 times)

Report message to a moderator

Re: Performance over a DB link!! [message #128019 is a reply to message #127900]

Thu, 14 July 2005 11:41

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.

Report message to a moderator

Re: Performance over a DB link!! [message #128131 is a reply to message #128019]

Fri, 15 July 2005 05:42

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!!

Report message to a moderator

Re: Performance over a DB link!! [message #128201 is a reply to message #128131]

Fri, 15 July 2005 12:01

srinivas4u2
Messages: 66
Registered: June 2005

Member

Ankur,

Even I don't understand the logic behind this execution. Keep us posted if you manage to get some way out!

Good Luck!

-Sri

Report message to a moderator

Re: Performance over a DB link!! [message #476253 is a reply to message #128201]

Wed, 22 September 2010 06:18

Paramjeet_Singh_Sasan
Messages: 1
Registered: September 2010
Location: Pune

Junior Member

Hi Srinivas, ankurgodambe.

Its an age old converstaion with no recent updates.

I am curious to know if yany of you could find a solution ot this then?

I am currently trying to tune a join involving tables form multiple schemas from a remote databse and my calling query stays in local databse.
I had driving_site hint usage in mind, but this conversation tries to show a case where it didn't work for some reason.

I am also trying to research on how we can fine tune dblinks Smile

Please let me know if you have any updates on this.

Thanks !

-Param
(send2param at yahoo)
OCA (9i PL/SQL), OCP (9i DBA)

Report message to a moderator

Re: Performance over a DB link!! [message #476274 is a reply to message #476253]

Wed, 22 September 2010 07:57

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Hi. Start a new thread with your question, and post the SQL and Explain Plan.

Report message to a moderator

Previous Topic:	Performance tunning
Next Topic:	queary taking time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 30 08:28:39 CDT 2024