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).
