RE: Increasing CPU on every execution of an anonymous block sent from WebLogic
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'Wolfgang Breitling'" <breitliw@xxxxxxxxxxxxx>, "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Wed, 21 Mar 2007 10:01:41 -0400
According to developer, it is thin client shipped with WebLogic. I'll
investigate further...thanks.
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: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
Sent: Tuesday, March 20, 2007 5:41 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Increasing CPU on every execution of an anonymous block sent from
WebLogic
Just a shot in the dark. Are you using JDBC with thin client or OCI. The JDBC
thin client has a problem in that it does not send the bind variables. I don't
see how that would cause your cpu increase but I thought I mention it. It also
may be fixed in 9.2.0.8. It is fixed in 10g but I don't know in which release
exactly. Christian?
At 10:04 AM 3/20/2007, Baumgartel, Paul wrote:
Oracle 9.2.0.8 on Solaris 8.
I've asked development to rewrite Java code that issues SQL with literals so
that it uses binds. 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. 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.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com <http://www.centrexcc.com/>
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Other related posts:
