Oracle trace file question

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Wed, 25 Sep 2002 09:53:33 -0800
Message-ID: <F001.004D8FC2.20020925095333@fatcity.com>

Hello all

   I am trying to debug a mysterious sporadic error that a Visual Basic program using ADO is hitting. In reviewing the trace file, we see an odd series of SQL statements. Before performing a 3 table join, a select * from table is issued for each of the tables to be joined. The developer swears ADO isn't doing this. I can't think Oracle would decide to spontaneously do this. These are large tables so if it were really occurring, the communications line would be tied up for a long time, but the developer is able to get subsecond response. Has anyone seen anything like this before?

---

---

select *
from
 source_reference

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 95 0.04 0.12 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 95 0.04 0.12 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18

---

---

select *
from
 account_master

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 95 0.05 0.10 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 95 0.05 0.10 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18

---

---

select *
from
 school_demographics

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 95 0.13 0.07 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------

---

total 95 0.13 0.07 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18

---

---

select *
from
 source_reference sr, account_master am, school_demographics sd where am.lid   >= 1 and am.lid <= 100and am.lid=sr.lid and am.lid=sd.lid order by am.lid   asc, sr.source_num asc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.03 0.07 7 27 0 100
------- ------ -------- ---------- ---------- ---------- ----------

---

total 10 0.06 0.10 7 27 0 100

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18

Rows Row Source Operation

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

    100 SORT ORDER BY
    100 HASH JOIN
    100 TABLE ACCESS BY INDEX ROWID SCHOOL_DEMOGRAPHICS     101 INDEX RANGE SCAN (object id 3290)     100 HASH JOIN

    100     TABLE ACCESS BY INDEX ROWID SOURCE_REFERENCE
    101      INDEX RANGE SCAN (object id 3294)
    100     TABLE ACCESS BY INDEX ROWID ACCOUNT_MASTER
    101      INDEX RANGE SCAN (object id 3214)

****************************************************************************

***

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Sep 25 2002 - 12:53:33 CDT