| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Insert performance problem
Hi,
We are testing a application prototype and inserting 1000 rows from 1
table and populating into 2nd table in a loop and inserting 1M rows.
2nd table have primary key on 2 column, 1 column unique key and 1
non-unique index on 6 columns
We are seeing tps of only about 5000 whereas if I run same test on sql
server tps is between 15000 to 20000
Following are the stats on the machine:
Oracle 10g on win2k
Writing to hard disk having all the datafiles(raid 5) :Avg disk
bytes/write 9000 and 13000
Writing to hard disk having log files :Avg diskbytes/write 48000 and 70000
Total SGA 1024 MB Database buffers 950MB Total Physical memory 2048MBDatabase : non-archive mode
SQL> select * from v$waitstat;
CLASS COUNT TIME
------------------ ---------- ----------
data block 165643 16218 sort block 0 0 save undo block 0 0 segment header 15 0 save undo header 0 0 free list 0 0 extent map 0 0 1st level bmb 2997 40 2nd level bmb 2 0 3rd level bmb 0 0 bitmap block 0 0 CLASS COUNT TIME
------------------ ---------- ----------
bitmap index block 0 0 file header block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 685 69 undo block 6 0
Why is oracle writing in small size to data files and such large sizes
to log files?
How can I improve the performance of inserts?
Why can I see so high waits for data block?
Thanks
--Harvinder
 |
 |