RUNSTATS and REORG-2: How to automate runstats

Hi guys!!! I am back... n back as an IBM Information Champion (yup got selected for 2011 :) ) !!!
Please rate this blog as informative/interesting/cool/all/two of these once you are done... Any comments on improvement are appreciated and will be welcomed...

Well in last post I talked about importance of runstats and reorg for a db2 database...

just a recap, reorg keeps the data pages near by for same table hence reducing disk seek n runstats will keep the stats updated so helping the optimizer make best use of available indexes and generate better plan...

well... automating runstats is pretty simpler... I will take you little deep into this automation n its impact...
Following are the automatic maintainence parameters in db2...
__________________________________________________________________
Automatic maintenance (AUTO_MAINT)
|
|-- Automatic database backup (AUTO_DB_BACKUP)
|
|-- Automatic table maintenance (AUTO_TBL_MAINT)
|
|---- Automatic runstats (AUTO_RUNSTATS)
|
|-------- Automatic statement statistics (AUTO_STMT_STATS)
|
|---- Automatic statistics profiling (AUTO_STATS_PROF)
|
|--------- Automatic profile updates (AUTO_PROF_UPD)
|
|---- Automatic reorganization (AUTO_REORG)
__________________________________________________________________
So it must be clear that above is an hierarchy... To set ON a child you need to set ON the parent first...
Now lets understand the parameters we are concerned about...

1) First one is AUTO_RUNSTATS
If you set it effective (not just ON... guess what i mean by this???), it will automatically keep doing runstats on tables...
And do not worry, this will not impact your other db2 processes by more that 7-10% (no matter what you have set as UTIL_IMPACT_PRIORITY)... and it will not keep doing this every time you do a CRUD operation... It will do a runstats only on those tables whose data has changed more than 10-15%...good news is db2 luw v9.7 will switch it on automatically by default for any new db created...

2) Next will be automatic statement statistics
If you set this ON, it will try to collect the stats synchronously for any CRUD operation you do... In fact at times it may cause your CRUD operation to wait... It is a real time stats gather...
Well here are a few SNAP DB monitor elements, which might help you decide best about these parameters...
i) STATS_FABRICATIONS Vs ASYNC_RUNSTATS+SYNC_RUNSTATS is STATS usage TO STATS GATHER ratio,
ii) SYNC_RUNSTATS vs SYNC_RUNSTATS+ASYNC_RUNSTATS is FRACTION of SYNC RUNSTAT
iii) STATS_FABRICATE_TIME Vs SYNC_RUNSTATS_TIME is usage stats compared to SYNC update of STATS
iv) SYNC_RUNSTATS_TIME Vs ELAPSED_EXEC_TIME_S & ELAPSED_EXEC_TIME_MS will give you impact of SYNC STAT COLLECTION ON STMT execution
BY default his parameter is ON... Well I had used the monitor elements above and was surprised that 22% of txn were getting delayed due to synchronous stats updates... 30% of stats were being updated synchronously...I leave it to you to decide what to do when you run into this...

3) Third is Automatic statistics profiling and Automatic profile updates
Switching on the automatic stats profiling, will turn on stats profile generation...
but you can not set it on if your db cfg section_actual is ON...
Would like to make a point that generally auto_runstats will respect the user profile if user has run a manual runstats with some profile...
Automatic Profile update will update the runstats profile with recommendations...
If it is off profile recommendations generated are stored in opt_feedback_ranking...
Both of these are off by default...
Will suggest keeping them off to let auto_runstats pick up the default profile... or run a manual runstats with a specific profile for a table n auto_runstats will keep it in mind...

One more way of statistics gathering, is to do a run-stats in idle time, with a background scheduled job... A few months ago, I would have preferred that, when my seniors explained me that...

"db2 does it automatically n won't impact the current processes by much fraction... More over if you automate it urself, you will update stats for all the tables, even if they did not change much (or not at all) so its better we leave it to db2..."

So this is all about automating the runstats...
I will get back on auto maint parameters for automatic reorg and how to identify when to do a reorg...

For people interested in exploring more...
--> Go thru this info center page for more content on auto_maint parameters...
--> Go thru this article on dev works... It explains the automatic maintainence parameters for statistics gathering quit well...

RUNSTATS and REORG-1: How important they are

Hi guys...

So I am back... little quick this time...
So this is final post in my pseudo participation blog for DB2 Night Show (I had an early exit :-( after qualifying for finals)...
Congrats to Norberto for winning the competitions... Check his blog for tips...
Congrats to JB, Momi and Thiru as well for making to final...
I am looking forward to more such opportunities in future...

So I have come with some tips for people (people as the DBA community) to understand how important is the usage of two DB2 commands RUNSTATS and REORG, more importantly how you can determine when to REORG/RUNSTATS on which tables...

1) RUNSTATS are important for your tables, as they keep the stats updated for your table. This will always help your optimizer generate a better plan, rather the apt plan for fetching the data. I have seen situation when a filtered fetch goes for a table scan for 0.1-1million records even though column in filter is indexed...

2) REORG is equally important as it helps in putting the data in continous chunks... Generally for better insert performance, the data is put where you have space rather than putting it in a place (or near it) where other data of the table... The placement of data is also dependent on availability of space in nearest extent/block... If your data is too scattered across, there might be too much of disk seek involved and might as well degrade the performance of sequential and concurrent fetch...

Well you can AUTOMATE your task to certain extent and use automatic maintainance database configuration for auto runstats and auto reorg...

I shall come up with more on pros and cons of using those parameters and ways to optimize your RUNSTATS and REORG, in my future blogs...

I would like to thank Susan and Sathey for their continuous support, encouragement and tips for improvement on by blogging...

I will keep my blogs little short and will split the massive overdose of info over several blogs (thanks to Sathey)...

Latter I will also try to split my previous long posts into several blogs, which shall help my new subscriber...