You can remove an optimization profile that is no longer
needed by deleting it from the SYSTOOLS.OPT_PROFILE table. When an
optimization profile is referenced, it is compiled and cached in memory;
therefore, if the original profile has already been used, you must
also flush the deleted optimization profile from the optimization
profile cache.
Procedure
To delete an optimization profile:
- Delete the optimization profile from the SYSTOOLS.OPT_PROFILE
table. For example:
delete from systools.opt_profile
where schema = 'NEWTON' and name = 'INVENTDB'
- If you did not create triggers to flush the optimization profile cache,
issue the FLUSH OPTIMIZATION PROFILE CACHE statement to remove any
versions of the optimization profile that might be contained in the
optimization profile cache.
Note: When
you flush the optimization profile cache, any dynamic statements that
were prepared with the old optimization profile are also invalidated
in the dynamic plan cache.
Results
Any subsequent reference to the optimization profile causes
the optimizer to return SQL0437W with reason code 13.