Storage Parameters
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Storage Parameters

  1. #1
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    In our new application, we designed the database and created the datamodel.
    we even sized the tables came with storage parameters for each table.

    but our DBA people tell that they will give us three tables sapces

    1.data1 with uniform extent size 160K
    2.data2 with uniform extent size 5MB
    3.data3 with uniform extent size 160MB

    now they are telling us to

    1.create tables which are going to be less than 5MB in data1 with no storage
    parametes for the table

    2.create tables which are going to be less than 160MB in data2 with no
    storage parametes for the table

    3.create tables which are going to be more than 160MB in data1 with no
    storage parametes for the table same case with the indexes. it doesn't look
    reasonable to me.


    because for a table with an estimated size of 120MB we place it in data2. as
    we create there it takes tablespace's storage parameters and it has to give
    5MB 24 times to make it 120 as the table grows in size which i think is
    expensive. and for tables with say 2k size(even less) i have to keep in
    data1 with 160k, where the rest of the space gets useless.


    DBA claim that Numerous test by Oracle Corporation and others proved that
    this design provides ultimate performance and prevents fragmentation. first
    of all my questions are 1. are they right doing so? or am i wrong 2. if they
    are not right i have to prove them with whatever they claim is wrong(either
    thru documentation or thru queries which should show a solid proof to make
    them convinced.)as our application so complex we don't want any performance
    hitches.

    Thanks.
    Vijay.
    Say No To Plastics

  2. #2
    Join Date
    Jul 2002
    Posts
    132
    Vijay,

    Ur point is quite valid but just having more number of extents does not fragment a database. Normally one tends to think that it is not right to have more number of extents, rather have a single extent of a larger size- but then I ask, what is the loss? If at any point if the data is deleted, it can be reused and with LMT, u dont even need to coalesce the tablespaces.

  3. #3
    Join Date
    Jul 2002
    Posts
    132
    Go thru this link too

    http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1667293495336,%7Bextents%7D

  4. #4
    Join Date
    Jul 2002
    Posts
    132
    Another excerpt

    "Most importantly -- it is IMPOSSIBLE to have free space fragmentation in a
    locally managed tablespace with uniform extents. Since every extent is the size
    of every other extent -- ANY extent is the correct size for ANY objects next
    extent. If you have 500m free in a locally managed uniform tablespace -- you
    really do have 500meg free

    In Oracle8i, use locally managed tablespaces with uniform extents. Oh yeah, if
    someone says "but that'll cause our objects to have many extents" just say "so
    what, who cares". It is perfectly OK to have objects with many 100's of
    extents. "

    So if the DBA has planned for LMT with uniform extents, u dont have to worry. Hope it is clear now

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    Thanx for your reply.

    But I would like to know in what way it helps.
    It is going to occupy more no. of extents and if a full table scan is going to be done then it has to go through all the extents(extents need not be contiguous). So the amount of time it takes to read the data increases and hence some performance degradation will be there.

    Please clarify.

    Thanks.
    Vijay.
    Say No To Plastics

  6. #6
    Join Date
    Aug 2002
    Posts
    1
    I believe what your DBA is referring to is known as SAFE (Simple Algorithm for Fragmentation Elimination), available as a whitepaper on metalink at http://metalink.oracle.com/cgi-bin/c..._cr.cgi?239049

    The idea is to keep uniform extent sizes in tablespaces and move objects as they approach a given threshold (512 extents, 1024, whatever) to a tablespace with larger extents.

    I believe the aim is lower fragmentation rather than improved performance, although some would argue it is a means to the end. Also, no need to coalesce tablespace, and so on.

    Hope this helps.

    Dave

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