Re: performance over dblink
Date: Fri, 9 Dec 2011 10:29:49 +0200
Message-ID: <OF8BC57FCE.300A651A-ONC2257961.002DC3A6-C2257961.002EAD25_at_seb.lt>
>I 've read T.Kyte answers to various db link question and he suggested
always using views, but still I have 2nd, 3rd and 4th option
In practice views worked better for me if used properly. I talk about my development experience as well as tuning experience.
But be prepared to tune some queries specifically, by departing from your general framework.
An important thing to watch out is this: db-links or not, views can become a performance killer. Usually the problem starts when developers start treating views as tables. They start thinking that join between views is the same as join between tables. Then they wonder why optimizer fails to generate plans they expect. predicates are not pushed down. wrong indexes are selected. etc, etc.
What works very well over db links is doing the processing on the right database. For example, filtering data set to get one row on remote db is much better than moving the whole table from remote db and then filtering. I've tuned many distributed queries that way: doing the processing on remote db and only then moving the result set. Optimizer was not allways capable to make the decision.
Please consider the environment before printing this e-mail
From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
To: oracle-l <oracle-l_at_freelists.org>
Date: 2011.12.09 10:15
Subject: performance over dblink
hi,
we have a let's say theoretical dispute what is the best way to go with the following stuff:
we have 2 databases. On one of them there are packages which call some
objects from the other one.
There are 4 possibilities:
- to call directly over dblink, for example: select fld1 into x1 from t1_at_db2 t1, t2_at_db2 t2 where t1.id=t2.id
- to cover db2 complexity with views, for example select fld1 into x1 from v_remote_at_db2
- to cover db1 link with views, for example select fld1 into x1 from v_local (where v_local is select fld1 from t1_at_db2 t1, t2_at_db2 t2 where t1.id=t2.id)
- to cover db2 complexity with views and cover db1 link with views, ie. select fld1 into x1 from v_local (where v_local is select fld1 from v_remote_at_db2 and v_remote is select fld1 from t1 t1, t2 t2 where t1.id=t2.id)
Covering db2 complexity is more user-friendly and from developer's point of view provides us kind of entry interface into db2 (we may then change some views into mviews or change the internal structure of the db2)
Covering db1 db link with views provides kind of "local" interface to remote database (making easier possible changes from remote views to local mviews on remote views, etc, and simplifying such operations as compilation of code on db1 side).
So from developer's point of view it seems the option 4 is a way to go.
What about practical side of this construction? Which option would
perform better? Is the overhead important if we call through one or two
views (ie. opt2 or opt3 vs opt4)?
Of course as this is a theoretical construct I do not want to go into
the special cases, as this is always a matter of details - I would like
to have a rule of thumb from which I would start consideration of real
cases.
What do You think about it?
My choice at the moment is option 4. Calling remote views would produce
not worse execution plans than calling a join on remote tables. The
performance changes (ie turning some views into mviews) are possible on
both sides. The compilation of the db1 code does not touch objects on
db2 (we had a case here with frequent changes of code, views structure
changes were very rare, and compilation lasted very long due to call to
remote objects to quite distant db).
What I miss here?
I 've read T.Kyte answers to various db link question and he suggested
always using views, but still I have 2nd, 3rd and 4th option
TIA
Remigiusz
-- Pole nakazi ---------------------------------------------------------------------- Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> pos : DBA at DIiUSI addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland phone : +48 58 667 17 43 mobile: +48 602 42 42 77 Nordea Bank Polska S.A. z siedzib� w Gdyni, ul. Kielecka 2, 81-303 Gdynia, wpisan� do Rejestru Przedsi�biorc�w Krajowego Rejestru S�dowego pod numerem: 0000021828, dla kt�rej dokumentacj� przechowuje S�d Rejonowy Gda�sk - P��noc w Gda�sku, VIII Wydzia� Gospodarczy Krajowego Rejestru S�dowego, o kapitale zak�adowym i wp�aconym w wysoko�ci: 277.493.500,00 z�otych, NIP: 586-000-78-20, REGON: 190024711-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 09 2011 - 02:29:49 CST