Table Sizing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Table Sizing

  1. #1
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    I am in the process of creating a database. The tables contains all sorts of data like number, varchar2, CLOBs, BLOBs etc. Can somebody tell me how can I estimate the size of the tables to allocate the size in the tablespace?
    Agasimani
    OCP(10g/9i/8i/8)

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Here are the excerpts from Metalink (by Mark Powell)
    I found it very useful for sizing tables.

    Sanjay
    ---------------------------------------------------------------
    Abbreviations

    AVIL = Available space in block to hold rows
    OBS = Oracle block size
    RS = Row size
    Ovhd = Fixed plus variable block overhead
    TBR = Total blocks required

    Expected size = (( RS * number of rows) / AVIL ) * OBS) / K or M
    where K = 1024 and M = 1048576

    Figure RS as
    for varchar2 expected number of characters for column
    for number 1 + floor(num digits / 2) + 1
    for date use 7
    + 1 byte per column in row
    + 3 byte row overhead per row

    Figure number of bytes for block as
    pctfree = decimal value of pctfree parameter * OBS

    The variable area is mostly made up of 23 bytes per initran area and 2 bytes per row for the row table entry. For 1 to 4 initrans I have calculated row overhead of 86 to 156 bytes so I just use a constant for this value. Try 113 to start.

    Figure AVIL as OBS - ovhd - pctfree

    Total bytes = number of expected rows * RS
    TBR = Total Bytes / AVIL
    Expected Size = TBR * OBS / 1024 [for K]

    This is one way and it is fairly quick and works pretty well. The formula can be improved by adjusting the variable area size for the number of initrans and for the number of expected rows in the block, but using a constant works well for us.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    If a table has both varchar2 and LOB, then allocate one tablespace for varchar2 and number data types and another tablespace for LOB columns.

    Example:
    -- CR_TAB_LOB.SQL
    --
    -- Author Tamil
    -- Date - Jan 20, 2001
    -- Example for CREATE TABLE Statement that contains LOB col
    --
    -- CHUNK is the number of bytes to be allocated for LOB Manipulation
    -- The max value is 32768 (32K) which is the largest Oracle Block Size
    -- Note that CHUNK can not be changed after creating the table



    CREATE TABLE TEST_LOB (ID NUMBER , TEST_CLOB CLOB)
    TABLESPACE TS_DVL_USER1 STORAGE (INITIAL 256K NEXT 256K PCTINCREASE 0)
    LOB(TEST_CLOB) STORE AS (TABLESPACE TS_DVL_USER2
    STORAGE (INITIAL 2M NEXT 2M )
    CHUNK 16K PCTVERSION 20 NOCACHE LOGGING)
    ;

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    Follow earlier entry in thread for table sizing, but i would prefer have the extent size constant and equal to extent size set for tablespace.Only reqt of doing of sizing of table is to decide on which tablespace to put this table on.I suppose you have different tablespaces for small tables,medium and large.


    For LOBS, it deoends whether you want to store in row data or out of row that is you want to define seperate storage space for lobsegment or you want to store in the table storage area.

    IF out of row (which should be the case if high update and query and size is more than 4K) follow tamils response.To add to that chunk size should be equal or multiple of oracle_block_size and depends on the type and amount of IO.
    And set PCTversion also depending upon whether and how many users would simultaneously read the LOB, if high concurrency and high number of transactons , keep PCTVERSION high.
    Logging , if u have a standby database or want recoverable option for LOB then use LOGGING , also depends on whether u want to use cache or not.
    Cache takes lot of db_block_buffers and causes lot of misses for other objects.Use only if the transaction size and chunk size is small.
    READ/Write is done in chunk size.

    thanks
    GP

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