RE: Increasing CPU on every execution of an anonymous block sent from WebLogic
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Tue, 20 Mar 2007 17:32:08 +0100
Hi Paul
> The new code dynamically constructs an anonymous PL/SQL
> block with binds, binds values to it, and executes it.
> The block consists solely of insert statements.
So, why not issuing the insert statements directly via JDBC? I mean, why
PL/SQL is used?
Regards,
Chris
I am aware that this would be better to implement this as a packaged
procedure, but because this is a heavily-used function of a critical
system, development is not willing to move the code from one tier to
another.
Each business transaction executes this block a few hundred times.
In testing we found that transactions using this code run for over 100
seconds, at which point WebLogic times them out. (In the test, the
dynamically-constructed PL/SQL block is identical every time.) The same
code implemented with literals in its dynamic PL/SQL anonymous blocks
performs just fine.
Analysis of the trace files for the bind-variable version reveals that
the CPU time to EXEC the anonymous block (and, to a lesser extent, its
constituent INSERT statements) steadily increases. The first time
through the CPU time is about 0.02 second. By the last execution before
the timeout, the CPU time is just about 1.0 second. Interestingly, the
logical I/O stats for each execution are quite low (<100 current reads
and consistent reads) and quite consistent.
We've made a rough copy of the anonymous block and run it several
hundred times from SQL*Plus; this setup doesn't show the increasing CPU
time. It's not an exact copy of the Java code, so not conclusive, but I
wonder if it's possible that interaction between Oracle and WebLogic,
maybe in getting the bind variable values, is somehow involved here.
Has anyone seen such behavior? Any suggestions?
Thanks in advance.
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Monday, March 19, 2007 2:40 PM
To: Kerber, Andrew W.
Cc: robertgfreeman@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Why an organization would need an enterprise DB team
On 3/19/07, Kerber, Andrew W. <Andrew.Kerber@xxxxxxx> wrote:
I think it would be more accurate to say that developers don't
really have time to figure out how the database really works, and dba's
don't really have time to figure out how the developers are programming.
Maybe, maybe not. A developer does not need to know in depth how the
database works, but do need to know how certain features work, such as
undo and redo.
A developer should know why it is bad to do simple DML in a loop, and
why issuing COMMIT frequently is a bad idea.
If they are unable/unwilling to learn, then they need to trust the DBA.
That trust works both ways of course.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
========================================================================
======
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
========================================================================
======
--
//www.freelists.org/webpage/oracle-l
Other related posts:
