performance issue

[manjunathk]

Hi friends,
Help is needed in improving the performance, for the following:
SUn E6500 EMC Clarion RAID5
64 bit
20 CPUs
20 gb ram
O/S Sun 2.8 Solaris
Oracle 8.1.6
The database is of around 3.5 terabytes and most of the data is in one table. This table is partitioned on the weekday parameter into 7 partitions and on the fourth day the first days data is truncated. There are 2 indexes on this table.
There are NO updations involved. pctfree=5 and pctused=95

There are 10 mount points from u01 to u10 each of 290 gigs.
The datafile distribution is like this:
u01:1st /5th day's data
u02:2nd/6th day's data
u03:3rd/7th day's data
u04:4th day's data
uo5 to u07: for indexes
u08:rbs
uo9:temp
u10:users

10 nos. of DBWRs are specified.

The main problem being faced is of very slow inserts.

My doubt is, since the entire day's data is specified under one mount point, is it useful to have 10 numbers of DBWRs? The dba says, that though a day's data is specified under one mount point, EMC has inbuilt capability to stripe it across all mount points. Is this correct?

In table creation script, PARALLEL and NOLOGGING options have been tried out without any tangible improvements.
I know that PCTUSED of 95% is not desirable normally, but since there are no deletes , I think it should not matter.

Can somebody offer suggestions in improving the performance?
I know you may require more info. but at present I only only the above details. The problematic database is being handled by a collegue and I will try to get any info which is required.
Thanks
manjunath

[gpsingh]

Why don't trace the inserts and see where the waits are, or use v$sesstat,v$system_event,etc to find the area of waits.

thanks
GP

[tamilselvan]

What is the striping size?

Do you use SQL*LOADER or INSERT command to load the data?

[jamesa]

Are you using SQL-Loader or insert statements?, does the table have many indexes on it? Might also look and see how many extents your table is using after the load, could be the time allocating new extents is slowing it down some.

[Kumud]

Check the size of rollback segment. If you have used cursor for loading bulk data you need to change how your commit works

[sudip]

If yr insert process is a batch job then check what is the commit frequency. Also check for redo log contention. Nologging wont be of much because Orcle logs all conventional insert only direct inserts and insert as select type of inserts are NOT logged. If needed increase the number of redo log groups.

During Batch inserts it is better to disable foreign key constraints (it must done vey cautiously). Disable indexes during bulk inserts.