Re: Do DB Links cause high execute and fetch stats?

From: EdStevens <quetico_man_at_yahoo.com>
Date: 28 Aug 2006 12:40:33 -0700
Message-ID: <1156794028.886959.246600@b28g2000cwb.googlegroups.com>

Niall Litchfield wrote:
> joel garry wrote:
> > I think what this is showing is that all the data has to come over and
> > then be summed. I bet if you summed it on rms2d_to_rms1p it would run
> > 100x faster. Maybe you can CTAS over there and read over the link to
> > the new table?
>
> I agree that this is what is being shown. The r=15 in each line also
> shows that you are only array fetching 15 rows at a time. This could be
> tuned but I'd consider using the DRIVING_SITE hint to get the query
> executed at the remote site - pulling back what looks like nearly 3
> million rows, 15 at a time, probably isn't a great strategy across any
> network. Traces are a great strategy for seeing what actually is
> happening - I'm a little surprised at the requests for more of them
> given your tkprof figures though.
>
> --
>
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info/services/

Good point. As a possible aside, after looking over the evidence a bit more closely, I picked up on this in the query:

SELECT ....
WHERE loc IN (
SELECT STORE FROM STORE_at_rms2d_to_rms1p WHERE STORE = 10 ) ...

Seems that SELECT inside the IN clause (SELECT STORE ... WHERE STORE = '10') is begging for (potentially) a lot of unnecessary work .. I wonder how much it would be reduced by

SELECT item,

             loc   to_loc,
             SUM(tsf_expected_qty) tsf_exp_qty

FROM ITEM_LOC_SOH_at_rms2d_to_rms1p
WHERE loc = 10
GROUP BY item, loc Received on Mon Aug 28 2006 - 14:40:33 CDT