DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Database Extents

  1. #1
    Join Date
    Dec 2001
    Posts
    203

    Database Extents

    Dear Guys,

    For a large tablespace, is it better to define a large extent, beside defining few small extents? If I will define a large tablespace with few numbers of small extents, will it cause any performance issue in a high end server?

    Regards
    sumit

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    always have fewer larger extents.

    at the same time:
    you need separate extents (or set of extents)
    for different objects

    if you are storing many objects, obviously you need many extents.

    but:
    intentionally never plan to store one object in many extents
    (unless they are partitioned).

    more the number of extents - more the delay for I/O.

    -Raja

  3. #3
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    Row Chaining/migration will occure because of small extents.

    from Performance point of view large extents will do.
    vishal sood
    OCP 8

  4. #4
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    i think Chaining/migration is the effect
    of wrong data block size and wrong pctused and pctfree
    parameters.

    not because of the extent size.

    but small extents cause fragmentation.

    -Raja

  5. #5
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    Yes u r right

    I am sorry.
    vishal sood
    OCP 8

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Are you using locally managed or dictionary managed tablespaces?

  7. #7
    Join Date
    Dec 2001
    Posts
    203
    Locally Managed
    sumit

  8. #8
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    There is no 'magic' number when it comes to the number of extents, but I tend to prefer a smaller number of extents per segment in an LMT (up to 50 say). The reasons for this aren't performance related as such. When segments have a smaller number of extents it makes it easier to notice 'abnormal' growth trends from a monitoring perspective. Also, if a segment has a lot of extents it tends to suggest that the segment is frequentally allocating new extents. Whilst this process is 'cheaper' in an LMT there is still some overhead I believe, and so placing it in a tablespace with a larger uniform extent size is often desirable.

    In addtion, using locally managed tablespaces means that freespace information isn't cached. Therefore, a query against DBA_EXTENTS (for example) could well have to read every segment header and additional extent map in the entire database. To minimize IO for these queries it may be an idea to limit the number of extents per segment to the number of rows that can be accomodated in the extent map within the segment header block that is, approximately (db_block_size / 16) - 7 extents per segment. This information is from Steve Adams on his website.
    Put this is perspective though, this is specifically referring to queries on certain data dictionary views, which isn't something you do that often in the grand schema of things.

    HTH

    Austin
    Last edited by hacketta1; 01-19-2004 at 10:56 AM.

  9. #9
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    Tom Kyte has an excellent thread on this at - http://asktom.oracle.com/pls/ask/f?p...:730289259844,
    ____________________
    Pete

  10. #10
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Also make sure the extents are equally size.

    regards
    anandkl
    anandkl

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