Home » RDBMS Server » Server Administration » Multiple FREELIST
icon7.gif  Multiple FREELIST [message #118910] Mon, 09 May 2005 11:44 Go to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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 #118923 is a reply to message #118922] Mon, 09 May 2005 12:56 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks for your reply again. Can you suggest some link which throws more light on this area?

Regds,
Re: Multiple FREELIST [message #118968 is a reply to message #118910] Tue, 10 May 2005 01:43 Go to previous messageGo to next message
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 #119104 is a reply to message #118968] Tue, 10 May 2005 10:38 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Thanks satheesh,
Your reply was really informative. Good to see a bangalore guy to reply. Me also from Bangalore. Which company do you work for?

Regrds,
Re: Multiple FREELIST [message #119249 is a reply to message #118910] Wed, 11 May 2005 08:12 Go to previous messageGo to next message
Satheesh Babu.S
Messages: 35
Registered: July 2004
Member
Iflex.
Re: Multiple FREELIST [message #330292 is a reply to message #119249] Sat, 28 June 2008 22:56 Go to previous messageGo to next message
shrinika
Messages: 266
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

Re: Multiple FREELIST [message #330301 is a reply to message #330292] Sun, 29 June 2008 02:19 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See my answer to your question in http://www.orafaq.com/forum/t/121071/102589/

Regards
Michel
Previous Topic: ASSM with LMT
Next Topic: Diff in dba_segments & dba_extents output.
Goto Forum:
  


Current Time: Fri Dec 09 08:03:05 CST 2016

Total time taken to generate the page: 0.13048 seconds