RE: Anonymous block from Java--performance

Bulk insert (FORALL loop)  may be useful.

"Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx> wrote:  Argh! Hit return 
by accident.

Follow-up to my earlier post about insert performance. This is 9.2.0.6 64-bit 
on Solaris.

An application executes an anonymous block 259 times. The block consists solely 
of insert statements. Trace shows total execution time for the 259 calls as 
about 90 seconds:



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 259 0.09 0.09 0 0 0 0
Execute 259 90.70 88.54 1 4268 51262 259
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 518 90.79 88.64 1 4268 51262 259

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 185

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 259 0.00 0.00
SQL*Net message from client 259 0.00 0.22

However, cumulative elapsed time of the insert statements adds up to about 19 
seconds. 

The trace file shows a lot of 'SQL*Net more data from client' waits on the 
anonymous block cursors, but tkprof shows these waits only in the 'Summary for 
all non-recursive statements'. I suspect that replacing the block with a stored 
procedure will help, but I don't yet have hard evidence of where the 70+ second 
difference comes from. Could Java buffer size be a factor? Any other gotchas in 
executing anonymous blocks from Java?


Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com


> -----Original Message-----
> From: Baumgartel, Paul 
> Sent: Tuesday, January 30, 2007 4:56 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: Anonymous block from Java--performance
> 
> Follow-up to my earlier post about insert performance. This is 9.2.0.6 64-bit 
> on Solaris.
> 
> An application executes an anonymous block 259 times. The block consists 
> solely of insert statements. Trace shows total execution time for the 259 
> calls as about 90 seconds:
> 
> However, cumulative elapsed time of the insert statements adds up to about 19 
> seconds. 
> 
> The trace file shows a lot of 'SQL*Net more data from client' waits on the 
> anonymous block cursors, but tkprof doesn't. I suspect that replacing the 
> block with a stored procedure will help, but I don't yet have hard evidence 
> of where the 70 
> 
> Paul Baumgartel
> CREDIT SUISSE
> Information Technology
> DBA & Admin - NY, KIGA 1
> 11 Madison Avenue
> New York, NY 10010
> USA
> Phone 212.538.1143
> paul.baumgartel@xxxxxxxxxxxxxxxxx
> www.credit-suisse.com
> 
> 

==============================================================================
Please access the attached hyperlink for an important electronic communications 
disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

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




 
---------------------------------
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives. Check it out.

Other related posts: