I have a serious performance issue and would like to receive some feedback from and dba who have similar experinces with oracle.
We are running:
Sune450 4 CPU 2GB Ram 250GB Hard Drive SPACE.
Oradata 212 GB DISK Raid 0 Striped.
Have a database 16 K block size.
I have 1 major table, it is 6 columns long, each columns HAS to have an index, for search capabilites. And the table accepts 52 Million records a day. Funny enough, Oracle is having trouble accepting this amount of records. Commits are happening every 100,000 rows. After 5 days (to keep it simple) and 260 million records, I have to then remove the OLDEST 52 million records. Therefore, manplating 104 Million records per day.
I have set the tables and indexes on two different Local Tablespaces, NOLOGGING on tablespaces and Tables (Not that make any difference on normal DML.. when will Oracle realise that NOLOGGING should really be NOLOGGING. My database in in NOARCHIVELOG mode also.
Now both TABLESPACE are on the ONE mount point, but remember this single mount point is RAID 0 STRIPED, and spreads the I/O evenly.
Can somebody offer any suggestions in how to increase insert performance on this ONE table.
INIT parameters of interest.
open_cursors = 300
db_block_buffers = 35000
shared_pool_size = 250000000
large_pool_size = 15000000
java_pool_size = 30000000
log_buffer = 4194304
db_block_size = 16384
db_block_lru_latches = 4
sort_area_size = 131072
sort_area_retained_size = 65536
Suggestions PLEASE anyone.
Click Here to Expand Forum to Full Width