How to identify FREELIST contention for a table.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to identify FREELIST contention for a table.

  1. #1
    Hi there,
    I have to check the storage parameters for a particular table. One of the questions I have been explicitly asked is "Are there enough freelists on that table". After saying "Huh? Wot?" a few times and going and reading some books, I now know what the free list is and how it is used, but I still have no idea how to tell if there is freelist contention on the table. I know it will be caused by lots of concurrent updates and deletions so that the blocks are popping onto and dropping of the list of available blocks as space goes over pctfree/under pctused, but how do you identify this?

    Currently there is the default of 1 for freelists and 1 for freelist groups.

    Any ideas?

    -Bob

  2. #2
    Join Date
    Sep 2001
    Posts
    27
    Here is the procedure for detecting freelist contention:

    Use the following procedure to find the segment names and free lists that have contention:

    Check V$WAITSTAT for contention on DATA BLOCKS.

    Check V$SYSTEM_EVENT for BUFFER BUSY WAITS.

    High numbers indicate that some contention exists.

    In this case, check V$SESSION_WAIT to see, for each buffer busy wait, the values for FILE, BLOCK, and ID.

    Construct a query as follows to obtain the name of the objects and free lists that have the buffer busy waits:

    SELECT SEGMENT_NAME, SEGMENT_TYPE
    FROM DBA_EXTENTS
    WHERE FILE_ID = file
    AND BLOCK BETWEEN block_id AND block_id + blocks;


    This returns the segment name (segment) and type (type).

    To find the free lists, query as follows:

    SELECT SEGMENT_NAME, FREELISTS
    FROM DBA_SEGMENTS
    WHERE SEGMENT_NAME = SEGMENT
    AND SEGMENT_TYPE = TYPE;

    You can also create additional freelists, (see your doc) but this is most frequently done in Parallel Server environments. I have never done this, so I can't offer any advice.

    Good luck.





  3. #3
    Thats reassuring - I did some research and came up with a query that would tell me what waits against a particular segment. When I came back to this thread, your answer confirmed what I had come up with so thanks :-)

    Basically I came up with the same as you but working backwards from the segment...

    select sid, event, state, seconds_in_wait, wait_time, p1,p2,p3
    from v$session_wait vsw where (vsw.p1) in
    (
    select file_id from dba_extents
    where segment_name = 'COMMUNICATIONS'
    )
    and p2 >=
    (
    select block_id from dba_extents
    where segment_name = 'COMMUNICATIONS'
    and file_id = vsw.p1
    )
    and p2 <
    (
    select block_id+blocks from dba_extents
    where segment_name = 'COMMUNICATIONS'
    and file_id = vsw.p1
    )

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Bob, whay do you worry about freelists and groups? Do you run OPS?

  5. #5
    Hi there,
    no OPS (thankfully), the freelist stuff was just a specific question from one of the more techy managers. Im reasonably new to DBA (less than a year) and while I have lots of dev experience there is still masses I dont know - hence when asked about freelists I had no idea about them at all.

    Basically we have one table that is about to change its useage pattern to get something like a predicted 10 million inserts a year - the table is to list all customer communications (emails, phone conversations, marketing mailings, statement mailings, all letters, the lot), which from development experience i know is a nasty idea (been there, done that, seen the 120M row tables elsewhere...) but not my decision im afraid. Hence expecting lots of concurrent inserts, which may cause contension for free blocks. The SQL I had a bove doesnt work either...should read as follows to show all waits in a session for a segment.

    DECLARE
    cursor c1 is
    select sid,
    event,
    state,
    seconds_in_wait,
    wait_time,
    p1,p2,p3
    from v$session_wait vsw ,
    dba_extents ext
    where vsw.p1 = ext.file_id
    and ext.segment_name = 'COMMUNICATIONS'
    and vsw.p2 >= ext.block_id
    and vsw.p2 < ext.block_id+ext.blocks;
    BEGIN
    for i in 1..100 LOOP
    for cur_rec in c1 LOOP
    dbms_output.put_line('SID= '||cur_rec.sid||' EVENT='||cur_rec.event||' '||cur_rec.state||' WAIT(s)='||cur_rec.seconds_in_wait||' WAIT-TIME='||cur_rec.wait_time);
    end loop;
    dbms_lock.sleep(1);
    end loop;
    END;


    Thanks for the help :-)

    -Bob

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by bmycroft
    Hi there, no OPS (thankfully), the freelist stuff was just a specific question from one of the more techy managers.
    :-) Then forget (at least) about FREELIST GROUPS. You use them only in Oracle Parallel Server enviroment.


  7. #7
    Join Date
    Dec 2001
    Location
    Germany
    Posts
    3
    Originally posted by julian
    Originally posted by bmycroft
    Hi there, no OPS (thankfully), the freelist stuff was just a specific question from one of the more techy managers.
    :-) Then forget (at least) about FREELIST GROUPS. You use them only in Oracle Parallel Server enviroment.

    ;-) Of course, you can use FREELIST GROUPS and more than one FREELISTs for NON Parallel Server environment! Try it. It's a not documented method to improve massive parallel DML and to reduce freelist contention for a table.

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    ;-) Of course, you can use FREELIST GROUPS and more than one FREELISTs for NON Parallel Server environment! Try it. It's a not documented method to improve massive parallel DML and to reduce freelist contention for a table.
    There is absolutely no need to have more than one free list group in a non-OPS enviroment. Free list groups give the ability to partition access to an objects free space, by reducing contention on the master free list. The partitionning free list groups provide allow each instance to perform parallel inserts without contending for space across instances. So if you run your database single-instanced, one free list group is enough.

    Each instance in Oracle has a unique instance number assigned at startup (it is the INSTANCE_NUMBER init.ora parameter). If the instance number is greater than the number of free list groups the instance number is hashed to a free list group. The recommendation of Oracle is to have as many free list groups as instances.

    Because locks are owned by instances, blocks are allocated on a per-instance basis-and that is why they are allocated to free list groups.







  9. #9
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Julian, your contention that freelist contention can occur only in OPS is not justified.
    You can have parallel processes running in the non-OPS environment, making use of multiple free-lists. Eliminating free-list contention in Non-OPS environments also leads to improvement in performance.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by Raminder
    Julian, your contention that freelist contention can occur only in OPS is not justified.
    You can have parallel processes running in the non-OPS environment, making use of multiple free-lists. Eliminating free-list contention in Non-OPS environments also leads to improvement in performance.
    Hello! I am talking about free list groups, not free lists? Can you make the diference?


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