Segment space management issues
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Segment space management issues

  1. #1
    Join Date
    Jul 2006
    Posts
    10

    Segment space management issues

    Hi,
    I have a theoretical doubt with regards to manually managing segment space. There r 3 parameters, PCTFREE, PCTUSED and FREELIST. Data can be inserted into a segment upto a point where we reach the PCTFREE parameter value. also when we want to insert data into a segment already with some data existing, we can only insert data if the existing data is occupying space below the PCTUSED parameter value. So once a segment has data lesser than the PCTUSED value, then this segment is put into the freelist for insertion. my question is, once a segment is in the free list, can i be allowed to delete data from that segment?
    It would be nice if someone cud answer this question.

    thanks,
    Jay

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by bjayaram
    my question is, once a segment is in the free list, can i be allowed to delete data from that segment?
    It would be nice if someone cud answer this question.
    Not sure I understand this? Just because a block is on the freelist doesn't mean you can't delete from that block.

    The block will remain on the freelist until it reaches the PCTFREE limit. Once it reaches the PCTFREE, the block will be taken off the freelist. If rows are deleted in the block or rows decrease in size due to updates and the freespace falls below PCTUSED, then the block is put back on the freelist.

    Have you seen this How PCTFREE and PCTUSED Work Together.

    If possible, use ASSM and you don't have to worry about this.

  3. #3
    Join Date
    Jul 2006
    Posts
    10
    Quote Originally Posted by ebrian
    Not sure I understand this? Just because a block is on the freelist doesn't mean you can't delete from that block.

    The block will remain on the freelist until it reaches the PCTFREE limit. Once it reaches the PCTFREE, the block will be taken off the freelist. If rows are deleted in the block or rows decrease in size due to updates and the freespace falls below PCTUSED, then the block is put back on the freelist.

    Have you seen this How PCTFREE and PCTUSED Work Together.

    If possible, use ASSM and you don't have to worry about this.
    thanks a lot.. I have one more doubt... what is the need to use the PCTUSED parameter then? why do i need to go all the way down to 39% in order to insert assuming my PCTUSED = 40.

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    Typically, PCTUSED is relevant only in tables that undergo deletes. You may be able to pack rows into blocks more tightly by setting PCTUSED to be higher than 40%.

    For tables with many inserts, changing PCTUSED may improve block storage performance. Blocks that are densely populated can cause freelist contention, but fewer blocks are required, tables are smaller, and read operations are faster.

  5. #5
    Join Date
    Jul 2006
    Posts
    10
    i did not understand this... can you please explain with an example?

  6. #6
    Join Date
    Apr 2006
    Posts
    377

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width