About Me

My photo
Singapore, Singapore, Singapore
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Connect to me

See my profile on LinkedIn Visit my Ashnik Website

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

6 comments:

  1. very informative. Thanks - Sathy

    ReplyDelete
  2. what is a a CRUD operation?

    ReplyDelete
  3. CRUD is CReate, Update or Delete of records... i.e. any insert/update/delete operation...

    ReplyDelete
  4. where do we find REORG time in Db2V8?

    ReplyDelete
  5. Well you can get that from DB_HISTORY


    SELECT TABNAME,OPERATION, START_TIME, ENTRY_STATUS,END_TIME,CMD_TEXT
    FROM SYSIBMADM.DB_HISTORY where operation='G'

    ReplyDelete
  6. If the Db hasnt been bounced since you last reorged ,
    db2pd -d DBNAME -reorgs Can be a desired resultant.

    ReplyDelete