RE: Anonymous block from Java--performance

From: Murtuja Khokhar <murtuja_oracle_at_yahoo.com>
Date: Wed, 31 Jan 2007 02:12:49 -0800 (PST)
Message-ID: <657381.2924.qm@web32705.mail.mud.yahoo.com>

Bulk insert (FORALL loop) may be useful.

"Baumgartel, Paul" <paul.baumgartel_at_credit-suisse.com> 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_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




 
---------------------------------
Never miss an email again!
Yahoo! Toolbar alerts you the instant new Mail arrives. Check it out.
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 31 2007 - 04:12:49 CST