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