Return-path: Envelope-to: oracle-l@orafaq.net Delivery-date: Thu, 26 Sep 2002 03:47:22 +0800 Received: from newsfeed.cts.com ([209.68.248.164]) by jean.onefuse.com with esmtp (Exim 3.36 #1) id 17uI81-0006fp-00 for oracle-l@orafaq.net; Thu, 26 Sep 2002 03:47:21 +0800 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA14515; Wed, 25 Sep 2002 12:47:22 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004D9273; Wed, 25 Sep 2002 11:38:35 -0800 Message-ID: Date: Wed, 25 Sep 2002 11:38:35 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Henry Poras" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Henry Poras" Subject: RE: Oracle trace file question Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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@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@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@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@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).