 Multiple FREELIST [message #118910] |
Mon, 09 May 2005 11:44  |
Anand Ramaswamy
Messages: 111
Registered: January 2005
|
Senior Member |
|
|
Hi All,
I was going through Tom Kytes Expert One On One Book. I was reading something about FREELISTs.
It says that FREELIST is where Oracle keeps tracks of blocks under the High Water Mark for objects that have free space on them. Oracle advances the high water mark when the Freelist is empty and blocks to the freelist.
The book also says that it is good to have more than one FREELIST if the object anticipates heavy inserts.
I have a question here, Can anyone tell me which Freelist decides the High Water Mark if we have multiple freelists for the object?
|
|
|
|
| Re: Multiple FREELIST [message #118914 is a reply to message #118910] |
Mon, 09 May 2005 12:17  |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
Hi,
If any one of the freelists run out of space it will bump-up the HWM to make more space available for that particular freelist.
BTW: You may also want to consider using ASSM (Automatic Segment Space Management) instead of having multiple FREELISTS.
Best regards.
Frank
|
|
|
|
| Re: Multiple FREELIST [message #118917 is a reply to message #118914] |
Mon, 09 May 2005 12:24 |
Anand Ramaswamy
Messages: 111
Registered: January 2005
|
Senior Member |
|
|
Thanks for your fast reply and suggestion. Actually I was just getting deep into the theory so this question came to my mind. My question is still unanswered.
As you said that the High water mark is bumped when the freelist runs out of space. I totally agree on that.
Let us consider this example:
There are 3 Freelists for an object. All the 3 freelists contains 5 empty blocks.
Now that the first freelist runs out of space and it advances the HWM and gets a free block. What happens to the other 2 freelists? Whether the new HWM is applicable to the other 2 freelist also? Does it mean that empty blocks are added to these freelists also?
My question may sound ackward to you.
Please help.
Regds,
|
|
|
|
| Re: Multiple FREELIST [message #118922 is a reply to message #118917] |
Mon, 09 May 2005 12:52 |
Frank Naude
Messages: 4580
Registered: April 1998
|
Senior Member |
|
|
Hi,
> What happens to the other 2 freelists?
They remain unchanged.
> Whether the new HWM is applicable to the other 2 freelist also?
The HWM is a segment level thing. A free block can only be enqueued to one freelist at a time.
> Does it mean that empty blocks are added to these freelists also?
No, blocks will only be added to those freelists when they also run out of space and request blocks above the current HWM.
Best regards.
Frank
|
|
|
|
|
|
| Re: Multiple FREELIST [message #118968 is a reply to message #118910] |
Tue, 10 May 2005 01:43 |
Satheesh Babu.S
Messages: 35
Registered: July 2004
|
Member |
|
|
Anand,
Each free list will be allocated to an oracle process using the formula,(P % NFL) + 1
P ==> Oracle pid in v$process
NFL ==> number as defined by FREELIST while you are creating the segment.
So, if the free list doesn't have empty block, it will be bumped up by 5 blocks. If you want oracle to increase number of blocks during HWM bump, then you can use
"_bump_highwater_mark_count".
It means oracle process will not search all the free list available for free blocks, it will search and bump only the freelist which is assigned to it using the mentioned formula.
Thanks and Regards,
Satheesh Babu.S
Bangalore.
|
|
|
|
|
|
|
|
| Re: Multiple FREELIST [message #330292 is a reply to message #119249] |
Sat, 28 June 2008 22:56 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Dear All,
Let us assume, the DB is running ASSM set to manual for some reason.
1. How do we determine the number of freelist in oracle?
My understanding is, freelist is roughly equivalent to total number of concurrent insert statement. Is this correct? Please correct me if i am wrong..
Another question,
2. when do we need FREELIST GROUPS?
Let us say, we have two freelist groups and each group has 2 freelists. Instead of this, why can't we have FREELISTS 4?
Thanks
[Updated on: Sat, 28 June 2008 22:58] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
