Re: Increasing CPU on every execution of an anonymous block sent from WebLogic
- From: Zoran Martic <zoran_martic@xxxxxxxxx>
- To: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Thu, 22 Mar 2007 23:43:36 -0700 (PDT)
From point 4. you are saying that your worry is CPU time and not the elapsed
time that is the same.
As already pointed out CPU is usually spent on parsing, block reads/updates,
latching, networking, ....
To watch the possible memory increases (leaks) you can use ps command or
something else dependant on the OS.
Memory allocation may be just 1 cause of increased CPU activity if for example
prepared statements for the new dynamic block is prepared in the loop and
possible not closed at the end.
The granularity of what CPU is spent on apart from parsing/execution split may
be observed in two ways:
1. Oracle way by looking statistics that may give you indirectly the possible
cause
2. OS way by tracing it on the OS side and looking the difference in calls
On Oracle side you can get a few snapshots of session statistics between a few
calls.
Statistics difference may show you why the subsequent call taking much more CPU
time.
If it does not show up then the cause needs to be look on the OS side.
You can trace the process on the OS level with the timing so you can see more
clearly on what CPU time is spent (of course looking what is what there is not
an easy task).
-Zoran
----- Original Message ----
From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
To: Zoran Martic <zoran_martic@xxxxxxxxx>; "Baumgartel, Paul"
<paul.baumgartel@xxxxxxxxxxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Thursday, March 22, 2007 2:15:20 PM
Subject: RE: Increasing CPU on every execution of an anonymous block sent from
WebLogic
1. The connection is dedicated and all executions take place in the same
session.
2. The PL/SQL blocks are built dynamically, but in this test, all blocks are
identical.
3. I haven't checked for memory leaks; can you advise how I might do so?
4. The block contains only INSERT INTO...VALUES. The symptom is not increasing
elapsed time (which might include increased waits), but increasing CPU time on
EXEC.
5. No LOBs; bind values are numbers, short varchar2 strings, and dates.
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: Zoran Martic [mailto:zoran_martic@xxxxxxxxx]
Sent: Thursday, March 22, 2007 4:35 AM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Increasing CPU on every execution of an anonymous block sent from
WebLogic
Not sure I will help anything but to throw a few ideas.
I will not expect anonymous block to be different then PL/SQL call as you need
to pass the binds whatever way.
Also from any client you need to call PL/SQL anonymously anyway with begin/end
making it similar if not the same.
The binding should be by position meaning it does not matter the name of it (I
may be wrong as did not look java code for some time).
I will focus on your statements about the server side timing that increasing.
Just a few more ideas:
1. Is the connection dedicated or shared? Shared may cause some interesting
issues :)
2. If you are dynamically building PL/SQL blocks and they are different so
executing them may eat up the memory inside the server side process increasing
the CPU time.
3. Did you check the memory leaks as usual problem with such constructs usually
related to Oracle bugs and sometimes bad coding of course (I have them a lot in
the past)
4. Of course it may be just that these blocks are piling up one after another
and increasing the timing due to the actual work done (block reads, latching,
...) but you mention it is not the case.
5. I assume binds are reasonable size and not lobs?
-Zoran
----- Original Message ----
From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
To: Mark W. Farnham <mwf@xxxxxxxx>; "Baumgartel, Paul"
<paul.baumgartel@xxxxxxxxxxxxxxxxx>; Alberto Dell'Era
<alberto.dellera@xxxxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, March 21, 2007 4:30:07 PM
Subject: RE: Increasing CPU on every execution of an anonymous block sent from
WebLogic
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
==============================================================================
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:
