RE: 10046 trace - weird library misses

Tim - Your statement about the JDBC thin client really grabbed my attention since we plan to create some important systems based on this.

    I thought parsing was related to bind variables (using PrepareStatement in Java), and whether that SQL statement was previously executed and is still cached. What am I missing?

    Also we are getting into J2EE if that affects the answer.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, February 13, 2003 10: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: 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).