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'

Where, exactly, is your SQL*net client trace file or your statspack report? You can't really tell WHAT is causing this delay without one, the other, or both. Also, you should have a query plan run on the source database to see if any tuning problems surface there.

David Fitzjarrell Received on Fri Aug 25 2006 - 16:57:45 CDT