Heavy Inserts in to a table:

Following are my suggestions; you may not follow all of them. Study them and carefully implement them with your system admin.

1 Use parallel query where ever possible. Remember that parallel query is effective only when the box has more than one CPU and the data files on which the table resides are more than one disk.
2 If more than one CPU in the box and the inserts are coming from multiple sources, set FREELIST for the table = Number of CPUs.
3 Use RAID 0 ( striping the data file)
4 Pre-create extents for the table. This will help Oracle not to figure out space as and when needed.
5 Never put the REDO log files on RAID DISK Array. This will slow down very much, because Oracle writes Redo Log serially, not randomly. Put these files onto non-striped and non-mirrored disks.
6 Do not multiplex REDO log files both HW and SW levels.
7 Keep maximum memory for data buffer.
8 Keep the TEMP tablespace in a separate disk. It is advisable to maintain the data file onto non-striped and non-mirrored disks.
9 Separate INDEX and DATA table spaces’ data files under different controllers. Even if you use RAID 0, identify the controllers and place these data files carefully.
10 Identify each disk’s through put. The sum of disks’ throughput under one controller should not exceed the disk controller’s through. If more disks are configured under one controller, then the system will experience IO bottleneck.
11 IF you are using import to load data into the big table, use DIRECT=TRUE and PARALLEL options, also set the table in NOLOGGING mode.
12 Partition the table and Index. Of course in your case, the partition key is date. In 8I, you can add new partition and drop existing partition very easily.
13 Set LOG_CHECKPOINT_INTERVAL = 1000000000 and LOG_CHECKPOINT_TIMEOUT = 0. Remember the unit of LOG_CHECKPOINT_INTERVAL is number of OS blocks not the Oracle block. In Unix, it is normally 512 bytes.
14 Do not delete two days old rows when other sessions are inserting data into the table. Do it at nighttime.
15 Check with your system admin that the Unix is configured to ASYNC IO.
16 Always maintain 4 or 5 disks (non-striped and non-mirrored) for storing the export files / flat files. From here you should load the data into table.

Closed mouths gather no feet.

Tamilselvan