you create the functions tables in the remote database and you call the
function table remotely.
2016-09-13 19:23 GMT-04:00 Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx>:
Hello you can try function tables too :)
2016-09-12 16:28 GMT-04:00 Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>:
Hey all,
We're testing a vendor's app. Since the app generates dynamic SQL that
mostly don't use binds, I've put their schema in a separate database and
used views of our ERP tables over DB links for them to query. Performance
was terrible, timing out interactive queries after five minutes. The
vendor recommended that instead of those views of our ERP tables that we
reference the tables more directly using the "ERP.ERPTABLE@ERPDB"
syntax. The app isn't timing out any more, but performance is still
suboptimal for our users.
The app queries in question appear to be simple, but it turns out that
the simplicity is masked by the several layers of nested views that contain
dozens of correlated subqueries, each to one of a handful of tables in the
remote ERP DB. The views are something like:
CREATE SYNONYM erpt1 FOR erpschema.erptable575@ERPDB;
CREATE SYNONYM erpt2 FOR erpschema.erptable620@ERPDB;
SELECT
( SELECT col6 FROM erpt1 WHERE erpt1pk = ltab.col25)
description,
( SELECT col14 FROM erpt1 WHERE erpt1pk = ltab.col18)
otherdesc,
( SELECT col7 FROM erpt1 WHERE erpt1pk = ltab.col7)
randomstuff,
( SELECT col40 FROM erpt2 WHERE erpt2pk = ltab.col33)
morestuff,
( SELECT newstuff FROM otherlocalview olv WHERE ltab.col99 =
olv.col21 )
...
FROM local_table ltab;
There's dozens of the inline SELECTs to the remote ERP tables, via the
"erpt1" and other similar synonyms. And the "otherlocalview" contains
similar subqueries and references to yet more views, etc. One particular
explain plan takes almost a minute to come back and is over 320 steps.
So, I'm tasked with making this work. My kneejerk is to replace the
subqueries with an outer joined table, but I really can't rewrite the
vendor's code. The vendor claims that creating their schema in the ERP DB
directly will take care of the performance issue. I have created a test
schema in the test ERP DB and it does perform better for the end user, but
the lack of binds is a deal breaker, having gone through that particular
hell before. So I haven't done a more scientific comparison between the
app schema being local to the ERP DB and being remote. I've also ruled out
Data Guard and MVs for other non-related reasons...
Is there something from the optimizer point-of-view that I may have
missed? Any other tricks of tweaking to get the last 10% improvement
(instead of the 95% improvement by reworking the vendor app)?
Any pointers are appreciated! TIA!
Rich
