Re: Q:Table Fragmentation. How do I reduce it?

Steve Phelan wrote:
>
> Connor McDonald wrote in message <350E3E15.56FA_at_bhp.com.au>...
>
> >Also worthy of note that in a normal (ie multi-user) environment then as
> >a general rule, lots of extents will NOT affect performance. The
> >popular belief that the performance of one extent is always better than
> >many is a myth...
>
> Well, that's not true if you have a system with large tables that you
> frequently full (or range scan - when you've partitioned the data or taken
> an explicit load order). The performance can be much worse, especially if
> the extent sizes are not matched to the IO size of the machine and the
> multi-block read count is also set to an unsuitable figure.
>
> Not to mention what the heavy fragmentation is doing to the free-space
> coalescing...
>
> Sure, many people become paranoid about having data in more than, say, 5
> extents, and that is just silly. Likewise, throwing your hands up and saying
> any amount of extents is not a problem is also silly.
>
> >
> >This is not to say the exp/imp will not assist. Unloading and reloading
> >the data has many benefits in terms of better packing the rows etc
> >etc...But exp compress=y OR exp compress=n will give you the same
> >benefits...
>
> Er, no it won't. If you took one of my databases where I've spent the entire
> design stage setting suitable initial and next extent values for all my
> objects and imported it with compressed extents, well, I'd have to shoot
> you.
>
> The point I'm trying to make is that this is a complex area. The original
> poster should read up a little. The Oracle White Papers on space management
> are a good start.
>
> Steve Phelan.
>
> >--
> >==========================================
> >Connor McDonald
> >BHP Information Technology
> >Perth, Western Australia
> >"These views mine not BHP..etc etc"
> >
> >"The only difference between me and a madman is that I am not mad."

Point taken but don't you agree that you things you mention are essentially about getting you extent SIZING correct as opposed to the NUMBER of extents...

eg (say) 8k blksize, db_mul...count = 8 and IO buffer limit of 64k at the OS, then any reasonable extent size that is a multiple of 64k is going to give identical (full scan) read performance, simple because all full scan reads will be done in multiples of 64k anyway...

Conversely any extent size that is not an appropriate multiple will impact performance...

Maybe it is just a semantics thing...

Cheers

-- 
==========================================
Connor McDonald
BHP Information Technology
Perth, Western Australia
"These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad."