Re: Tuning database for inserts

Hi,

I do not have experience on NT based systems. I have worked only on large Unix based systems. Nevertheless I am mentioning some points/parameters which might be useful to you. The exact value of the parameters will depend your specific environment. You can try the following:

General:

Ensure that the table you are inserting into has a single(preferable) large initial extent to accomodate all your data. This will avoid dynamic  extension of the table. Set pctincrease to 0. Avoid having any indexes, triggers etc. on the table. I prefer to have large redo log files with about 4 groups. Carryout loads/import directly on the server(not over the network). Have one large online rollback segment if you do not plan to use commit=y parameter while importing. Even if you use commit=y, your each rollback segment should be big enough to accomodate the largest transaction. While using loader, increase the value of the rows parameter instead of the default 64. While importing, increase the value of the parameter buffer; set indexes=no, analyze=no in the import parameter file.

init.ora:

db_block_buffers lower the frequency of the checkpoints by making the initialization parameter LOG_CHECKPOINT_INTERVAL larger than the redo log file size. checkpoint_process=true log_buffers high - to avoid any contention in the redologs multiple db_writers - if writes are taking place to multiple disks and not using raw partitions switch off archiving if possible, otherwise modify the values of log_archive_buffers, log_archive_buffer_size timed_statistics=false. Initially load 1 or 2 files, and observe v$session_wait for wait events related to the loading session. Also observe v$sesstat - DBWR, rollback, redo related(this requires timed_statistics to be on). Tune to remove bottlenecks & later switch off timed_statistics.

I hope this helps.

Ajit

In article <01bd27f7$f3a368e0$67675cc3_at_pccomms_ian>,   "Ian" <Ian.Lewis_at_nospamsynectics-solutions.com> wrote:
>
> We have an Oracle 7.3. database on an NT4Server.
>
> To build the database we are inserting data into tables from ascii files,
> and from imports from other Oracle databases.
>
> What would you recommend the best set up of the Database to be - the
> imports are approx 1.7 million rows max?
>
> - should we have a lot of small sized Rollback segments, (how many?,What
> size?)
>
> what other initialisation parameters would you recommend setting, that
> would improve performance speed when inserting the data.
>
> Thanks in advance
>
> Ian

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet