Re: Oracle8. Insert takes soooo...long

Hi Tony,
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

"Stallan, Tony (EXCHANGE:BRW2:S901)" wrote:

> Hi folks,
>
> 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.
>
> Can anyone help me, see the setup below for more info.
>
> Thanks in advance for any help
>
> Tony Stallan
>
> --------------------------------------------------
> 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

Received on Wed Mar 31 1999 - 11:21:33 CST