slow inserts
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34

Thread: slow inserts

  1. #1
    Join Date
    Jan 2006
    Posts
    23

    slow inserts

    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

    Code:
    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 09:28 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    are you using ASSM?

    are you seeing ITL waits on v$segment_statistics?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Increase FREELISTS on tables if you are not using ASSM.

    Tamil

  4. #4
    Join Date
    Jan 2006
    Posts
    23
    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

  5. #5
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    I added the CODE tags.
    this space intentionally left blank

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by mjschwenger
    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
    Ridiculous settings.
    Tamil

  8. #8
    Join Date
    Jan 2006
    Posts
    23
    Quote Originally Posted by tamilselvan
    Ridiculous settings.
    Tamil
    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.
    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.
    Please, suggest

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

    Tamil

  10. #10
    Join Date
    Jan 2006
    Posts
    23
    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:
    PHP Code:
    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 
    The data blocks waits reported include the freelist waits as well.
    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.
    mj
    Last edited by mjschwenger; 01-11-2006 at 11:39 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width