Reorgchk, Reorg, Runstats not having any effect after completion. Why is this?
in [DB2]
|
Prev: heavy inserts and bufferpool
Next: TRANSLATE function
From: Okonita via DBMonster.com on 23 Jun 2008 19:47 Hi all, I am very surprised to see that after doing a Reorgchk followed by reorg of selected tables and concluding with a runstats of the reorged tables, all of the tables continue to be identified and selected as reorg candidates in subsequent/followup reorgchk. Has anyone had this experience? Can you share with me what you may have found out to the the reason and if possible what are the possible solutions to correct the situation? This is very important to us to get this tables to their optimal state and I'll greatly appreciate a solution to this issue. Thanks -- Message posted via DBMonster.com http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200806/1
From: peter on 28 Jun 2008 02:52 On Jun 25, 6:46 am, Ian <ianb...(a)mobileaudio.com> wrote: > Okonita via DBMonster.com wrote: > > Hi all, > > I am very surprised to see that after doing a Reorgchk followed by reorg of > > selected tables and concluding with a runstats of the reorged tables, all of > > the tables continue to be identified and selected as reorg candidates in > > subsequent/followup reorgchk. > > > Has anyone had this experience? Can you share with me what you may have found > > out to the the reason and if possible what are the possible solutions to > > correct the situation? > > > This is very important to us to get this tables to their optimal state and > > I'll greatly appreciate a > > > solution to this issue. > > As I suggested before, please post the output from reorgchk -- before > reorg, then after reorg/runstats. > > As I explained before, this is very common with indexes. You may also > 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.
From: Okonita via DBMonster.com on 28 Jun 2008 12:51 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. Could it 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 via http://www.dbmonster.com
From: peter on 29 Jun 2008 01:02 On Jun 29, 2:51 am, "Okonita via DBMonster.com" <u36825(a)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. Could it > 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.
From: Okonita via DBMonster.com on 29 Jun 2008 05:40
What do you mean by "For example, is there enough space to do a inline re-org or should temporary area be used". ? If you have space to do a reorg, you have space to do a reorg in-line/off- line, does it matter? If you don't have enough space, you don't have enough space. Both reorg method need space to work with, right? Please correct me in this matter as I am still learning some of the finer points of this tool... Thanks peter wrote: >> Hello Peter, >> Thanks for the post. It confirms what seems to be happening here especially >[quoted text clipped - 39 lines] >> -- >> 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. -- Message posted via http://www.dbmonster.com |