Contention
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Contention

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    Contention

    I have a oltp environement with about 20 user inserting orders.
    When there is more than 5 user concurently inserting orders, they complain about
    time it takes to save the order.


    SQL> SELECT * FROM V$WAITSTAT;

    CLASS COUNT TIME
    ------------------ ---------- ----------
    data block 5146 3703
    sort block 0 0
    save undo block 0 0
    segment header 1 2
    save undo header 0 0
    free list 0 0
    extent map 0 0
    bitmap block 0 0
    bitmap index block 0 0
    unused 0 0
    system undo header 0 0

    CLASS COUNT TIME
    ------------------ ---------- ----------
    system undo block 0 0
    undo header 254 62
    undo block 203 29


    I have lots of wait on data block .
    Presently, all the tables and indexes has a freelist of 5 and initrans of 5.

    Do i need to increase freelist and initrans?
    or just freelist?

    Any more advice to give me?

  2. #2
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    I would look at increasing the freelist. That's where the free
    blocks are "housed". With concurrent inserts, the contention
    is with the freelist. I would try to get the freelist close
    to the # of concurrent inserts.

    Gregg

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    I only have 1 datafile of 3 gig.
    Does that would be a contention issue also?

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    Tell applications to learn how to write their friggin code, or just shoot em!!

    I doubt this is a hardware issue, but hardware may solve your problem, SHOOT 'EM!.


    With Love

    MH

    I remember when this place was cool.

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    Top 5 Wait Events
    ~~~~~~~~~~~~~~~~~ Wait % Total
    Event Waits Time (cs) Wt Time
    -------------------------------------------- ------------ ------------ -------
    db file sequential read 9,676 9,842 51.78
    latch free 163,725 6,475 34.06
    db file scattered read 1,755 1,477 7.77
    log file sync 294 368 1.94
    control file parallel write 218 177 .93



    Here's my top 5 wait event, does anyone has advice?

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