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:[EMAIL PROTECTED]] > 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" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
