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@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Sep 2012 21:26:07 +0200
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.
a) It does not say anything about your application behaviour - will it
perform better with fragmented
data or with compacted data
b) 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@xxxxxxxxxxxxxxxxxxxxx>
> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
