For remote queries I use two options 1) the hint /*+DRIVING_SITE(table)*/ 2) create a function table in the remote database, to get only the data I want 3) I suppose I never tried, because I have standard one database, but a materialized view could help too 2014/1/21 Uzzell, Stephan <SUzzell@xxxxxxxxxx> > Hi all, > > > > Hoping someone can point me in the right direction, or give me some basic > reading material here… > > > > We have a query that (apparently) used to perform well, but recently runs > 10+ minutes (long enough that the web-based front end times out). The > problem, the reason I don’t know how to approach it, is that it is a query > against a simple table joined to a view. And the view is a join of several > tables from the other side of a db link. > > > > I don’t want to dump the whole plan here, but hopefully I’m not stripping > out too much too relevant: > > > > | 12 | NESTED LOOPS | > | 1 | 250 | 11948 (1)| 00:02:24 | | | > > | 13 | REMOTE | GUEST_CHECK_LINE_ITEM_HIST > | 4 | 748 | 11944 (1)| 00:02:24 | MMHMS | R->S | > > | 14 | TABLE ACCESS BY INDEX ROWID| MST_STORE_TAB > | 1 | 63 | 1 (0)| 00:00:01 | | | > > | 15 | INDEX UNIQUE SCAN | MST_STORE_TAB_PK > | 1 | | 0 (0)| | | | > > > > Unfortunately, I can’t tell from this what the access is for > GUEST_CHECK_LINE_ITEM_HIST, and that is a significantly large partitioned > table. Large enough that I’m sure it isn’t doing a FTS, or it would take a > heck of a lot longer than this… > > > > How do I tune / investigate across a db link? Is there somewhere to see > what is getting hidden under the operation “REMOTE”? > > > > Thanks! > > > > *Stephan Uzzell* > > >