Archives of the TeradataForum
Message Posted: Fri, 10 Mar 2006 @ 17:21:44 GMT
| Subj: | | Re: Adding Nodes to a System: Re-Collection of Stats necessary? |
| |
| From: | | Walter, Todd A |
A reconfig to add AMPs/nodes/disks to the system changes a number of optimizer calculations. Many of the cost functions have both total system
cost and per AMP cost components. The total system cost might be the same but the per AMP cost changes because of the change of configuration. Or
the opposite can be true. A simple example is table duplication - the larger the system becomes, the greater the system cost of a table
duplication operation since it has to be done on all AMPs.
Because of these costing changes, plans change after an upgrade. Most of these plan changes are good and right, but ocassionally they result in
a less performant plan. When we drill down into these cases, ones that are not optimiser problems are frequently caused by stale stats. There were
two or more plans available to the optimizer and their costs were very close. Because of the change in configuration the costs of each change but
do so such that one of the other plans now has the slightly lower cost. This is how issues with stale/missing stats get magnified across a
configuration change. They also all show up at the same time because of the one big change to the config where normally they would have crept in
one at a time as the stats got stale enough.
There is nothing that technically requires a recollect of stats after a reconfig. Our experience however is that there frequently are stale or
missing stats out there and that those issues cause plan issues to all show up at the same time after the new configuration is put into operation.
This leads us to a best practice recommendation to recollect after reconfig so neither you or us has to fight that set of problems. If you are
certain you are current...
| 
