Skip to Content

Sybase ASE maintenance - update stats or reorg rebuild

Hello ,

I am new to Sybase (previous oracle dba experience).

I have inherited, some maintenance scripts, which take hours to run = 12 hours (85GB database), I have looked at the scripts and there appears to be duplication in what they are doing
ie
script 1
update index statistics acct_class
update index statistics acct_class_type

script 2
reorg rebuild acct_class acct_class_prim
reorg rebuild acct_class acct_class_sec
reorg rebuild acct_class acct_class_slct
reorg rebuild acct_class_type acct_class_type_prim
reorg rebuild acct_class_type acct_class_type_sec
reorg rebuild acct_class_type acct_class_type_slct

my question is .... if I am (reading the manuals correctly), If I 'reorg rebuild' a table and its index, then an update statistics is not required

is this correct?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 18, 2017 at 04:32 PM

    From the ASE 15.7 Reference Manual: Commands manuals:

    ‚óŹ Running reorg rebuild <table_name> updates the statistics for all leading index columns. However, running reorg rebuild <table_name> <index_name> does not automatically update the statistics. Instead, the SAP ASE server automatically updates index statistics when you run reorg rebuild <index_name> if the update includes a sufficient change in data to affect its plan choice and performance.
    

    Key take-aways:

    1 - reorg rebuild <table> : updates stats for *leading* index columns (ie, does not update stats for non-leading index columns)

    2 - reorg rebuild <table> <index> : may or may not update stats

    --------------------

    In your situation:

    - script 2 (as posted) doesn't show any reorg rebuilds at just the table level

    - script 2 (as posted) only shows reorg rebuilds at index levels, so some stats may or may not get updated

    - script 1 (as posted) show update index statistics commands being run at the table level; these commands will ensure all column stats are updated for all indexes for a given table

    Based solely on what you've posted, I don't see anything that I'd consider redundant.

    What I would suggest is that you run script2 first, followed by running script1; this should ensure that stats are as up-to-date as possible.

    Since you're a Sybase newbie I'd also recommend you consider running optdiag (Sybase-supplied, OS-level utility) before and after the various reorg rebuild and update index statistics commands, with the objective being to get a feel for what table/index/column stats are affected by the various commands.

    --------------------

    As for your maintenance activities taking 12 hours for a 85GB database ... which, without a lot more details, seems a bit excessive ...

    - I'd want to review maintenance logs to see which reorg rebuild / update index statistics commands are taking the longest; objective being to concentrate initial P&T efforts on the commands that'll provide the biggest bang for the buck

    - the reorg rebuild and update index statistics commands are disk intensive so I'd want to take a look at your disk subsystem to see if you're dealing with any 'slow' disks

    - the reorg rebuild and update index statistics commands will also make heavy use of your ASE caches, so monitoring of the caches for any excessive thrashing may be of benefit

    - update index statistcs (out of the box) may use a lot of tempdb space, so some tempdb tuning may be appropriate; with newer versions of ASE (eg, 15.7 SP13X, 16.0 SP02 PL0[34]) you may want to look at having the update index statistics commands use in-memory hashing to eliminate any bottlenecks with tempdbs (see the update index statistics / with hashing option)

    - if you have multiple dataserver engines then you may want to look at the feasibility of running some of your maintenance commands in parallel (eg, running reorg rebuilds against different tables/indexes via different isql sessions); obviously (?) this assumes such an operation can be supported by your cache/memory configurations and/or disk subsystem (ie, if you've already got a bottleneck then running more commands at the same time will likely make the bottleneck worse ... "Duh, Mark!" ?)

    There are a lot of possible P&T steps to consider ... way too many to try to address in this Q&A forum ... so best bet would be to find a couple of the bigger time consumers and try to drill down on where there may be a bottleneck, then determine the best way to address said bottleneck(s).

    Add comment
    10|10000 characters needed characters exceeded