All Forums Database
TdMan 91 posts Joined 01/07
26 Jul 2007
What is meant by "SPOIL" in explain plan?

When i run explain for collect stats on a column i find a statement as below1) 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 ofan all-rows scan into Spool 3 (Last Use), which is builtlocally on the AMPs.4) We do an UPDATE STATISTICS step from DBC.TVFields by way ofthe 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 AMPsinvolved 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).Regards,Sakthi

Barry-1604 176 posts Joined 07/05
26 Jul 2007

Sakthi,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.Hope that helps.Regards,Barry

TdMan 91 posts Joined 01/07
26 Jul 2007

Hi Barry,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.Regards,Sakthi

Jim Chapman 449 posts Joined 09/04
27 Jul 2007

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.

TdMan 91 posts Joined 01/07
31 Jul 2007

ok Thank you Jim

You must sign in to leave a comment.