Re: Insertion performance

Gregert Johnson wrote:

> I'm concerned with the speed of INSERTs in Oracle 7, and I wonder if anyone
> can tell me whether the results I've been getting are typical.
>
> Environment -
>
> DB: Oracle 7.3.4
> OS: Solaris 5.6
> Machine: SPARCstation-20, 166 MHz single CPU
>
> Test harness (running on same machine as Oracle server) -
>
> C program, using the OCI API, inserts 10,000 rows into a table of 6 columns
> (4 integer, 2 floating point), average row size about 30 bytes, two indexes
> (2 column primary key index, 1 column non-unique inversion entry index). An
> array of structures is used for the insertions, in which 256 rows are sent
> with each execution of the insertion cursor. The insert statement is parsed
> once, and executed repeatedly until all rows have been inserted. The
> process is performed as a single database transaction. The test table
> resided in a pre-extended tablespace extent.
>
> Results -
>
> Load 10,000 rows: 200 rows/second (with indexes)
> 2500 rows/second (without indexes)
>
> These results were a great surprise, because I had first performed the test
> with the MySQL relational database system, an essentially free product from
> T.c.X. in Sweden, which supports a subset of ANSI SQL. The results with
> MySQL were
>
> Load 10,000 rows: 2000 rows/second (with indexes)
> 5000 rows/second (without indexes)
>
> The MySQL database resided on the same disk which contained the Oracle test
> table.
>
> The full order of magnitude difference with indexed inserts was especially
> surprising, since MySQL does not support prepared statements - each
> insertion command was an ASCII SQL command string, with data for about 110
> rows. Transactions are not supported by MySQL, so some overhead may have
> been saved there.
>
> The bottleneck in Oracle seems to be the indexes, since their presence
> reduced throughput by 90%. Both Oracle and MySQL use B-tree indexes, so
> it's difficult to understand why Oracle is so much slower.
>
> Is this typical of Oracle? Is it the price we pay for all the bells and
> whistles? Or is there a way to speed up inserts significantly? I'd be very
> interested to hear of others' experiences.
>
> -- Greg Johnson
> Cabletron Systems

Have you tried the SQL*Loader utility? On the configuration you describe SQL*Loader should be able to load several thousand rows per minute. (I used to load over 250,000 in less than 10 minutes on a 100Mhz pentium with Oracle 7.3.2 on NT 4.0)

--
Once I figured out how to spell DBA I became one -- Jerry Gitomer Received on Thu Dec 30 1999 - 00:15:23 CST