Synchronous statistics collections

24 January,2011 by Jack Vamvas

An interesting feature of the RUNSTATS facility – as well as collecting  statistics asynchronously (in the background)  , it can also set a synchronous collection – at statement compilation time

 If the real time statistics is enabled – the optimizer makes the decision. Real statistics collection doesn’t preclude  the background collection – which still happens in the background. The decision to enable real time collection should  be based on application requirements. For example , you probably wouldn’t turn on real time statistics , if there are rarely any UPDATE,DELETE,INSERT statements

 A number of measures are in place to throttle the impact , such as : RUNSTATS is throttled , one synchronous stats collection per table, a table has to have no statistics or high levels of UPDATE,DELETE,INSERT to be considered for collection., in V9.5 a statistics cache is made available to all statements.

 It is useful to monitor the $DIAGPATH/events db2optstats.number.log  file which is a rotating log of all statistics activity on the database

 Ref:Jack Vamvas(http://www.dba-db2.com)

 

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Synchronous statistics collections

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright