RE: Oracle trace file question

From: Henry Poras <hporas_at_etal.uri.edu>
Date: Wed, 25 Sep 2002 11:38:35 -0800
Message-ID: <F001.004D9273.20020925113835@fatcity.com>

Boy, I go to a new job and I don't have any of my old notebooks. I remember something like this from the past from a third-party app. The first three queries would just be verifying the existance of the object. Can't remember if it was via a SELECT * (I don't think so) or some other similar query. Your app isn't really doing a SELECT * since the fetch.count and fetch.rows are both 0, so I bet it is also just object verification.

Henry

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

WILLIAMS
Sent: Wednesday, September 25, 2002 1: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: Henry Poras
  INET: hporas_at_etal.uri.edu

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:38:35 CDT