RE: Anonymous block from Java--performance

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

Other related posts: