Re: Defrag 8i DB

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<4035d318$0$29129$afc38c87_at_news.optusnet.com.au>...
> "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
Howard,

Oracle using an extent size of 64KB as the starter in auto-allocate has me wondering. I chose 128KB extents (uniform) way back when we migrated from 7.3.4.5.x to 8.1.6.3.0. I shudder to think that I should perform a re-org, just to reclaim 32 MB of space, for those segments that didn't allocate more than 8 (8KB block size) blocks.

Our default db_file_multiblock_read_count was usually 16, so 16 * 8192 = 128KB.
It certainly kept the math easy.

In Dell-land, their PERC raid controllers had a max stripe size of 256 KB.
In our environment, setting dbfmbrc = 32 caused the CBO to think that full table scans were too cheap, so 128KB reads for fts seemed okay. It certainly deserved more testing than I gave it.

so now that we're getting around to testing 10.1.0.2 (on RHEL 3 ES), I guess I'll look at using auto-allocate with automated segment space management, for everything that is not clustered. I don't see me using anything other than uniform-sized extents with clusters, but I also haven't read anything regarding storage parameter impacts on index and hash clusters in 10g.

Pd Received on Fri Feb 20 2004 - 15:25:31 CST