I have a heavy insert database - 3rd party application which checks for existance of a record, and if they are not there, it inserts them in 10 major tables. I'm running 36 instances of this application and have about 25,000 inserts per table/per minute. Unfortunately, this throuput is not enough for the business and I'm trying to optimize it. What wonders me is that if I add more application instances the insert rate doesn't improve. Bellow is a part of my statpack.
Please help me analyze what is the problem and what can I do to improve the performance.
Thanks a lot,mj
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
------------------------- ---------- -------- ---------
db file sequential read 10,147,781 96,505 88.00
CPU time 7,286 6.64
db file parallel write 102,185 2,391 2.18
log file sync 165,362 1,649 1.50
log file parallel write 297,068 1,251 1.14
Last edited by gandolf989; 01-10-2006 at 10:28 AM.
are you using ASSM?
are you seeing ITL waits on v$segment_statistics?
Increase FREELISTS on tables if you are not using ASSM.
My system is too busy and ASSM doesn't handle it right. I have 45% better performance with manual freelists.
My table/index structure is PCTFREE 60 INITRANS 90 MAXTRANS 255 STORAGE (INITIAL 160M NEXT 160M FREELIST GROUPS 7 FREELISTS 20 )
for 5 heavy inserted tables.
How much more I could bumped it up?
Thanks a lot, mj
Can you trace one of the sessions that is performing this check\insert, so we can see what is going on? Also, please use the "[ code ]" tags when posting output (like the above statspack waits).
Assistance is Futile...
this space intentionally left blank
Originally Posted by mjschwenger
Well, I'm not in love with them either but this brings me better insert rate. Even with so many freelist groups I still have free lists contention and buffer busy waits. I would appreciate if you suggest something that makes more sense. Assusme good hardware, all big tables with 160M extents in a separate tblspace, 4 huge array 10 row devices, and indexes separate as well.
Originally Posted by tamilselvan
Let me put some more details for the app - this is 3rd party products - I cannot touch the code or make any changes rather then database. It all into the logic - it starts with the select from 1 table - then if the data is there - performs some logic - select all info, compare and reinserts the new data. If the data is new - straight insert into the same tables where the select are.When there's 1 instance of this app - it's not too bad. But when there are multiple session - like 50-100 then the performance is terrible. The problem is that I cannot change anything, but I need to obtain a better performance. My stat pack doesn't look so bad but I have no throughput - the insert rate is low. There are about 25,000 inserts a minute in a single table in the beginning of the app run - later on, they get down to 10,000.
What is your block size?
What are the BIG reasons for setting PCTFREE 60 and INITRANS 90 ?
Remember each INITRANS takes 24 bytes in a block.
Assume your block size = 8192.
The PCTFREE 60 will simply take away 4843 bytes ((8192 -120)*0.60). Note approximately 120 bytes is needed for block header info.
Then INITRANS 90 will consume 2160 bytes (90*24).
Available space for new insert = 8192 - 4843 - 2160 = 1189.
That's why your INSERT is running slow. Most of the space within a block will never be used.
What you must consider:
1 Set FREELIST GROUPS to 1. Higher value is needed only if you use RAC.
2. Reduce FREELISTS to 17 (or 13) if you are going to run more than 34 concurrent sessions that insert rows.
3. Set PCTFREE to 10. If appln updates the rows that leads to row expansion in future, then you may try with 15. If you donot know right now, then set it to 10.
4. Set INITRANS to 3 (I would consider 1, if only one sesion is going to update/delete in future). The ITL will be used only when delete/update takes place on the table/index. Right now, your main concern is "INSERT". More over, when there is a shortage in ITL, Oracle will use the space reserved by PCTFREE. Hence, very very high value for INITRANS is not needed.
5. Experiment TABLE PARTITION with GLOBAL INDEXES as a last option.
Thanks a lot, Tamil. Here's where I'm at.
The insert rate is what the business measures and they want to see at about 60,000 inserts per min for Oracle (the second system is running on DB2 and already does that with almost no tuning). So they consider Oracle a bad system because of it's bad performance for this app. I'm trying to tune it up so I could get better insert rate - whatever is required.
The reason for PCT free=60 is that I need to keep less data in the block. I can see how 5-6 (sometimes 12-13 sessions) are going after the same data on selects/updates - that causes my big contention on db sequential reads. I use 4K page size for the same reason - less data in the block. The data is selected very randomly - the possibility of not finding it in the cache is pretty big... especially where there are 100,000 mln of records in the 10 base tables.
I started with ASSM - the insert rate I'm getting is the same like now. Then I tried using manual + freelists like 48 per table - and my troughput went a little bit up but had waits. Adding of 1 freelist group did not help a lot but ncreasing them to 5-7 did. I know this will cause me space though.
My wait events are buffer busy waits and enq: TX - index contention. Oracle advices to increase initran on this indexes/tables to eliminate these waits.
After I went to 7freelist groups/20 freelist my undo table space start getting slammed - I guess there's more activity now. Here are my waits by class:
The data blocks waits reported include the freelist waits as well.
data block 921559 47037
segment header 311 18
free list 4440 1734
1st level bmb 14 2
2nd level bmb 2 0
undo header 107972 9673
undo block 237835 3804
I still use auto undo handling - may be I have to go to manual one if the auto doesn't handle it.
I must consider between 72 to 96 concurent sessions - app instances running together. The problems with Oracle is that I get the same insert rate no matter how many processes I run. Let's say I run 25 processes - I get 10,000 inserts a min. You would expect if I run 50 processes to get may be no 20,000 (because of the contention) but at least 15-18,000. No, this is not the case... I get the same 10,000 inserts no matter what tuning I do (ASSM, ASM, freelist or no, freelist groups, etc.)
I'm starting new testing today - I'll try single freelist group and different % of Pctfree = and I'll post the results.
I cannot use partitioning for the moment - still waiting for approval per license agreement.
Thanks a lot for the help - really appreciated.
Last edited by mjschwenger; 01-11-2006 at 12:39 PM.
Click Here to Expand Forum to Full Width