Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Increasing CPU on every execution of an anonymous block sent from WebLogic

Re: Increasing CPU on every execution of an anonymous block sent from WebLogic

From: Wolfgang Breitling <>
Date: Tue, 20 Mar 2007 15:40:45 -0600
Message-Id: <>

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


Wolfgang Breitling
Centrex Consulting Corporation

This email has been scanned by the MessageLabs Email Security System. For more information please visit
Received on Tue Mar 20 2007 - 16:40:45 CDT

Original text of this message