RE: Increasing CPU on every execution of an anonymous block sent from WebLogic
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'Mark W. Farnham'" <mwf@xxxxxxxx>, "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>, "'Alberto Dell'Era'" <alberto.dellera@xxxxxxxxx>
- Date: Wed, 21 Mar 2007 16:30:07 -0000
I'm no Java expert, but my understanding is that the Java code builds an
anonymous block (PreparedStatement) dynamically, sets the bind values, then
uses the execute() method to submit it to the database. It's executed in the
database (appears in trace file).
So, your point is that the database has to go back to the app server to get
each bind value? If so, each time it might have to traverse a larger and
larger Java structure to get the new value...but would that translate to more
CPU on the Oracle server?
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
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Wednesday, March 21, 2007 11:35 AM
To: 'Baumgartel, Paul'; 'Alberto Dell'Era'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Increasing CPU on every execution of an anonymous block sent from
WebLogic
no, I really can't. If the java code is setting the values, does that mean you
really only have a single line of pl/sql in the block, or are you generating
bind variable assignments from java and creating a long block of code?
If you're generating a long block of code, where is the pl/sql actually
executed? If the pl/sql is executing on the client or a middle tier, then each
one of those bind variable assignements is going to be a line turnaround, and
even if you're somehow getting it to the database server for execution you're
still walking down into the database and back out for each variable assignment.
Sounds like even a ham handed approach like turning each of what you currently
have as bind variable assignments could be batched together as writes to a temp
file (external) on the database server and then a single insert of the form
insert into <table_name>
select * from <external_temp_table>
(and then scratch the external_temp_table).
in place of the block, and then do whatever the current code does between each
block submission.
Quite possibly I totally misunderstand your entire architecture. If so, please
ignore all my comments.
-----Original Message-----
From: Baumgartel, Paul [mailto:paul.baumgartel@xxxxxxxxxxxxxxxxx]
Sent: Wednesday, March 21, 2007 11:11 AM
To: 'Mark W. Farnham'; 'Alberto Dell'Era'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Increasing CPU on every execution of an anonymous block sent from
WebLogic
The block consists solely of INSERT INTO...VALUES statements. The values of
the binds are set in the calling Java code that submits the block.
There are some standalone inserts (into different tables) and queries between
executions of the block.
Can you clarify your comment regarding the approach?
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
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Wednesday, March 21, 2007 11:00 AM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx; 'Alberto Dell'Era'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Increasing CPU on every execution of an anonymous block sent from
WebLogic
okay, I'll bite -- What else is going on in this block?
Are queries, potentially involving blocks and indexes affected by the inserts
being iteratively queried between the inserts?
Since the bind variables are constant in name, this implies they must be set to
new values either by query or new value assignment in the pl/sql code.
The former could be affected by what our friend Tom Kyte refers to as write
consistency, while the latter would still imply a full stack line turn around
to the client (that is where your pl/sql is running, right?).
Sounds to me like either possibility is the wrong way to run a railroad, even
if some bug can be discovered and fixed to make the performance within your
service level requirements.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Baumgartel, Paul
Sent: Wednesday, March 21, 2007 9:44 AM
To: 'Alberto Dell'Era'; Baumgartel, Paul
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Increasing CPU on every execution of an anonymous block sent from
WebLogic
The timings are for serial execution of the block by the same connection.
The text of the block, including bind names, is identical every time.
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
-----Original Message-----
From: Alberto Dell'Era [mailto:alberto.dellera@xxxxxxxxx]
Sent: Tuesday, March 20, 2007 3:52 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Increasing CPU on every execution of an anonymous block sent from
WebLogic
> 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.
Are those timings for a serial execution of the block by the same connection,
or by concurrent execution by different connections ?
Is the block identical for bind variables names also ? ie
begin
insert ... values (:bind_one);
insert ... values (:bind_one);
end;
or
begin
insert ... values (:bind_one);
insert ... values (:bind_TWO);
end;
--
Alberto Dell'Era
"dulce bellum inexpertis"
============================================================================
==
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
============================================================================
==
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
============================================================================
==
==============================================================================
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:
