When i run explain for collect stats on a column i find a statement as below
1) First, we lock DB1.T1 for access. 2) Next, we lock DBC.TVFields for write on a RowHash. 3) We do a COLLECT STATISTICS step from DB1.T1 by way of an all-rows scan into Spool 3 (Last Use), which is built locally on the AMPs. 4) We do an UPDATE STATISTICS step from DBC.TVFields by way of the primary index. 5) We spoil the p****r's dictionary cache for the table. 6) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> No rows are returned to the user as the result of statement1.
I would like to know the meaning of line no 5 (which has spoil word).
That means that any plans that access that table that are in the plan cache will be flushed from cache. That's done so that the plan can be re-generated using the new statistics, which may change the plan.
That is nice to hear. But I have never heard of the term "Plan cache". Is it like the optimizers plan is saved in a location called plan cache for future run's of the same query? If it is the case only after the query is run after spoil the new plan will get generated. If am wrong pls explain the same.
There is a plan cache (usually called the "request cache"), but "spoil" is an operation on the dictionary cache, though it may have a secondary effect on the request cache as well. Spoiling insures that obsolete items are removed from the cache on every PE, so that the correct version will be brought into the cache the next time that item is referenced.