Firstly people, I must say I'm extreamly please with the feedback you are providing. I also made a similar posting to Oracle's Metalink site and received only 1 reply (I'm glad that person did reply, but no reposne from Oracle). Now, let me examine your feedback.

Table structure.

Parent table

ID, TYPE, DATE_CREATED

Child Table

ID (Forein Key), PID (Primary), TAG, TEXT, NUMERIC, CID (Child).

Basically, for every parent reco there are at least 50 children records. Becasue Oracle INTERMEDIA can't index (to be queried/ordered) a numeric value's asshole, I've been forced to try this as an alternative method.

Basically splitting the tags for each record into the table. With their corresponding tag values, in either TEXT or NUMERIC fields.

Zaggy.

1. Parallel inserts (Increases the use of multiple CPUs)

If I an inserting from only one process (i.e only one session is inserting these rows, am I able to parralel insert. And if there is ref integrity, would parrallel be affected.

2. Set LOG_CHECKPOINT_INTERVAL to a huge setting, thus turning it off

Check

3. Set LOG_CHECKPOINT_TIMEOUT to 0, thus turning it off

Check

4. PCT_USED to 0 (This gets rid of freelists)

Not quite sure the benefit of this, I ned to reuse the blocks space when it come available, if there are no freelist, how does Oracle know that the bloack is available for NEW INSERTS?

5. Redo Logs on RAID 0 mount points

Check.

I would greatly increase your DB_Block_Buffers and Log_buffers. Watch out for redo latch contention and redo log I/O contention.

My Buffer Cache is already 600 Mb, I have a combined SGA of 860 Mb, there are mulitple of Non-Oracle process runnig on the box which need RAM resources also, and I dn't want the box to start paging.

I like to use OEM, "iostat -xnc 3", "vmstat 3" and top to diagnos my problems.

Cheers

tamilselvan
Moderator

Thanks for your feedback tamilselvan.

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.

No sure if this will help for inserts.

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.

One session performs ths inserts

3. Use RAID 0 ( striping the data file)

Check

4 Pre-create extents for the table. This will help Oracle not to figure out space as and when needed.

In a LOCALLY managed tablespace DATA UNIFORM 1 GB extents, INDEX UNIFORM 200 MB extents.

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.

They are on my RAID 0 disk array. I could move them.

6 Do not multiplex REDO log files both HW and SW levels.

Hardware and Software level... I might check this out again, I'm not sure.

7 Keep maximum memory for data buffer.

Check

8 Keep the TEMP tablespace in a separate disk. It is advisable to maintain the data file onto non-striped and non-mirrored disks.

Not able to do so.

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.

Different tablespaces, all under a RAID 0 config, on the one filesystem.

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.

Is this a UNIX side task. I'm not sure about how to find ou thte actual disk's thoughput.

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.

No direct loading.

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.

I definatly think I'll have to partition, another column will have to be created (date) and partition on this, day by day.

1). The referential integrity of

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.

Check

14 Do not delete two days old rows when other sessions are inserting data into the table. Do it at nighttime.

This insert volume is the same 24x7x356, there isn't any peak or low times.

15 Check with your system admin that the Unix is configured to ASYNC IO.

Check

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.

N/A -- I only perform FULL schema backups

Thanks guys, I was failry concerned maybe I was missing something, but from the feedback I am receiving, it seems the mojority of your suggestions, I have already implemented, which is releiving to say the least.

I might also check out IOT's and see if they can help the situaltion.

If anyone else has any additional suggestions, I'd love to hear from you,

Cheers,