How to identify FREELIST contention for a table.

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

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.

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
)

Bob, whay do you worry about freelists and groups? Do you run OPS?

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

Quote:

---

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.

Quote:

---

Originally posted by julian

Quote:

---

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.

Quote:

---

;-) 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.

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.

Quote:

---

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?

sorry for my late answering,

I agree with you julian, if i take the ora-doc 8.1.7.
The hint for non-shared environments has been taken out.

HINT -> ORA-DOC8.1.5/Oracle8i Parallel Server Concepts and Administration
--------------------------------------------------------------------------------
Note:
Even in a non-shared environment, multiple free list groups can improve performance. With multiple free list groups, the free list structure is detached from the segment header, thereby reducing contention for the segment header. This is very useful when there is a high volume of UPDATE and INSERT transactions.

--------------------------------------------------------------------------------
Additionally, we discussed this issue on the german Oracle User Group (DOAG) Meeting in november 2001. It has been checked by more consultants then me.

armin (merry christmas ...)