Ideal Extent Size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Ideal Extent Size

  1. #1
    Join Date
    Jul 2005
    Location
    PUNE-INDIA
    Posts
    12

    Ideal Extent Size

    HI

    What should be Ideal size for Extents in LMT?
    For example I have Inventory_Data, Inventory_IDX tablespace where i am storing data , indexes created for Inventory Tables. both tablespaces are LMT
    All the tables in the Invetory_Data are IOT and their secondary indexes are stored in the Inventory_IDX. The avg Row length for each table in the Indentory_Data is 400 bytes and around 12-50 bytes for tables in the Inventory_IDX.
    All the tables in the Inventory tablespace will roughly contain 10 lack+ records and will have heavy updates and inserts on these tables and around 50-100 concurrent users will be playing with the system
    Now, how should i set BLOCK_SIZE and EXTENT_SIZE for both.
    IF BLOCK_SIZE = 8K and EXTENT_SIZE = 64 K
    Avg Row Length : 400 bytes
    No of Rows in the Block: 20
    IF PCTINCREASE = 20 No of Free Rows per Block : 4
    No of Rows per Block = 20-4 =16
    No of Rows per Extent = 16 x 8 = 128
    with same calculation i could have 162 rows per block for Index
    tablespace tables


    IF BLOCK_SIZE = 8K and EXTENT_SIZE = 1M
    Avg Row Length : 400 bytes
    No of Rows in the Block: 20
    IF PCTINCREASE = 20 No of Free Rows per Block : 4
    No of Rows per Block = 20-4 =16
    No of Rows per Extent = 16 x 128 = 2048
    with same calculation i could have 162 rows per block and 20864
    rows per extent for Index tablespace tables

    With this setting Can i able to avoid Internal fragmentation ?
    How should i maintain INITTRANS , MAXTRANS, PCTFREE, PCTUSED
    and other Segment space parameters ?
    Will larger or reduced block size will help in this situation ?


    Regards
    Shrikant Kulkarni
    Pune, India
    Regards
    Shrikant Pune
    shrikant23_datex@hotmail.com

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you considered just using automatic extent sizing and automatic segment space management?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    You have way too much time on your hands.

    Realistically, if you use LMT, you won't suffer from fragmentation. Have a read up on it.

    Use ASSM, you're obviously unsure of what extent sizes you want, let oracle handle it for you.

    Bazza

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Quote Originally Posted by slimdave
    Have you considered just using automatic extent sizing and automatic segment space management?
    I thought the answer was 42.
    this space intentionally left blank

  5. #5
    Join Date
    Jul 2005
    Location
    PUNE-INDIA
    Posts
    12

    Ideal Extent Size

    Hi All

    I am using Oracle 8.1.6 Enterprise Manager Edition which does not support Segement space management. So by setting PCTFREE parameters I need to avoid ROW chaining..
    I am really sorry..i have not mentioned it in Q.

    Waiting for reply
    Regards
    Shrikant Pune
    shrikant23_datex@hotmail.com

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The principle behind PCTFREE is that it allows you to tell Oracle that a block must not be used for inserting new records if if has less than that Percent of free space available, thus reserving that space for the growth ofindividual rows due to updates. So, your setting for pctfree must reflect the liklihood that inserted rows will grow in size post-insert. If they will never grow then you can set PCTFREE=0 (a table of invoice details might be a candidate for this, as it is static once it has been generated).
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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