RE: 10046 trace - weird library misses

John,
Good idea, but outlines disabled.

-----Original Message-----
Sent: Thursday, February 13, 2003 3:19 PM To: Multiple recipients of list ORACLE-L

Vadim,

Just a shot in the dark - are you using (or trying to use) Outlines?

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Disappointment is inevitable, but Discouragement is optional!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Gorbounov,Vadim [mailto:vadim.gorbounov_at_liberate.com]
> Sent: Thursday, February 13, 2003 11:10 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: 10046 trace - weird library misses
>
>
> Tim,
>
> Thanks for you idea, doesn't solve the mistery though.
>
> AFAK, eliminating _HARD_ parses doesn't seem to be client-side
> responsibility, server takes care of this. This is definitely
> true about
> soft parses, i.e., client side cursor caching. BTW JDBC 9.x driver is
> capable of maintaining statement cache, which allows to avoid
> even soft
> parses.
> And last detail, there is no unnecessery HARD parse (and hence library
> misses) when I execute SQL rather than PL/SQL from SQL plus
> or tracing is
> disabled.
>
> Regards
> Vadim
>
>
> -----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).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: Tim_at_SageLogix.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.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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.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).