I don't know if you intended to "shout", but using
color for your reply certainly does
so...
----
Can you prove any benefit from the
extraordinary actions of overriding LMT extent control and using EXP/IMP,
ALTER TABLE ... MOVE, ALTER INDEX ... REBUILD, or whatever, when number of
extents is the only criteria?
If so, was it DML or DDL statements which
demonstrated improved performance?
Also, if there was any testing performed, then how
did you isolate the issue of "number/size of extents" away from the issues of
"row-migration" and 'blocks made empty due to deletion activity'?
----
What you are referring to as "fragmentation"
(which has never meant simply "the number of extents") does not equate
to "performance degradation" at all. There is a difference in the possible
performance impact of number/size of extents on DML statements as opposed
to DDL statements, as previously explained. Simply put, the
"conventional wisdom" left over regarding extents
in dictionary-managed tablespaces has little or no application in the world
since the introduction of locally-managed tablespaces.
For example, a hundred million extents for a
table or index would *not* impact the performance of indexed scans
(i.e. DML) in *any* way whatsoever (due to single-block, random-access
reads). However, this situation would be catastrophic to any DDL involving
extent allocation/deallocation in a dictionary-managed tablespace
(requiring database recreation) as *every* other DDL involving extent
allocation/deallocation could be crippled (due to abused cluster tables in the
UET$/SEG$ cluster in the data dictionary). By the same token, dropping or
truncating a table with a hundred million extents would take a while even
in locally-managed tablespaces, but the impact would not be as globally
catastrophic for the rest of the segments in the database or for the database's
data dictionary itself.
|
Title: RE: datafile sizing question
- RE: datafile sizing question Rachel Carmichael
- RE: datafile sizing question VIVEK_SHARMA
- RE: datafile sizing question Rachel Carmichael
- RE: datafile sizing question Jamadagni, Rajendra
- Re: datafile sizing question Tim Gorman
- Re: datafile sizing question Tim Gorman
- RE: datafile sizing question DENNIS WILLIAMS
- RE: datafile sizing question Rachel Carmichael
- RE: datafile sizing question Jamadagni, Rajendra
- RE: datafile sizing question VIVEK_SHARMA
- Tim Gorman