Why insert takes so long?

I have just installed Oracle8 on an NT box and am attempting to insert rows into a completly empty table with no indexes. My problem is that my routine gets to approx 100 rows quite quickly and then slows down to a crawl. The frustrating part of this is that this is a new database and this is the first table to go in it.
The same routine on Personal Oracle 7 (Win 95) flys.

Server setup
350Mhz PC with 64Mb RAM and plenty of disk space
NT 4.0 (sp4)
Oracle8 with Context cartridge installed but not started.
Not connected to any network.

Database setup
Empty, other than system tables and my new table

Table
4 columns. 1 number, 2 Varchar2(100) and one Long. The average size of
the data going into the long column is 3k with a maximum size of 32k.
No indexes, triggers, etc. Initial extent 50Mb

InitOra
db_name = orc1
db_files = 1020
control_files = ("E:\orant\database\ctl1orc1.ora",
"E:\orant\database\ctl2orc1.ora")
db_file_multiblock_read_count = 16
db_block_buffers = 550
shared_pool_size = 11534336
log_checkpoint_interval = 8000
processes = 100
dml_locks = 200
log_buffer = 32768
sequence_cache_entries = 30
sequence_cache_hash_buckets = 23
#audit_trail = true
#timed_statistics = true
background_dump_dest = E:\orant\rdbms80\trace
user_dump_dest = E:\orant\rdbms80\trace
db_block_size =2048
compatible = 8.0.5.0.0
sort_area_size = 65536
log_checkpoint_timeout = 0
remote_login_passwordfile = shared
max_dump_file_size = 10240
text_enable = true

Ans1:

The obvious one first:
db_block_buffers = 550
This is way too small. I usually start at 2000. However, bigger figures like
4000 and 8000 are more realistic.
Oracle avoids physical IO as much as possible, the db_block_buffers
determines the size of the buffer cache where all Oracle data always go to
before it is retrieved by the client. The cache is managed by a Least
Recently Used algortihm. It is quite likely you have filled up the cache
with your 100 rows and it starts replacing blocks in the cache.
The size of the buffer cache determines the hit rate :
physical reads / (db block gets + consistent gets)
(you can determine that for individual sessions by looking at the v$sesstat
view, statistic#'s 38,39 and 40)
Less obvious: how often do you commit and what is the size of your rollback
segments?
Then: you specify the length of that long column as ranging from 3k to 32k.
I would (as this is your 'first' table), increase the db_block_size to 8k
and rebuild the database (necessary with any change of block size) NOW. As
your average long is already bigger than your current block size, you will
have row chaining (row doesn't fit in one block and is extended into another
one) everywhere.

Hth,

Sybrand Bakker, Oracle DBA

Hosted by www.Geocities.ws