Adding Freelists to LOB Segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Adding Freelists to LOB Segment

  1. #1
    Join Date
    Sep 2006
    Posts
    23

    Adding Freelists to LOB Segment

    I am trying to add freelists to a table and lob segment. I added freelists for that table with:

    alter table storage (freelists 4);

    But I don't know the syntax to add freelists to a lob segment. The lob segment is in its own tablespace if that makes a difference. I have tried:

    alter table modify lob() (freelists 4);

    But it did not work. Somewhere I read that lobs don't use freelists. But I know I can set the number of freelists for the lob when creating the table. I have not been able to find anything after hours of searching. Thanks for any help!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    LOB Segment does not have FREELISTS parameter, however, in a RAC environemnt you can set FREEPOOLS parameter which is equivalent to FREELIST GROUPS.

    Tamil

  3. #3
    Join Date
    Sep 2006
    Posts
    23
    I am trying to get 1 freelist group with 2 or 4 freelists total. Would setting the FREEPOOLS parameter to 2 be equal to having 2 freelist within 1 freelist group? The reason I am asking is becuase my table currently has 3 freelist groups and 8 freelists for both the table and lob segment. There seems to be alot of unused space below the high water mark. I am trying to reduce it to one freelist group and 2 or 4 free lists. I can do this for the table, but not the lob. If freelists are not used for lobs, why can I query user_segments and get the freelists and groups for the lob segment? I'm not disagreeing with you, I'm just trying to understand better. Right now the size of the lob tablespace is 5 times the size of the total size of all the Thanks!

  4. #4
    Join Date
    Sep 2006
    Posts
    23
    Sorry forgot some of my last sentence:

    Right now the size of the lob tablespace is 5 times the size of the total size of all the lob in the tablespace. I found this by using dbms_lob.getlength.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    See my article on freelists and freelist groups.

    http://www.beaconinfotechcorp.com/art_004.html

    You have to be very careful when adding freelist groups because of space wastage.

    Tamil

  6. #6
    Join Date
    Sep 2006
    Posts
    23
    Very nice article tamilselvan. Right now I have a 2 node RAC setup. On all of my segments, they have 3 freelist groups and 8 freelists. I think I only need 2 freelist groups (maybe one depending on the # of concurrent transactions). Could all the extra space being wasted be caused by the 3rd freelist group. As far as I understand, each instance in a RAC environment will be connected to their own freelist group. Does this mean the 3rd group is never used? So when deletes are done, will freed blocks also go to the 3rd group? Also, is there a range of the # of concurrent transactions I should have before implementing 2 freelist groups compared to one? Thanks for all the help!

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    If you have 2 nodes RAC environment, then you should use 2 FREELIST GROUPS. Never use more than needed.

    Note:
    Manually setting of FREELISTS and FREELIST GROUPS for the database objects should be avoided as much as possible unless the client needs the best performance (measured in < 5 seconds).
    To over come this manual setting, Oracle introduced ASSM. With the ASSM you can also get good performance (not the best) on RAC environment. If disk space is not an issue, I would suggest to use ASSM.

    Tamil

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