Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle trace file question

RE: Oracle trace file question

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 25 Sep 2002 11:13:31 -0800
Message-ID: <F001.004D918F.20020925111331@fatcity.com>


Dennis,

I've seen ADO do some pretty strange stuff "under the covers" on my project also. Ny guess is that ADO is sending these sql statements kinda like a "prepare" statement, just to see if the query will pass muster - like basic security checks - does the table exists, etc. My guess is that it just compiles the sql, but does not fetch any data.

We had something similar with all of our PL/SQL Package call statements. We traced ADO doing a DBMS_DESCRIBE {package_name} first, getting the parameter names, then calling the package directly. We got Microsoft in here for another problem, and he told the developers how to stop ADO from doing that. It was all an un-documented item, by the way.

If you can, post the question to the Microsoft development team and see what they say about it. My guess is that they could help you resolve the problem.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

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

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: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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:13:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US