pl/sql profiling shows big run time at "END" line?!?

From: James Manning <jmm_at_sublogic.com>
Date: Thu, 07 Feb 2002 21:16:21 -0800
Message-ID: <F001.00409A24.20020207205824@fatcity.com>

AFAICT unrelated to the last post, but:

I have a procedure with 8 IN params, 1 OUT param and all it does in an INSERT ... RETURNING id INTO out_param with out_param being the one OUT param, then a commit. That's the entire contents of the procedure. Yup, it inserts a new row and commits it - that's the entire thing.

This procedure is getting called from a jdbc client a *ton* (a few thousand times per minute) and when I do dbms_profiler runs, the breakdown of run time showj the insert around 30%, the commit around 5%, and the rest of the time is in the "END THE_PROCEDURE;" line?!?! I simply don't get what's going on.

the insert is into a table with an insert trigger to set the PK of new rows based on fetching from a sequence
- side note: the sequence is currently set to NOCACHE, but I'll probably be changing that to "CACHE" to let it cache 20 values ahead like it seems to want to :)
the INSERT has the above-mentioned RETURNING clause.

Any ideas what might be going on here? Why would the END line get so high of run time? How can I track down what's going on?

The only thing I could easily change without making a major impact to the always-running calls to this procedure would be to quit the "RETURNING" clause and dump it and replace it with "select the_seq.currval into out_param from dual;" but that would appear to be a reduction in efficiency and I'm not sure it would really answer my question (the profiler runs afterwards could still show the bulk of the runtime on the "END" line)

Any ideas?

Thanks!

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: jmm_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). Received on Thu Feb 07 2002 - 23:16:21 CST