Re: Trying to find good candidate / objects for re-org in SAP Databases --Weekly objects list, Monthly Objects list and Once in a while ( six month once )

From: Dragutin Jastrebic <orahawk_at_gmail.com>
Date: Thu, 13 Sep 2012 21:26:07 +0200
Message-ID: <CANGCQwk49HnPmusSJ9Cj_Sg1xcXR3Ckq9UUuQfoxkD3FP-4-iQ_at_mail.gmail.com>

Hi,
I suggest that you read also all Asktom's articles related to index rebuilds and
also find index fragmentation/rebuild related examples given by Jonathan Lewis and Richard Foot.

In short:

The simple full space/empty space ratio (given by space adviser or some other tool or script) isn't enough good criteria to schedule defragmenting process, since this is only a static approach.

This is just a picture of your database at the moment X.

If you want a true benefit (in the term of space or performance) from your rebuild strategy,
more thorough approach is needed (let's call it " the dynamic approach")

Why this dynamic approach is necessary ?

Let's say you have fragmented tables (with half-empty blocks) or indexes

If you rebuild , your table scan /index scan might be faster.

But , remember, if your application has a lot of concurrent sessions (acceding the same blocks)
than your half empty blocks could possibly provide better response time , because there will be much less collisions and waits (buffer busy waits etc)

 So fragmented tables/indexes can even outperform compacted tables/indexes

Therefore, it all depend on your application(s) (tables!) , some of them may need reorganisations,
some of them do not. Some of them will quickly fragment the tables /indexes again after you have compacted them.

So you need to carefully analyze your system behaviour before and after rebuild and compare
it .

Again, the used space/empty space ratio is just a picture of your database taken at the moment X.

1. It does not say anything about your application behaviour - will it perform better with fragmented data or with compacted data
2. it does not say anything about "the speed of fragmentation"

Also, in addition to this empty space/full space ratio, perhaps you will hear people saying that number of extents and number of chained rows are criteria for rebuilding

The number of extents was more important with dictionary managed tablespaces, but today, all tablespaces should be localy managed

Concerning chained/migrated rows, check your "table fetch by continued row" statistics (from statspack/AWR for example) to see whether you need to worry about it or not.

And if you have rows that cannot fit inside one block, they will be chained anyway.

The one thing you may consider is the clustering factor of your indexes, perhaps that reordering data can give you much better performance in some cases

HTH Dragutin

2012/9/7 Rich Jesse <rjoralist2_at_society.servebeer.com>

> Raja writes:
>
> > I am trying to find the good candidate / objects for reorg in SAP
> > databases. We are planning to do,
> >
> > We are planning to do weekly reorg, monthly once and every six month
> once.
> > Accordingly we need to identify the objects and schedule the re-org. Any
> > one in the group, provide assistance on that.
>
> Unlike some database platforms (like MSSQL), I would question the need for
> any reorg in Oracle. The only time I do reorgs are in special cases, like
> for our upcoming data archival project, where a table's average size (and,
> most likely, that of its associated indexes) will be permanently reduced.
> Tom Kyte has a great writeup on the how and the why for reorgs:
>
>
> http://asktom.oracle.com/pls/askto/f?p=100:11:::::P11_QUESTION_ID:35336203098853
>
> If that URL gets mangled, try: http://tinyurl.com/asktomreorg
>
> GL!
>
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Sep 13 2012 - 14:26:07 CDT