RE: Oracle trace file question

Dennis,

Ask Anjo about his Magic utility. Using that utility one has to set an environment variable on the client which would tune and avoid extra parse sql before they are sent to Oracle. Infact Cary mentions about this in his emails.

btw we use delphi 3rd party ODAC component instead of ADO/BDE which avoids extra parse.

> -----Original Message-----
> From: DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM]
> Sent: Wednesday, September 25, 2002 10:54 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Oracle trace file question
>
>
> 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: Mandar A. Ghosalkar
  INET: mghosalk_at_byer.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).