OraFAQ Forum: Performance Tuning » optimizer path via DBLINKS

Home » RDBMS Server » Performance Tuning » optimizer path via DBLINKS

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

optimizer path via DBLINKS [message #322475]

Fri, 23 May 2008 12:32

gilou28
Messages: 3
Registered: May 2008
Location: Québec, Canada

Junior Member

I have a view that uses a remote view through a DBLINK. When I do the explain plan, the DBLINK part is shown as "REMOTE". Does this means that the the remote view will be executed as a whole, then, the result set send to the issuer to complete the where clause on it ?

In my case, the remove view has 50M rows, but the where clause that is applied narrows the number of rows to 10k, but it takes for ever.

Here is the explain plan:

[SIZE=1][I]SELECT STATEMENT, GOAL = ALL_ROWS			Cost=7791	Cardinality=239	Bytes=28441
 HASH JOIN			Cost=7791	Cardinality=239	Bytes=28441
  HASH JOIN			Cost=7773	Cardinality=239	Bytes=22705
   HASH JOIN			Cost=7755	Cardinality=239	Bytes=17925
    TABLE ACCESS FULL	Object owner=toto	Object name=totoA	Cost=7	Cardinality=4784	Bytes=105248
    REMOTE			Cost=7739	Cardinality=159994	Bytes=8479682
   TABLE ACCESS FULL	Object owner=toto	Object name=totoB	Cost=17	Cardinality=3480	Bytes=69600
  TABLE ACCESS FULL	Object owner=toto	Object name=totoC	Cost=17	Cardinality=4507	Bytes=108168[/I][/SIZE]

Thanks.

[Updated on: Fri, 23 May 2008 14:07]

Report message to a moderator

Re: optimizer path via DBLINKS [message #322507 is a reply to message #322475]

Fri, 23 May 2008 22:18

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

Senior Member

Welcome to OraFAQ. Nice first post!

Yes, according to this plan, the remote view is resolved remotely and dragged back to the local server in its entirety.

If it is the join that restricts th 50M rows down to 10K, then you have two choices:

- Try to force the join to be a Nested Loops join (using hints for example). This would not be so good as it would perform 10K round-trips across the network.

- Force the query to execute on the remote server using a DRIVING_SITE hint (do a search)

Ross Leishman

Report message to a moderator

Re: optimizer path via DBLINKS [message #322535 is a reply to message #322475]

Sat, 24 May 2008 03:55

coleing
Messages: 213
Registered: February 2008

Senior Member

You could always convert your join on the remote table to an inline view on the remote table. THat is only if you can restrict enough rows on your remote table without joining...

example

select *
from taba a, tabb b, remote_tabc c
where a.id = b.id
and b.id = c.id
and c.cond1 = '1';

would drag remote_tabc back, then apply a filter on c.cond1 I suspect.

select *
from taba a, tabb b, (select * from remote_tabc where c.cond1 = '1') c
where a.id = b.id
and b.id = c.id;

This would apply filter condition on cond1 before dragging the rows back.

All depends onyour query though.

[Updated on: Sat, 24 May 2008 03:56]

Report message to a moderator

Previous Topic:	Conditions On Virtual Columns Degrades Performance
Next Topic:	b-tree index of partitioned tabl

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 22:42:32 CDT 2024