I am not sure I understand your question or the scenario that
you are trying to understand but I can provide some
The relevant dynamic SQL entries in the package cache will be
affected when you execute runstats (or update statistics manually)
so that the next new request will automatically force a
compilation. Similarly if you create an index for a table etc.
- I issue "SELECT * FROM T1"; this causes an entry for this
statement to be compiled and put into the package cache.
- I execute the exact same statement again; there is no
compilation this time and I would end up using the cached
- You then issue RUNSTATS on T1; part of the runstats logic will
"invalidate" (i.e. mark it as no longer available for reuse) any
cached dynamic SQL statement which is dependent on T1. This
includes the cached "SELECT * FROM T1"
- I issue the same statement a third time; this time a new
compilation occurs and a new entry is put into the package
There are a few caveats and other factors that can affect the
above but the basic philosophy is that the package cache will
automatically keep dynamic SQL entries synchronized with the
effects of statistics and DDL updates so that new requests for them
always reflect the current environment.
FYI, the reason that FLUSH PACKAGE CACHE is not recommended for
production systems is that it will invalidate all the cached
dynamic SQL entries at once causing a massive surge in compilations
which will hit resources and affect throughput.
Hope this helps,