Archives of the TeradataForum
Message Posted: Tue, 06 Sep 2011 @ 16:03:01 GMT
| Subj: | | Re: Stats Refresh Post Expansion |
| |
| From: | | Joe Gleason |
For easy reference, I copies this out of a separate thread addressing some of the same questions. This is from Todd Walter, I believe CIO or
CTO at Teradata:
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...
| 
