Storage settings for a large insert only table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Storage settings for a large insert only table

  1. #1
    Join Date
    Aug 2000
    Posts
    143

    Question

    Can anyone tell me what are good settings for a large insert only table ie pctfree, pctused, initial extent, maxextent?

    This table currently has 30 million rows and 500 are added daily.


  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    insert only > small pctfree (let's say 5), big pctused (~90)
    but as far as initial and next size are concerned, it mainly depends of the average row length, which we cannot guess ...

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Wink


    Hopefully the table already exists somewhere and you can do an analyze table command and check it out in dba_tables to get the avg row length etc.

    good luck!

    - Magnus

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pipo
    insert only > small pctfree (let's say 5), big pctused (~90)
    Depends on what you are looking for. Are you looking to maximize storage? Then these settings are probably fine.

    If you are looking to maximize performance, you should set the pctused to a low value to avoid blocks being linked and unlinked from the free list.

    As far as the initial and next extents, use a Locally managed tablespace with automatic extent management and don't worry about it.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Aug 2000
    Posts
    143
    Jeff,

    Do the blocks in this table get effected by the freelist if the table is insert only?

    Here are the stats:
    PCTFREE=10
    PCTUSED=80
    Avg_row_length=32
    Initial_extent = 1000Mb

    This table does not get updated after it is inserted into, but there may be a possibility that older data maybe moved to an archive table.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Just inserting, no. However, if this is true:
    Originally posted by mb
    there may be a possibility that older data maybe moved to an archive table.
    then you will have deletes. If a block gets down to 79% full, it will get put on the free list. If you are deleting a lot of data, the chances are good that you will get many blocks that are 79% full. These blocks then get put on the free list. When you go back to inserting data, Oracle will seach all these blocks only to use another 12% (91-79) of the block. This would yield good utilization of the block, but slower insert performance.

    If the PCTUSED was set to a smaller number, say 10, then the block would have to get down to 9% before it was put back on the free list. When the block finally got back on the free list, you would have 82% (91-9) use of the block before it goes off the freelist again. Since this would leave unusable space in the block until you get down to 9% you would "waste" space but your inserts would go much faster because they would not have to search an artificially long freelist.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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