Re: high tkprof parse counts == real # of hard parses?

[Bjørn Engsig]
> There are two things worth mentioning: cursor_sharing does NOT remove
> soft parses (see my white paper for details) and PL/SQL does not
> generate unnecessary soft parses except when using native dynamic SQL.

I'm using the implicit statement caching in oracle's cache (OracleConnectionCacheImpl) at the moment. Not sure whether that's helping on the parse side or not.

> So I am speculating, that your jdbc app is generating SQL statements
> with literals, which you have cursor_sharing taking care of, but you
> are nevertheless still doing a soft parse, which also implies a soft
> parse of any static SQL statements in your pl/sql package. You need
> to make sure you keep one cursor open and parsed all the way from
> JDBC, and then simply execute this cursor contienously. If you cannot
> do this, use session_cached_cursors to make the soft parses somewhat
> cheaper (that's also in my white paper).

Using session_cached_cursors sounds like the right idea, but just to be clear:
- jdbc fetches conn out of cache and does prepareCall with bind's

      obj_stmt = conn.prepareCall("{call INSERT_OBJECT_RESULTS(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");   The implicit statement caching support with oracle's cache appears to   help prevent most of the re-soft-parsing/validation/whatever. - this procedure is a thin wrapper for insert-returning/commit

BEGIN
  insert into OBJECTLEVEL
   (OBL_URL, OBL_DNS, [other fields snipped])   values
   (o_url, o_dns, [other fields snipped])   returning obl_id into o_link_id;
  commit;
END INSERT_OBJECT_RESULTS;

• in the itrprof analysis at http://www.sublogic.com/results.htm, the outer pl/sql wrapper is statement 26 (http://www.sublogic.com/results.htm#st26) and the insert is statement 27 (http://www.sublogic.com/results.htm#st27)
• the pl/sql wrapper has 51 soft parses, 1489 exec's
• the insert itself has 1489 soft parses, 1489 exec's

I'm still trying to figure out why the difference in elapsed and CPU time between the insert and its thin wrapper are so different. At the moment I'm stucking looking at passing oracle.sql.ARRAY's down to the pl/sql wrapper and then bulk insert those so at least I can do more than one row per transaction.

Is there any way to get the trace data about the commit itself? Does the insert trace numbers include an insert trigger on the table that sets the PK field to a seq.nextval?

Thanks for your help, Bjørn!

James

-- 
James Manning <jmm_at_sublogic.com>
GPG Key fingerprint = B913 2FBD 14A9 CE18 B2B7  9C8E A0BF B026 EEBB F6E4
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: James Manning
  INET: oracle_at_sublogic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).