Re: Defrag 8i DB

"Jeff McClure" <jeffrey_mcclure_at_wideopenwest.com> wrote in message news:32ea946a.0402200046.441bec84_at_posting.google.com...
> Folks,
>
> I need to defrag my DB (as well as reorg) for performance reasons.

Tablespace fragmentation has no performance impact at all. Re-organising tables might, but not for the reasons you might think.

> I
> have been looking through various postings and papers and one struck
> me. It is a white paper posted on URL:
> http://otn.oracle.com/deploy/availability/pdf/defrag.pdf. It suggests
> using the SAFE algorithm to eliminate defragmentation. Within the
> paper, it suggests using segment sizes of 160K, 5120K and 160M as
> required. Where did these numbers come from? Being an old school
> DBA, I learned that the most efficient disk access came from using a
> multiple of the physical buffer size (generally 512K).

I think you might be wrong on the 512K as the physical buffer size. If you're talking about the file system buffer, then that's usually 512 *bytes*, not kilobytes.

They picked those sizes for no particular reason that I could fathom. Someone once pointed out that they have even steps between them, but I always felt they were "peculiar". If you're going to take that paper's advice (which is generally sound -"use a few standard extent sizes") then use the ones which Oracle itself uses for locally managed auto-allocate tablespace: 64K, 1M, 8M and 64M (oh, and 256M if you really need it). Indeed, if you have a late-patched 8i or better, use auto-allocate LM tablespace and have done with it.

>Although,
> these are in fact multiples of that buffer, they do not evenly divide
> into the standard block sizes (mine is 8K). Can someone explain to me
> how this is more efficent and why I should consider changing extent
> size throughout my DB to fit this scheme?

Because if everything comes in the same sized extents within a tablespace, you can't get tablespace fragmentation, which is not a performance worry, but is a waste of space worry. Because if you stop sitting there umming and ahhing about what the "right size" extent is for a table, but merely drop it into a tablespace with OK-ish sized extents, you can stop worrying about extent acquisition and sizing issues and go and be more productive elsewhere.

>The performance of my DB is
> lack-luster, at best and as it has exceeded 30 GB, I would like to get
> it all under control.

There is not going to be a performance benefit from sorting your extent sizes out. There may be some performance benefit from re-organising tables which are suffering from inflated high water mark syndrome, but you can check if that's the case by collecting some statistics on the table and checking what the average space is in DBA_TABLES for a given segment. It is highly unlikely that re-organising indexes is going to give you any noticeable performance benefit either.

You didn't mention a version, but if you've got 8i or 9i, this stuff is really pretty ancient, and is not the thing to get worried about these days.

>
> Thanks for your input.
>
> Jeff

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------