To Reorg or Not To Reorg..

All

This is not a product spam message - we're interested in hearing peoples opinions on something.

We're looking at the topic of table and tablespace reorganisations at the moment. As time has gone by, various schools of thought have come and gone. In the good old, bad old days free space fragmentation due to inconsistent extent allocations/pct increases etc, and therefore fragmented (and inconsistently sized) free extents were a good reason to reorg a tablespace.

Times move on, and LMT's, uniform extents etc tend to make that less of an issue.

Some schools of thought used to say "keep all your data in a minimum number of extents", but now we hear (know) that this is "no longer" an issue.

Volatile tables with mass deletions end with free block space issues. Row-chaining may be a matter of concern from a performance perspective (in or out of block takes CPU cycles, logical and, potentially, physical I/O etc).

Table size has been raised and recovering free space after any reorg action is taken. Some sites intentionally promote tables through small, medium and large LMT's based on initial, next extent preferred defaults.

The knock on effect of improved throughput when such issues are addressed can be dramatic, with runtimes going down from hours/minutes, to minutes/seconds.

We'd like to pose some questions to you:

1. Silly question - do you even consider or care about the data distribution in your tables/tablespaces?
2. If you do, how do you identify a reorg candidate? Is it by any of the methods above - or something different. What script would you execute in doing it "your way"? As an example, we know someone who looks at the differential sizing of an index after an index rebuild.
3. Is your concern more at the table "fragmentation" level, or at the tablespace level?
4. Do you follow a policy of sizing tablespaces (small, Medium/large) as mentioned above?
5. Summary question, covers all of the above - what do you do, and how have you instrumented/scripted it?

We would be interested in any input on this topic. You may know we're a vendor but don't worry, your response will not be used in any way other than for us to build a profile of current practices in use by people on the list who are obviously genuinely interested in Oracle throughput and performance issues.

We also think that opening this topic may be of genuine interest to all members of the list.

Thanks to any who respond, as part of this we will be putting together an overview / summation of all respondents (both on this list, and from our own channels), which I will make sure is shared with everybody here.

Cheers

Mark

Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005
 

--
http://www.freelists.org/webpage/oracle-l