-
Thanks Pando, thinking about OLTP environment. I better limit the number of extents... here I'm talking about 100 + million row tables...
Reddy,Sam
-
Excerpt from the following Oracle Doc links:
Avoid dynamic extension in dictionary-mapped tablespaces. For dictionary-mapped tablespaces, do not let the number of extents exceed 1,000. If extent allocation is local, then do not have more than 2,000 extents. Having too many extents reduces performance when dropping or truncating tables. I don't think this is an issue from Pando experience... having many extents for staging tables.
http://technet.oracle.com/docs/produ...20_io.htm#3454
http://technet.oracle.com/docs/produ...20_io.htm#3480
http://technet.oracle.com/docs/produ...0_io.htm#24620
I guess this 1000/2000 is just a number we got go by, there might not be noticable perforamance degradation if its not too many...5000 or 10000+
Reddy,Sam
-
Myths About #of extents
Reddy,Sam
-
[QUOTE]Originally posted by jmodic
About the recomendation that segments with a very large number of extents (1.000 is often quoted as a "magic limit", even in docs) should be avoided in LMT (I would say the same recomendation is valid also in DMT, if not even more!) - this is because of the fact that the space reserved in segment header for the extent bitmaps becomes insufficient and additional "bitmap blocks" must be allocated in some other extent (or in more of them) "somewhere in the middle" of the segment, thus inducing more complex bitmap management and additional I/Os.
And I absolutly agree with Jriy.
Exists only ONE limitation on a number of extents --> DB_BLOCK_SIZE
or more simple, how many blocks oracle have to allocate for a segment header.
Becuase server cannot allocate a segment header in neast blocks never. It have to
create linked list from segment header blocks and, as result, increase number of I/O
opeartion for works with the table/partition/index (segment).
--------------------------------------------------------------------------
Note: doesn't matter we use LMT or DMT.
--------------------------------------------------------------------------
[Edited by Shestakov on 08-28-2002 at 02:10 PM]
-
Originally posted by Shestakov
And I absolutly agree with Jriy.
Exists only ONE limitation on a number of extents --> DB_BLOCK_SIZE
or more simple, how many blocks oracle have to allocate for a segment header.
Becuase server cannot allocate a segment header in neast blocks never. It have to
create linked list from segment header blocks and, as result, increase number of I/O
opeartion for works with the table/partition/index (segment).
--------------------------------------------------------------------------
Note: doesn't matter we use LMT or DMT.
--------------------------------------------------------------------------
So, performance degradation (if at all there is) boils down to howmany trips(I/O) oracle needs to make towards extent bitmap to find the free space/free contiguous blocks available. I don't think it needs to be considered an issue for performance.(unless there are too many deletes, truncates, drops of objects on the tablespace).
Reddy,Sam
-
1) I wrote on only about DATAFILE HEADER BLOCKS,
2) i wrote (mostly) about SEGMENT HEADER BLOCKS.
In first case we probably can see perfomance degradation only via microscope, you right.
but in second case, this is question. SEGMENT HEADER BLOCK keep
master freelist structure and we may (i don't say must) have problems with
insert/delete... operations, if we have long linked list of header blocks.
-
There are no more tablespaces in 10i, so we don't have to worry about this concept anymore.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
and log_archive_start parameter is deprecated in 10i
-
Originally posted by pando
and log_archive_start parameter is deprecated in 10i
And UTF8 too.
Your turn :-)
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
No more tablespaces? wow...that is news. wonder what is next on Oracle list of new features. No more DBA's ???
-- Dilip
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
|