-
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
-
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
-
Row Chaining/migration will occure because of small extents.
from Performance point of view large extents will do.
vishal sood
OCP 8
-
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
-
Yes u r right
I am sorry.
vishal sood
OCP 8
-
Are you using locally managed or dictionary managed tablespaces?
-
-
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.
-
____________________
Pete
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|