Skip to Content
0

Sybase ASE maintenance - update stats or reorg rebuild

May 18, 2017 at 03:17 PM

464

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Mark A Parsons May 18, 2017 at 04:32 PM
0

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).

Share
10 |10000 characters needed characters left characters exceeded