+ Reply to Thread
Results 1 to 10 of 10

What should I use from optdiag to run reorg

  1. What should I use from optdiag to run reorg

    Hi folks
    I was looking at the manual and could not get a distinct
    answer on how exactly I should use optdiag to run or not to
    rung reorg on dol tables.
    I have my own idea but I would like to know your opinion.
    Next are fragments are from manual:

    Data page cluster ratio: 0.0075819672131148
    Index page cluster ratio: 1.0000000000000000
    Data row cluster ratio: 0.0026634382566586

    Data page cluster ratio: 0.9896907216494846
    Forwarded row count: 18.0000000000000000

    So I would look only at Data page cluster ratio to be < .9
    I would use forwarded row count as not less than 5%

    How would I use data page and data row cluster ratios for
    the reason to determine if I need to run reorg?
    Do I have to look for something else?

    Thank you.

  2. Re: What should I use from optdiag to run reorg

    If you intend to use Reorg, which options of Reorg would you use in the
    above case?

    regards
    Ash


  3. Re: What should I use from optdiag to run reorg

    I see it from different angle:
    I want to maintain dol tables. I do not want arbitrarily to run reorg
    rebuilt for every table every day. Instead, I want to run optdiag may
    be every day.
    And than, based on finding (which readings should I look for
    specificly?) I want to run reorg.

    Don't you think it's more logical to run rebuilt when need arise?

    thank you, Gene.


    TITU19@gmail.com wrote:
    > If you intend to use Reorg, which options of Reorg would you use in the
    > above case?
    >
    > regards
    > Ash



  4. Re: What should I use from optdiag to run reorg

    good you know reorg only works on DOL tables...

    as for cluster ratios, depends if you are looking at
    clustered indices or non-clustered indices. For NC ones,
    data row cluster ratio doesn't make much sense. but for
    Clustered ones, it tells the real cluster ratio that you
    want to do the <.9 comparison (rebuild if it is < .9)

    I use index cluster ratio for NC indices. data page cluster
    ratio tells how well the pages themselves are clustered
    which is interesting if you use larger i/o sizes (such as
    the 16k pool!)

    hope this makes sense and answers your questions.
    Meg


    > Hi folks
    > I was looking at the manual and could not get a distinct
    > answer on how exactly I should use optdiag to run or not
    > to rung reorg on dol tables.
    > I have my own idea but I would like to know your opinion.
    > Next are fragments are from manual:
    >
    > Data page cluster ratio: 0.0075819672131148
    > Index page cluster ratio: 1.0000000000000000
    > Data row cluster ratio: 0.0026634382566586
    >
    > Data page cluster ratio: 0.9896907216494846
    > Forwarded row count: 18.0000000000000000
    >
    > So I would look only at Data page cluster ratio to be < .9
    > I would use forwarded row count as not less than 5%
    >
    > How would I use data page and data row cluster ratios for
    > the reason to determine if I need to run reorg?
    > Do I have to look for something else?
    >
    > Thank you.


  5. Re: What should I use from optdiag to run reorg

    Meg, thank you for answer.

    from you answer:
    I use index cluster ratio for NC indices. data page cluster
    ratio tells how well the pages themselves are clustered
    which is interesting if you use larger i/o sizes (such as
    the 16k pool!)

    I guess you are referring to index statistics data page
    cluster:
    Derived statistics:
    Data page cluster ratio: 0.0075819672131148
    Index page cluster ratio: 1.0000000000000000
    Data row cluster ratio: 0.0026634382566586

    So what is that exactly it tells you? Could any readings
    from the above serve as a signal to run any jobs or use any
    caches?

    If sybase took an effort to create reog utility and then
    optdiag. Would it be worth an effort to exactly describe how
    to use it and meaning of the parameters it produces?
    What I see instead: A counter used to compute the data page
    cluster r.atio for accessing a table using the index.

    Gene.


    > good you know reorg only works on DOL tables...
    >
    > as for cluster ratios, depends if you are looking at
    > clustered indices or non-clustered indices. For NC ones,
    > data row cluster ratio doesn't make much sense. but for
    > Clustered ones, it tells the real cluster ratio that you
    > want to do the <.9 comparison (rebuild if it is < .9)
    >
    > I use index cluster ratio for NC indices. data page
    > cluster ratio tells how well the pages themselves are
    > clustered which is interesting if you use larger i/o sizes
    > (such as the 16k pool!)
    >
    > hope this makes sense and answers your questions.
    > Meg
    >
    >
    > > Hi folks
    > > I was looking at the manual and could not get a distinct
    > > answer on how exactly I should use optdiag to run or not
    > > to rung reorg on dol tables.
    > > I have my own idea but I would like to know your
    > > opinion. Next are fragments are from manual:
    > >
    > > Data page cluster ratio: 0.0075819672131148
    > > Index page cluster ratio: 1.0000000000000000
    > > Data row cluster ratio: 0.0026634382566586
    > >
    > > Data page cluster ratio: 0.9896907216494846
    > > Forwarded row count: 18.0000000000000000
    > >
    > > So I would look only at Data page cluster ratio to be <
    > > .9 I would use forwarded row count as not less than 5%
    > >
    > > How would I use data page and data row cluster ratios
    > > for the reason to determine if I need to run reorg?
    > > Do I have to look for something else?
    > >
    > > Thank you.


  6. Re: What should I use from optdiag to run reorg


    wrote in message news:4443fde5.754a.1681692777@sybase.com...
    > Hi folks
    > I was looking at the manual and could not get a distinct
    > answer on how exactly I should use optdiag to run or not to
    > rung reorg on dol tables.
    > I have my own idea but I would like to know your opinion.
    > Next are fragments are from manual:
    >
    > Data page cluster ratio: 0.0075819672131148
    > Index page cluster ratio: 1.0000000000000000
    > Data row cluster ratio: 0.0026634382566586
    >
    > Data page cluster ratio: 0.9896907216494846
    > Forwarded row count: 18.0000000000000000
    >
    > So I would look only at Data page cluster ratio to be < .9
    > I would use forwarded row count as not less than 5%
    >
    > How would I use data page and data row cluster ratios for
    > the reason to determine if I need to run reorg?
    > Do I have to look for something else?
    >
    > Thank you.


    A suggested criteria is:
    ========================================================
    Mich Talebzadeh
    Posted: Apr 05, 2006 - 07:17 PM

    Depends on the type of table and index, derived_stats parameters can guide
    you.

    'table type ==> DRL or DPL and index type ==> clustered, watch =====> dpcr&
    drcr'

    'table_type = DRL or DPL and index_type = non-clustered, watch ====> ipcr&
    sput'

    'table_type = APL and index_type = clustered, watch ====> lgio and sput'

    'table_type = APL and index_type = non-clustered watch ====> lgio'


    if any value relevant (as above) goes below < 0.85, you should think of
    reorg rebuild or rebuild the index
    ========================================================

    Offhand, what bothers me is that there are really only two kinds of indexes:
    APL clustered and "any other index". So, why are there three different
    inicators for the non-APL clustered situation?

    Another question is, if I have the maintenance window available, is there
    additional benefit in drop/create index versus reorg? If so, should
    different indicators be evaluated for drop/create index versus reorg?





  7. Re: What should I use from optdiag to run reorg

    I've seen those "recommendations" several times as well, and I don't necessarily
    buy into them at all. I've never seen them explained, just posted. There are
    several types of fragmentation, and as you state, the difference between a DOL
    non-clustered index and APL index don't make a whole lot of sense. Not to
    mention that there is no mention of forwarded/deleted row counts for DOL tables
    here. The other red flag is going specifically on "lgio" for APL non-clustered
    indexes, where the "lgio" number for objects which are bound to a cache that
    doesn't have a large io pool will always be "1.0".

    Second, you raise the question of the benefit of using drop/create vs reorg, so
    I'm assuming we are only applying this question to DOL tables. On that
    assumption, the differences between the two that jump out at me are:

    1. If you don't have a placement (clustered) index on your DOL table, or you do
    not drop and recreate your clustered DOL index, then you don't get the benefits
    of rebuilding the heap and therefore don't get your forwarded rows relocated, or
    your unused space compacted. As such, you are only rebuiding your indexes, and
    not your DOL heap (and therefore, this would run faster than reorg rebuild)

    2. drop/create is not resumable like "reorg with [resume | time = ]

    3. There may be some locking differences i'm not sure right now. Look up the
    locking protocols for these commands.

    4. reorg rebuild is an all-or-nothing deal. If there is a failure, you are
    left with the table and indexes in the previous state. I find that drop/create
    allows me more flexibility in terms of getting at least the most "fragmented"
    indexes rebuilt, if for some reason I can't rebuild the others.

    5. This is a great question and I'm sure others here have more to add to this
    list based on their experiences.


    "Carl Kayser" wrote in message
    news:44464c4a$1@forums-2-dub...
    > ...
    > A suggested criteria is:
    > ========================================================
    > Mich Talebzadeh
    > Posted: Apr 05, 2006 - 07:17 PM
    >
    > Depends on the type of table and index, derived_stats parameters can guide
    > you.
    >
    > 'table type ==> DRL or DPL and index type ==> clustered, watch =====> dpcr&
    > drcr'
    >
    > 'table_type = DRL or DPL and index_type = non-clustered, watch ====> ipcr&
    > sput'
    >
    > 'table_type = APL and index_type = clustered, watch ====> lgio and sput'
    >
    > 'table_type = APL and index_type = non-clustered watch ====> lgio'
    >
    >
    > if any value relevant (as above) goes below < 0.85, you should think of
    > reorg rebuild or rebuild the index
    > ========================================================
    >
    > Offhand, what bothers me is that there are really only two kinds of indexes:
    > APL clustered and "any other index". So, why are there three different
    > inicators for the non-APL clustered situation?
    >
    > Another question is, if I have the maintenance window available, is there
    > additional benefit in drop/create index versus reorg? If so, should
    > different indicators be evaluated for drop/create index versus reorg?
    >
    >
    >
    >




  8. Re: What should I use from optdiag to run reorg

    > I've seen those "recommendations" several times as well,
    > and I don't necessarily buy into them at all. I've never
    > seen them explained, just posted.


    I use the following rule of thumb:

    For DOL tables:

    If the SPUT < 0.75 and the DPCR < 0.95
    or if forwarded or deleted row ratio > 0.001
    then reorg compact of the table.

    For DOL table indexes:

    If the SPUT < 0.75 and the IPCR < 0.95
    then reorg rebuild of the index.

    The forwarded or deleted row ratio is just the
    forwarded/deleted row
    count / table row count.

    I do reorg compact because my main servers have one table
    that uses up about 1/3
    of the database, and leaving that much empty space is
    annoying (especially as
    the prod. database is copied multiple times to test and
    development environments)

    It's probably not a perfect solution, but it appears to work
    reasonably well here.

    Michael

  9. Re: What should I use from optdiag to run reorg

    > > I've seen those "recommendations" several times as well,
    > > and I don't necessarily buy into them at all. I've
    > > never seen them explained, just posted.

    >
    > I use the following rule of thumb:
    >
    > For DOL tables:
    >
    > If the SPUT < 0.75 and the DPCR < 0.95
    > or if forwarded or deleted row ratio > 0.001
    > then reorg compact of the table.
    >
    > For DOL table indexes:
    >
    > If the SPUT < 0.75 and the IPCR < 0.95
    > then reorg rebuild of the index.
    >
    > The forwarded or deleted row ratio is just the
    > forwarded/deleted row
    > count / table row count.
    >
    > I do reorg compact because my main servers have one table
    > that uses up about 1/3
    > of the database, and leaving that much empty space is
    > annoying (especially as
    > the prod. database is copied multiple times to test and
    > development environments)
    >
    > It's probably not a perfect solution, but it appears to
    > work reasonably well here.
    >
    > Michael


    In my experience, rebuilding indexes on scheduled basis may
    do more harm than good in most of the cases. An index
    usually has its “natural” shape that it tends to be in
    unless the way that applications use the index doesn’t
    change. If we rebuild an index (with the default
    fillfactor), we may see dramatic drop in its size (by factor
    of 5, for example, or even more). But, in the course of time
    (usually rather quickly), the index will grow back to its
    “natural” size and lots of additional index page splits
    may occur to support this growth. As result of these index
    page splits, performance of DML operations updating the
    index and also overall dataserver performance will be
    affected. Also, “reorg rebuild index” is quite a
    resource-costly operation by itself.

    We can rebuild an index with lower fillfactor in order to
    try to eliminate the overhead of additional index page
    splits resulted from the rebuild. I tried a number of times
    to reduce the index height by this way (for example, the
    index height after rebuild with default fillfactor is 4 and
    the “natural” index height is 7) – without success.

    Of course, it all depends. For example, if we replicate from
    OLTP environment to a report system, performance of reports
    in the reports system is a top priority and in this case
    rebuilding of indexes in the report system may be
    preferable, even if the replication performance would
    suffer. Performance of some reports can be improved
    dramatically if we rebuild a 20GB index into a 4GB one, for
    example.

    Generally, I would recommend considering “reorg rebuild
    index” only after some outstanding data change. In order
    to determine if we really need to rebuild an index, daily or
    weekly history of optdiag outputs can be helpful.

    As for difference between reorg rebuild index and
    drop/recreate index – we should always choose the first
    option. In addition to Kevin Sherlock’s arguments, I can
    say that dropping of an index (especially a large one) can
    lock the whole database and not only the table in question
    because ASE puts exclusive table locks on some important
    system tables (sysindexes, syscolumns). As result, the
    database will be effectively locked until “drop index”
    is finished. It can take tens of minutes for large indexes
    on a loaded production system while it is usually not
    possible to stop the operation quickly and release the
    locks. I understand that such behaviour of “drop index”
    is going to be changed in ASE 15, but I have never tested it
    myself.

  10. Re: What should I use from optdiag to run reorg

    mpeppler@peppler.org wrote:

    >> I've seen those "recommendations" several times as well,
    >> and I don't necessarily buy into them at all. I've never
    >> seen them explained, just posted.

    >
    > I use the following rule of thumb:
    >
    > For DOL tables:
    >
    > If the SPUT < 0.75 and the DPCR < 0.95
    > or if forwarded or deleted row ratio > 0.001
    > then reorg compact of the table.
    >
    > For DOL table indexes:
    >
    > If the SPUT < 0.75 and the IPCR < 0.95
    > then reorg rebuild of the index.
    >


    I also do the same rule as Michael for DOL tables.
    I have a cron job that runs every day and determines which DOL table indexes
    have an IPCR < 0.98 and I then run a "reorg rebuild " on
    them.

    Furthermore, with regards to "reorg rebuild index" vs. "drop/create an
    index", I posted a particular experience I am having in s.p.a.a some months
    ago, but got no response:

    http://groups.google.com/group/sybas...8f5fb07d1c001d
    http://groups.google.com/group/sybas...b924748420e949

    In short, a DOL table that is essentially used as a "queue" ( high inserts
    and high deletes ) will have its non-clustered index size grow and grow and
    grow, despite the fact that the number of rows is almost always near 0.

    The only way I could complete reduce the size of the index is to drop and
    create it again. Fortunately, because it is small, it only takes a few
    seconds.