Re: Improving the speed of forall ... insert with many columns

I believe there are a couple of grey areas in your tests. Your assumed timing for 48MB over 100Mb/s may be valid for a file transfer but Oracle uses a synchronous dialogue mechanism with a packet size typically of 1.5K to 4K, so the latency of the network is much more significant than the bandwidth.

Comparing the PL/SQL insert with the insert /*+ append */ is also a little misleading (especially if the copy table is a nologging table). /*+ append */ switches off UNDO, which reduces the logical I/O overhead by a few percent, and if the target is NOLOGGING (or the database is in noarchivelog mode) then the log cost is non-existent, and for a 48MB load that would probably be the most significant I/O cost. There is also the fact that "insert / select" can use an 'internal format to internal format' strategy which is more CPU efficient than a "PL/SQL format to internal format".

First check where your time is going using v$session_event and v$sesstat -

    v$session_event will show you time lost on     file writes and waiting for data from client.     v$sesstat will show you CPU used by the session.

These may give you a better idea of where you can improve performance - my guess would be on the network.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Sven C. Koehler wrote in message ...


>Hello all!

>

>I am using the Oracle OCI8 interface (namely OCIBindByPos(), and

>OCIBindArrayOfStruct()) to insert (append) data sets of 400,000 rows each

>into a table with 19 columns (the table has no indexes and no

>constrains).  On the server side I use a PL/SQL procedure (PL/SQL source

>is appended to the end of this message) that takes 19 array parameters

>corresponding to each column of the table, and inserts them all using

>forall insert.  When I run my example application it takes about 42s to

>insert the 400,000 rows (IMHO this is too slow).

>

>I measured/calculated the total time to consist of the following

>time portions:

>  - Preparing the c-arrays bound with OCIBindByPos(): <4s;

>  - Transferring the data over the network:

>      127 B/row * 400000 rows = ~48MB

>      over an otherwise idle 100MBit network

>      transferring about 8MB/s: 6s;

>

>So the total time spent on the database is about 32s (42s-10s).

>I also experimented with the size of the chunks that I transfer

>each time with OCIStmtExecute(), ranging from 200 to 5000 rows

>for each chunk and settled at 1000 rows (which gave me the 42s).

>