| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
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.freelists.org/webpage/oracle-lReceived on Tue Mar 20 2007 - 16:40:45 CDT
 |
 |