Pro*C performance degradation on size of insert

Using Pro*C on Oracle 8i (8.1.6), under Sun Solaris 8 on a SPARC with 1G of memory we came across an interesting phenomenon.

Writing a program to populate a database with ~2,000,000 rows from data in files the load speed suddenly dropped from ~100 rows a second to ~10 rows a second. The only change had been to add two additional columns to the table being loaded.

Removing the columns and sure enough the load speed returned to ~100 rows per sec.

These relative speed differences are seen even during the first 20,000 rows or so - its not related to the size of the table.

The table has no indexes not even primary key. We tuned the init.ora parameters, we tried disabling logging on the table but this made no appreciable difference even to the amount written to the redo log files? Tried changing how often the load was commited...

Then we analyzed the tables, with the extra two columns the average row length was ~540 bytes, without ~480 bytes. This looks suspiciously like a 512 byte threshold effect. We tried simulating the load using a PL*SQL stored procedure , even with an average row length of >800 bytes the speed was 500-1,000 rows a second (so obviously much faster than 10 rows a second we didn't bother with any timings). So the problem seems to be on the Pro*C route to the server. Our program was being run on the host running the database so there shouldn't be any network effects.

So we split the insert statement - into an insert returning a rowid and an update using the returned rowid. Bingo the load speed is back up to ~100 rows a second!

        Tom

-

TESSELLA Tom Parke (Tom.Parke_at_tessella.co.uk)
_/ _/ _/ Tessella Support Services plc
_/ _/ _/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
_/ _/ _/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
Received on Fri Jul 21 2000 - 00:00:00 CEST