If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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

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.

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.

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

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?