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

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Baumgartel, Paul'" <paul.baumgartel@xxxxxxxxxxxxxxxxx>, "'Alberto Dell'Era'" <alberto.dellera@xxxxxxxxx>
  • Date: Wed, 21 Mar 2007 11:35:25 -0400

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



--
//www.freelists.org/webpage/oracle-l


Other related posts: