output of sql

From: Daud <daud11_at_hotmail.com>
Date: 9 Dec 2002 18:46:24 -0800
Message-ID: <f0bf3cc3.0212091846.49c45930@posting.google.com>

I have a PL/SQL packaged procedure that is now taking longer to run. I set auto_trace and looked at the output but it did not make sense to me.
Here it is:

SELECT LOT.SYSID,LOT.PLANNAME,LOT.PLANREVISION,LOT.PROCESSINGSTATUS FROM
FWLOT LOT WHERE LOT.APPID NOT LIKE 'TMP-%'

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.01          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch    15653      2.44       9.88          0          0          0

122500379
------- ------ -------- ---------- ---------- ---------- ----------

total 15655 2.44 9.89 0 0 0 122500379

FWLOT is a table on a remote database (We set db link and private synonym). Both local and remote databases are 8.1.7.3.0. Now, I have only 70692 rows in FWLOT. How can it processed over 122 million rows?

I then logged in as the user that was executing the above procedure on the local database and set sql_trace on and execute SQL above and I got a different result - this one makes sense.

SELECT LOT.SYSID,LOT.PLANNAME,LOT.PLANREVISION,LOT.PROCESSINGSTATUS FROM
FWLOT LOT WHERE LOT.APPID NOT LIKE 'TMP-%'

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.16          0          0          2  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch     4714      0.88       5.59          0          0          0

70692
------- ------ -------- ---------- ---------- ---------- ----------

total 4716 0.89 5.75 0 0 2

70692

What's going on? I have seen something like this a few times when doing stuff over db link. Is this is a bug that is not fixed in 8.1.7.3? I checked the bug list that 8.1.7.4 fixes but I am not sure if this is one of them. Any idea?

rgds
Daud Received on Mon Dec 09 2002 - 20:46:24 CST

output of sql_trace