Mike Matrigali wrote:
Lots of good stuff...
4) Need to make sure validation of these plans work. This was an area
that caused a number of corrupt databases in cloudscape. The basic
problem was that we would use a stored plan incorrectly when it should
have been invalidated because some sort of ddl event happened since the
original compile. Easiest way for this to lead to a corrupt db is that
an index was added, and the old compiled plan did not know about it and
thus when an insert happened the new index would not get an entry
leading to a mismatch between new index and table. This is not an
issue for system stored plans that get invalidated every release. There
must be a mechanism for invalidated the trigger ones, but I don't know
if existing invalidation will work in all cases for non trigger use.
I think this is a tricky bit which will require a lot of testing. Much
of the dependency tracking happens purely in memory on the assumption
that when you bounce the database the statements will be recompiled and
the in-memory validation logic will be rebuilt. Approach this cautiously
since there are a lot of unstated assumptions in the validation logic.
I think that the current discussion calls for recompiling persistent
plans at upgrade time--this includes soft-upgrade time. This is a good
way to limit our exposure. Sybase wasted a lot of effort trying to
guarantee that persisted plans would run on future releases--in my
opinion, the logic was very complicated and unreliable.
Regards,
-Rick
