Search

Top 60 Oracle Blogs

Recent comments

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.

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:

#993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">OR #993333; font-weight: bold; text-transform: uppercase;">REPLACE #993333; font-weight: bold; text-transform: uppercase;">PACKAGE p1 #993333; font-weight: bold; text-transform: uppercase;">AS
 #993333; font-weight: bold; text-transform: uppercase;">PROCEDURE p;
#993333; font-weight: bold; text-transform: uppercase;">END p1;
#66cc66;">/
 
#993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">OR #993333; font-weight: bold; text-transform: uppercase;">REPLACE #993333; font-weight: bold; text-transform: uppercase;">PACKAGE #993333; font-weight: bold; text-transform: uppercase;">BODY p1 #993333; font-weight: bold; text-transform: uppercase;">AS
 #993333; font-weight: bold; text-transform: uppercase;">PROCEDURE p #993333; font-weight: bold; text-transform: uppercase;">IS
   c #993333; font-weight: bold; text-transform: uppercase;">NUMBER;
 #993333; font-weight: bold; text-transform: uppercase;">BEGIN
   #993333; font-weight: bold; text-transform: uppercase;">SELECT count#66cc66;">(#cc66cc;">1#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">INTO c 
   #993333; font-weight: bold; text-transform: uppercase;">FROM t;
 #993333; font-weight: bold; text-transform: uppercase;">END p;
#993333; font-weight: bold; text-transform: uppercase;">END p1;
#66cc66;">/

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: