Re: 10046 trace - weird library misses

So there is one parse for every two executes. I think that there is a problem in the application. It could be a describe that precedes every other execute or so or a soft parse happens for each. Or ......

Could it that 40 different sessions run in the same trace file ? Each session will do a soft parse no matter what.

Anjo.

On Thursday 13 February 2003 10:59, you wrote:
> Tim,
>
> By "JDBC Thin program", are you referring to the JDBC Thin driver
> or the program that uses the JDBC Thin driver, i.e. the application.
> I assume you are talking about the later.
>
> Our application is written in Java and uses JDBC Thin and I don't
> see any of the hard parse problem.
>
> Thanks
>
> Richard
>
> -----Original Message-----
> Sent: Thursday, February 13, 2003 11:14 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Not an RDBMS bug -- it is the behavior of both your JDBC thin program as
> well as SQL*Plus. Every execute is preceded by a parse in SQL*Plus -- they
> didn't write that utility with parsing efficiency as a primary goal.
>
> Apparently, neither was the JDBC thin code...
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, February 13, 2003 7:49 AM
>
> > Dear friends,
> >
> > I traced one of our test cases and found something weird.
> >
> > Did anybody else observe this?
> >
> > Env:
> > server - 9.0.1.4, Solaris.
> > client - weblogic 7, uses original oracle thin 9.0.1 jdbc driver to
>
> connect.
>
> > In fact, I can reproduce all this from SQLPlus
> >
> > Here is an excerpt from tkprof below - why every parse is a hard parse?
> > Looks like the problem doesn't appear when 10046 is not set, and it
> > appers ONLY on pl/sql blocks returning data to client, normal selects OK.
> > Looks like bug again. Any workaround?
> >
> > And what are these "Misses in library cache during execute"?
> >
> > 9.2.0.2 on Linux works fine, i.e. no misses once it has been parsed.
> >
> > BEGIN :1 := FN_GET_STATUS_ID(:2,:3); END;
> >
> >
> > call count cpu elapsed disk query current
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 40 0.07 0.08 0 0 0
> > 0
> > Execute 80 0.62 1.55 64 1492 0
> > 80
> > Fetch 0 0.00 0.00 0 0 0
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 120 0.69 1.63 64 1492 0
> > 80
> >
> > Misses in library cache during parse: 40
> > Misses in library cache during execute: 40
> > Optimizer goal: CHOOSE
> > Parsing user id: 40
> >
> > This select
> >
> > select LOADED_VERSIONS, EXECUTIONS, LOADS,PARSE_CALLS, parsing_user_id
> > from v$sql
> > where sql_text like 'BEGIN :1 := FN_GET_STATUS_ID(:2,:3); END;';
> >
> > gives out whole bunch of these record groups
> >
> > LOADED_VERSIONS EXECUTIONS LOADS PARSE_CALLS PARSING_USER_ID
> > --------------- ---------- ---------- ----------- ---------------
> > 1 1 1 1 40
> > 1 1 1 0 40
> > .... repeated N times
> >
> > Thank you for you time
> >
> > Vadim G
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Gorbounov,Vadim
> > INET: vadim.gorbounov_at_liberate.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).

-- 
----------------------------------------------------------------
Anjo Kolk
http://www.oraperf.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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).