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

James,

Admittedly, I am no JDCB expert, but I wouldn't expect to see the behaviour you see, in particular that each insert seems to produce a soft parse.  Do you have a non-cached sequence?  If you do, that's your reason - recursive SYS SQL is (almost) never cached, so your update to seq$ will be soft parsed for each use of a non-cached sequence number.

I would suggest you run your session with event 10046 traceing turned on.

Thanks, Bjørn.

James Manning wrote:

  [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 whetherthat'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 beclear:- 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/commitBEGIN  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  (<a class="moz-txt-link-freetext" href="
http://www.sublogic.com/results.htm#st26">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'sI'm still trying to figure out why the difference in elapsed and CPUtime between the insert and its thin wrapper are so different.  At themoment I'm stucking looking at passing oracle.sql.ARRAY's down to thepl/sql wrapper and then bulk insert those so at least I can do more thanone row per transaction.Is there any way to get the trace data about the commit itself?  Doesthe insert trace numbers include an insert trigger on the table thatsets the PK field to a seq.nextval?Thanks for your help, Bjørn!James