Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anonymous block from Java--performance

RE: Anonymous block from Java--performance

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Tue, 30 Jan 2007 16:59:38 -0500
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663BC63@ENYC11P32005.corpny.csfb.com>


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_at_credit-suisse.com
www.credit-suisse.com

> -----Original Message-----
> From: Baumgartel, Paul
> Sent: Tuesday, January 30, 2007 4:56 PM
> To: 'oracle-l_at_freelists.org'
> 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_at_credit-suisse.com
> 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
Received on Tue Jan 30 2007 - 15:59:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US