Tuesday, November 16, 2010

DIAGNOSTIC HELPSTATS

One of my favorite commands and most useful among the lot , is Diagnostic help stats .
This command is very useful in helping user understand which all columns should have collect
stats be collected on, so optimizer can select the best plan.

  To Include the Stats collection recommendations in the explain plan.

DIAGNOSTIC HELPSTATS ON FOR SESSION;

At the end of the explain text is the recommended statistics for collection will be as follows

/*BEGIN RECOMMENDED STATS ->
 16) "COLLECT STATISTICS ADW.PRODUCT COLUMN P_SIZE".  (HighConf)
 17) "COLLECT STATISTICS ADW.PRODUCT COLUMN P_CODE".  (HighConf)
 18) "COLLECT STATISTICS ADW.PRODUCT COLUMN P_DESC".  (HighConf) */
If you want explain to stop showing recommendations for collection of stats, then use the following

DIAGNOSTIC HELPSTATS NOT ON FOR SESSION;

Diagnostic help stats has some drawbacks like

  • It does not give any sort of indication of stale stats
  • Stats should be chosen carefully as recommended by diagnostic help stats
  • Care should be taken to see that too many stats on a given table can impact batch running of scripts and increases the overload of stats maintenance.
  • If recommended stats don’t show any improvements in performance, DROP them!

No comments:

Post a Comment