RE: Oracle trace file question

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Wed, 25 Sep 2002 11:03:27 -0800
Message-ID: <F001.004D9112.20020925110327@fatcity.com>

Okay, I think I answered my own question. If you look at the "Fetch" line on the select * calls, it is zero. My guess is that ADO is just checking for the existence of each table before it makes the real SQL call.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

Sent: Wednesday, September 25, 2002 12:54 PM To: Multiple recipients of list ORACLE-L

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).
-- 
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 - 14:03:27 CDT