Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

UPDATE 2015-12-18: As metioned by Bryn Llewellyn in this tweet, the invalidation I describe below is caused by bug 19450314. A patch for it is available here for several releases (e.g. it is available for 12.1.0.1.0, 12.1.0.2.1 or 12.1.0.2.13, but not for 12.1.0.2.0). I successfully tested it on 12.1.0.1.0.

UPDATE 2016-11-08: The patch related to bug 19450314 fixes the issue in case of ADD column. What it doesn’t fix are similar issues in case of MODIFY, RENAME and DROP column. Have a look to bug 21418655 (MORE INVALIDATIONS WORK IN 12C) in MOS for additional information.

The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g. In my opinion, since such an invalidation takes place only when a developer or DBA triggers it, I do not consider it a major problem.

What is new in 12c is that a SQL plan directive can instruct DBMS_STATS to create an extension without the intervention of a developer or DBA. In other words, automatically and almost at any time (depending on how you gather the object statistics). Since such an operation can invalidate packages that depend on the altered table, in some situations that might cause problems to systems that must be available 24×7. Hence, you have to be aware of it and, if necessary, make sure that you are not impacted by it.

Independently on how an extension is created, someone might ask why invalidations can take place. In fact, because of fine-grained dependencies, adding a hidden virtual column to a table should not require an invalidation of the objects that depend on the table. However, in practice, this is not always the case. (Honestly, I do not know whether is an expected behavior or a bug.) An example is provided by the following package:

CREATE OR REPLACE PACKAGE p1 AS
 PROCEDURE p;
END p1;
/
 
CREATE OR REPLACE PACKAGE BODY p1 AS
 PROCEDURE p IS
   c NUMBER;
 BEGIN
   SELECT count(1) INTO c 
   FROM t;
 END p;
END p1;
/

Since the package uses a “count(1)”, when an extension is created on the table T, the package body is invalidated. Note that using a “count(1)” instead of a “count(*)” is an old trick that is presently useless for improving the performance! In fact, the optimizer has a transformation that rewrites the “count(1)” into a “count(*)”. And, in this particular case, using the “count(1)” makes things worse from an availability point-of-view.

If you are interested in experimenting with such a package with either a user-defined extension or an extension created through a SQL plan directive, here are two scripts:

• extension_invalidate_pkg.sql
• spd_invalidate_pkg.sql