Re: Defrag 8i DB

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Fri, 20 Feb 2004 13:58:48 +0100
Message-ID: <c1507l$j36$1@news2.tilbu1.nb.home.nl>

Howard J. Rogers wrote:

> "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.

There was some article explaining that as well ('Stop defragmenting and start living' or someting?!?). V7 stuff, 5 blocks minimum allocated, plus overhead, some sauce, presto: 160k.

> 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.

:-)) Noticable vs measurable.
Always hard to explain to users and management. Them: 'isn't it time to do some reorgs for performance?' Me: 'Why - are you having problems in a particular area, then?'
>
> 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.
>

It's in the header: 8i... Patch to 8.1.7.4 (if not done), and start using/migrating Locally Managed Tablespaces (LMT's)
>
> Regards
> HJR

-- 

Regards,
Frank van Bortel

Received on Fri Feb 20 2004 - 06:58:48 CST