OraFAQ Forum: Server Administration » when a block is removed from free list

Home » RDBMS Server » Server Administration » when a block is removed from free list

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

when a block is removed from free list [message #143798]

Sat, 22 October 2005 10:57

contactkeval
Messages: 23
Registered: October 2005

Junior Member

i was going thru the online documentations and came across following text

For each data and index segment, Oracle maintains one or more free lists—lists of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE. These blocks are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the INSERT statement, and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.

my question is when does oracle takes a block off from the free list? does it check after insert if enough space is available for furhter inserts OR after failed insert attempt as mentioned above?

source:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10743/logical.htm#i19214

[Updated on: Sat, 22 October 2005 11:08]

Report message to a moderator

Re: when a block is removed from free list [message #143799 is a reply to message #143798]

Sat, 22 October 2005 11:13

contactkeval
Messages: 23
Registered: October 2005

Junior Member

or oracle checks after successful insert that available space is more than PCTFREE and after unsuccssful insert attempt available space less than PCTUSED?

pls clafify if my above understanding is correct.

thanks

Report message to a moderator

Re: when a block is removed from free list [message #143918 is a reply to message #143799]

Mon, 24 October 2005 03:20

sunil_v_mishra
Messages: 506
Registered: March 2005

Senior Member

hi,

I think oracle check for the free list when it finds the free block it insert into the free block (i.e. uses the block), now when insertion is over oracle update the records of free list( i.e. how much free blocks are available)

Regards
Always Friend sunilkumar

[Updated on: Mon, 24 October 2005 03:22]

Report message to a moderator

Re: when a block is removed from free list [message #143966 is a reply to message #143798]

Mon, 24 October 2005 07:39

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Do you have a good reason for using free lists with oracle 10g on your system?

Report message to a moderator

Re: when a block is removed from free list [message #144114 is a reply to message #143966]

Tue, 25 October 2005 02:23

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

The movement of block into & out of freelist is totally dependent on pctfree parameter. If blocked is having empty space above pctfree, it will remain in freelist, else it will be out of freelist.

FREELIST, PCTFREE and PCTUSED

    While creating / altering any table/index, Oracle used two storage parameters for space control.

        * PCTFREE - The percentage of space reserved for future update of existing data.
           
        * PCTUSED - The percentage of minimum space used for insertion of new row data.
          This value determines when the block gets back into the FREELISTS structure.
           
        * FREELIST - Structure where Oracle maintains a list of all free available blocks.

    Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value.

    When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value.

    Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks.

Source: http://www.akadia.com/services/ora_chained_rows.html

--Girish

Report message to a moderator

Previous Topic:	removing enterprise options
Next Topic:	Urgently needed solution for Error:-- ORA-00607

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 12:47:38 CST 2025