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

From: g3000 <carlton_gregory_at_yahoo.com>
Date: 25 Aug 2006 12:58:46 -0700
Message-ID: <1156535920.164390.281390@m73g2000cwd.googlegroups.com>

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'
Received on Fri Aug 25 2006 - 14:58:46 CDT