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

g3000 wrote:
> Thanks, I meant to post this....
>
> SELECT item,
> loc to_loc,
> SUM(tsf_expected_qty) tsf_exp_qty
> FROM ITEM_LOC_SOH_at_rms2d_to_rms1p
> WHERE loc IN (SELECT STORE FROM STORE_at_rms2d_to_rms1p WHERE STORE = 10
> )
> GROUP BY item, loc
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.01 0.11 0 4 1
> 0
> Execute 2 0.00 4554.15 0 0 0
> 0
> Fetch 197492 21.41 79.31 0 0 0
> 2962360
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 197495 21.42 4633.58 0 4 1
> 2962360
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
>
> EdStevens wrote:
> > g3000 wrote:
> > > Im on 9.2.0.4.0 AIX 64bit Enterprise Ed.
> > >
> > > I have a query that I traced. I ran tkprof on the trace file.
> > >
> > > It showed that there where alot of rows retreived and that executing
> > > the query took alot of time. The query ran for a little over an hour.
> > >
> > > Can I assume that the dblink causes the slow execute/fetch or is there
> > > something else I should check?
> > >
> > > The raw trace file has MANY statements like
> > >
> > > FETCH #1:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213288191
> > > FETCH #1:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213289848
> > > FETCH
> > > #1:c=10000,e=346,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213291506
> > > FETCH #1:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213293163
> > > FETCH #1:c=0,e=346,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213294625
> > > FETCH #1:c=0,e=345,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213296282
> > > FETCH #1:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213297941
> > > FETCH #1:c=0,e=603,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213455506
> > > FETCH #1:c=0,e=354,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213457163
> > > FETCH #1:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213458811
> > > FETCH #1:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213460468
> > > FETCH #1:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213462028
> > > FETCH #1:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213463686
> > > FETCH #1:c=0,e=348,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213465344
> > > FETCH
> > > #1:c=10000,e=386,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=963213466844
> >
> > If it were due to the link, that would mean network waits. I'd expect
> > to see wait events like ''SQL*Net more data from dblink'

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'm not disagreeing with Ed, just pointing out sometimes you have to work around link limitations. It's not "waiting" on data over the network, just fetching lots of little bits of it rather than heaping shovelfulls that a local sum could do.

topost don't Please.

jg

-- 
@home.com is bogus.
http://www.snopes.com/science/nobel.htm