On Jun 29, 2:51*am, "Okonita via DBMonster.com" <u36825@uwe> wrote:
> Hello Peter,
> Thanks for the post. It confirms what seems to be happening here especially
> that about tables with few rows. When I look at the tables falls into this
> category, it appears to mainly tables with very few rows or some tables
> defined for 4K tablespace but has since seen many column additions. Couldit
> be that the second type of tables are not "fitting in" nicely in the 4K
> tablesspace (just like Ian was saying in his last post) *and REORGCHK sees
> that as needing reorging even if one has just been done? I don't know. I am
> not an expert in reorg utility.
>
> If I may ask, could you share your version *SQL script to generate reorg list?
> That will be appreciated and just as much educational how someone else is
> doing his reorgs.
>
> Thanks
>
>
>
> peter wrote:
> >> > Hi all,
> >> > I am very surprised to see that after doing a Reorgchk followed by reorg of
> >[quoted text clipped - 17 lines]
> >> see it for tables depending on your physical table design / page size
> >> etc. *But without more information we can't help you.
>
> >I have seen this and it is a problem with the way metrics are used
> >generically without consideration of some aspects of a table. *From
> >memory the the cluster ratio metric doesn't determine if it is a
> >clustering index. *The other good one is using on-line reorg. *It
> >cannot reduce the table to one page so if the table has a few rows it
> >always appears as a re-org required regardless of how many times you
> >do an online reorg. *On this if the table is less than a few pages you
> >are better off performing a offline reorg. *Then it goes down to one
> >page. I gave up using the utility and generated my own SQL statement
> >to determine the re-org list. *It can be used as a base but you have
> >to mask out certain options.
>
> >By the way, it doesn't pick up certain situation. *It cannot pick up
> >defragmentation within the tablespace where you have multiple
> >objects. *We discovered a major performance issue in this area (raised
> >a PMR on version 9) *My suggestion is to use the clause to determine
> >priority items to re-org and then re-org every table/index that
> >sustains updates regardless.
>
> --
> Message posted viahttp://www.dbmonster.com

The script evolved into a java UDF which does a lot more than just
selecting a re-org list. It works out an optimal schedule for a re-
org window based on past performance details, resources required,
running multiple streams and so on. For example, is there enough
space to do a inline re-org or should temporary area be used. Has
exclusion lists. Some tables such data propagator control table or
using table for event monitors cause issues. Don´t mind telling
people what it does and why but I want to maintain ownership of the
techniques I have used for obvious reasons. Sorry.