You are not logged in or registered. Please login or register to use the full functionality of this Website SybaseTeam.Com...
Hello There, Guest! (LoginRegister) Remember Me? Current time: 04-27-2015, 06:06 AM
   Home  |  About Us  |  Sybase Training  |  Synergy  |  Consulting  |  Job Openings  |  Tech Videos  |  Rules and Disclaimer  |  Search
Post Reply 
Forum Tools
Script to report list of tables requiring reorg
11-01-2010, 08:23 AM
Post: #1
Quote this message in a reply
Script to report list of tables requiring reorg

Does anyone have a script to report list of tables in a particular database/server requiring reorg based on cluster ratio threshold (say 0.5) ?

Appreciate your help.


Find all posts by this user
10-06-2011, 01:15 AM
Post: #2
Quote this message in a reply
RE: Script to report list of tables requiring reorg

Yes we have, but it is (a) much more full featured [takes a very detailed inventory] and producing REORG REBUILD statements is a tiny part (b) it is commercial, so I can't post the code. Drop me a line if you are interested and you have a budget.

But for this narrow need, just what you have requested, or for a more generic list of DataStructures, the code is easy to write.

You need to be specific about (a) what type of REORG and (b) which cluster ratio you want to examine. For (a) I will assume REBUILD. Of course, you are talking about DPL/DRL tables, so I will leave APL tables out of the discussion.

(b) requires a bit of discussion. DERIVED_STAT() does some things right and some things inconsistently. For some invalid requests:
it produces NULL, which is correct because there is no such thing as a Clustered Index (IndexId = 1) for DPL/DRL tables.

Similarly, but not quite consistently, if you execute

on a table that has a Clustered Index (APL only of course), IndexPageClusterRatio produces 0.00. Now that is technically true, because the Index is Clustered with the Data, and thus DPCR is meaningful; IPCR does not apply, and zero is sort of a reasonable return. But I would prefer if it were consistent and returned NULL.

Likewise, for DataRowClusterRatio on a CI, it always returns 100.00, which is technically true but quite meaningless, because when the Index is Clustered with the Data, DataRowClusterRatio will always be 100.00, pretty much by definition. DRCR applies only to NCIs. I would prefer that it returned NULL.

Now for DPL/DRL tables which cannot have Clustered Index, if you address the CI (IndexId = 1) it correctly produces NULL. But if you address any of the NCIs (including the Placement Index), it produces values:
    DPCR: quite meaningless, and confusing, because the Data is not clustered with the addressed index; the Heap is relevant
    IPCR: correct, because that is the path that any NonClustered Index would take to the Data
    DRCR: correct
    LGIO: correct, but note, that is the index; the Heap is more relevant
    SPUT: zero, as it does not apply (I would prefer NULL)

The DPCR for an NCI (including PI) is very confusing, particularly because it is sometimes meaningful: when you have created a fresh Placement Index, it will be close to 1.0; but that will very quickly get fragmented and deteriorate from the desired 1.0. Whereas with a Clustered Index (for which the DPCR figure is meaningful all the time), it deteriorates very slowly, two years on a well-designed CI. Second, there is nothing you can do to slow that fast slide of a NCI from 1.0 to 0.0, as you can do with a CI. SO that best way to view DPCR for NCI or PI is, that it is coincidental with the REORG and quite meaningless at all other times.

I would prefer if DERIVED_STAT() were consistent, and returned NULL for values that did not apply, rather than sometimes returning NULL; other times 0.00 or 100.00 (which are ignored), yet other times good-looking values that simply do not apply. Then these explanations would not be necessary.

For DPL/DRL tables, if you are concerned about fragmentation (your question is about REORG) you really want to look at the DPCR of the Heap (IndexId = 0), not the NCI (including PI), and the IPCR of the PI.

Now your specific "0.5" ratio for rebuilds. To be clear, we are discussing the DPCR of the Heap of DPL/DRL tables. Actually, by the time it gets that low, the table will be a tortoise. You need to rebuild when it gets to 0.9 for high speed, and and absolute bottom of 0.80 for reasonable speed.


The following summarises my points above, and identifies the values that are relevant, that you should act upon, the other values should be ignored in order to avoid confusion:
    Heap  yes               yes   yes
    Clst  yes               yes   yes
    NCI         yes   yes   yes         -- including PI

I have included a butchered list from our sprc, which includes the issues described above, in the hope that it clarifies my post. It lists DataStructures along with fragmentation stats which are relevant to the DataStructure type.

Attached File(s)
.pdf  HelpDataStructFrag.pdf (Size: 59.4 KB / Downloads: 96)

Ashirvad to my Shishyas, Cheers to the others
Derek Asirvadem
Information Architect / Sr Sybase DBA
Selection of Useful Documents for the Sybase DBA
Visit this user's website Find all posts by this user
Post Reply 

Possibly Related Threads...
Thread: Author Replies: Views: Last Post
  Sybase SQL script to find out user database data and log size , data free , log free Joshi 4 14,953 11-19-2014 07:28 AM
Last Post: rajeshneemkar
  How to get list of modified database objects (table) in sybase bdvn_sudheer 7 3,609 07-19-2013 09:03 AM
Last Post: pinku.sybase
Smile Dataserver Health Checkout/QA Script sybanva 22 14,251 06-15-2013 09:07 AM
Last Post: dpssmita11
  Finding Unused Objects in a server using Monitoring Tables breddy 1 2,260 04-02-2013 02:22 AM
Last Post: breddy
  SQL to display table size in database - all tables and the sizes amolkadu 2 7,594 11-13-2012 06:28 PM
Last Post: amol
  Process to manage transaction log using automated script Huseni Kathiria 1 3,654 12-24-2010 03:53 AM
Last Post: DerekAsirvadem
  Script to report non logged or minimally logged operation in sybase Kishor 2 6,305 08-04-2010 12:51 AM
Last Post: chandrashekhar
  Script to check whether a sybase database is offline or online Kishor 4 9,785 05-07-2010 06:49 AM
Last Post: Joshi
  Errorlog Monitoring Script for NT env in Perl sybanva 0 13,248 04-20-2010 07:28 AM
Last Post: sybanva
  Sybase Database maintenance script crazYoga 0 6,135 02-02-2010 09:29 AM
Last Post: crazYoga

Forum Jump:

Contact Us | SybaseTeam | Disclaimer & Rules | Return to Top | Return to Content | Lite (Archive) Mode | RSS Syndication