Search

Top 60 Oracle Blogs

Recent comments

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages – Take Two

In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love examples!) based on the extension_invalidate_pkg_remote.sql script, what can happen when the table on which the extension is created is stored in a remote database.

Let’s start by creating the required objects. Notice that all objects are local but, because the package body references the table through a database link that points to the very same schema owning all other objects, the database engine considers it a remote table.

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">DATABASE #993333; font-weight: bold; text-transform: uppercase;">LINK loopback
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">CONNECT #993333; font-weight: bold; text-transform: uppercase;">TO chris #993333; font-weight: bold; text-transform: uppercase;">IDENTIFIED #993333; font-weight: bold; text-transform: uppercase;">BY ian
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">USING #ff0000;">'DBB121.local';
 
SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">CREATE #993333; font-weight: bold; text-transform: uppercase;">TABLE t #66cc66;">(id#66cc66;">, n1#66cc66;">, n2#66cc66;">, pad#66cc66;">)
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">AS
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">SELECT rownum#66cc66;">, mod#66cc66;">(rownum#66cc66;">,#cc66cc;">113#66cc66;">)#66cc66;">, mod#66cc66;">(rownum#66cc66;">,#cc66cc;">113#66cc66;">)#66cc66;">, lpad#66cc66;">(#ff0000;">'*'#66cc66;">,#cc66cc;">100#66cc66;">,#ff0000;">'*'#66cc66;">)
  #cc66cc;">4  #993333; font-weight: bold; text-transform: uppercase;">FROM dual
  #cc66cc;">5  #993333; font-weight: bold; text-transform: uppercase;">CONNECT #993333; font-weight: bold; text-transform: uppercase;">BY level #66cc66;"><= #cc66cc;">10000;
 
SQL#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 p #993333; font-weight: bold; text-transform: uppercase;">AS
  #cc66cc;">2   #993333; font-weight: bold; text-transform: uppercase;">PROCEDURE p;
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">END p;
  #cc66cc;">4  #66cc66;">/
 
SQL#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 p #993333; font-weight: bold; text-transform: uppercase;">AS
  #cc66cc;">2   #993333; font-weight: bold; text-transform: uppercase;">PROCEDURE p #993333; font-weight: bold; text-transform: uppercase;">IS
  #cc66cc;">3     c #993333; font-weight: bold; text-transform: uppercase;">NUMBER;
  #cc66cc;">4   #993333; font-weight: bold; text-transform: uppercase;">BEGIN
  #cc66cc;">5     #993333; font-weight: bold; text-transform: uppercase;">SELECT count#66cc66;">(#66cc66;">*#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">INTO c
  #cc66cc;">6     #993333; font-weight: bold; text-transform: uppercase;">FROM t@loopback;
  #cc66cc;">7   #993333; font-weight: bold; text-transform: uppercase;">END p;
  #cc66cc;">8  #993333; font-weight: bold; text-transform: uppercase;">END p;
  #cc66cc;">9  #66cc66;">/

The dependencies of the package body P clearly shows that the table T is referenced through a database link:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT referenced_owner#66cc66;">, referenced_name#66cc66;">, referenced_type#66cc66;">, referenced_link_name
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">FROM user_dependencies
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">WHERE name #66cc66;">= #ff0000;">'P'
  #cc66cc;">4  #993333; font-weight: bold; text-transform: uppercase;">AND type #66cc66;">= #ff0000;">'PACKAGE BODY';
 
REFERENCED_OWNER REFERENCED_NAME REFERENCED_TYPE REFERENCED_LINK_NAME
#808080; font-style: italic;">---------------- --------------- --------------- ------------------------------
SYS              STANDARD        #993333; font-weight: bold; text-transform: uppercase;">PACKAGE
CHRIS            P               #993333; font-weight: bold; text-transform: uppercase;">PACKAGE
CHRIS            T               #993333; font-weight: bold; text-transform: uppercase;">TABLE           LOOPBACK.#993333; font-weight: bold; text-transform: uppercase;">LOCAL

It’s essential to know that to track that dependency the database engine stores, in the local data dictionary, a so called stub. Interestingly, even though it’s present in OBJ$, it’s not visible through data dictionary views like DBA_OBJECTS. Therefore, to see it you have to directly select OBJ$:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT o.remoteowner #993333; font-weight: bold; text-transform: uppercase;">AS referenced_owner#66cc66;">,
  #cc66cc;">2         o.name #993333; font-weight: bold; text-transform: uppercase;">AS referenced_name#66cc66;">,
  #cc66cc;">3         o.linkname #993333; font-weight: bold; text-transform: uppercase;">AS referenced_link_name#66cc66;">,
  #cc66cc;">4         to_char#66cc66;">(o.stime#66cc66;">, #ff0000;">'YYYY-MM-DD HH24:MI:SS'#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">AS specification_timestamp
  #cc66cc;">5  #993333; font-weight: bold; text-transform: uppercase;">FROM sys.obj$ o#66cc66;">,
  #cc66cc;">6       #66cc66;">(#993333; font-weight: bold; text-transform: uppercase;">SELECT p_obj#
  #cc66cc;">7        #993333; font-weight: bold; text-transform: uppercase;">FROM sys.dependency$
  #cc66cc;">8        #993333; font-weight: bold; text-transform: uppercase;">WHERE d_obj# #66cc66;">= #66cc66;">(#993333; font-weight: bold; text-transform: uppercase;">SELECT object_id
  #cc66cc;">9                        #993333; font-weight: bold; text-transform: uppercase;">FROM user_objects
 #cc66cc;">10                        #993333; font-weight: bold; text-transform: uppercase;">WHERE object_name #66cc66;">= #ff0000;">'P'
 #cc66cc;">11                        #993333; font-weight: bold; text-transform: uppercase;">AND object_type #66cc66;">= #ff0000;">'PACKAGE BODY'#66cc66;">)#66cc66;">) d
 #cc66cc;">12  #993333; font-weight: bold; text-transform: uppercase;">WHERE o.obj# #66cc66;">= d.p_obj#
 #cc66cc;">13  #993333; font-weight: bold; text-transform: uppercase;">AND o.name #66cc66;">= #ff0000;">'T';
 
REFERENCED_OWNER REFERENCED_NAME REFERENCED_LINK_NAME           SPECIFICATION_TIMESTAMP
#808080; font-style: italic;">---------------- --------------- ------------------------------ -----------------------
CHRIS            T               LOOPBACK.#993333; font-weight: bold; text-transform: uppercase;">LOCAL                 #cc66cc;">2016#66cc66;">-02#66cc66;">-06 01:#cc66cc;">33:#cc66cc;">21

Now that the necessary objects are available, let’s see what happens if an extension is added to the (remote) table referenced by the package body P:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">DECLARE
  #cc66cc;">2    l_extension #993333; font-weight: bold; text-transform: uppercase;">VARCHAR2#66cc66;">(#cc66cc;">30#66cc66;">);
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">BEGIN
  #cc66cc;">4    l_extension :#66cc66;">= dbms_stats.create_extended_stats@loopback#66cc66;">(user#66cc66;">, #ff0000;">'T'#66cc66;">, #ff0000;">'(n1,n2)'#66cc66;">);
  #cc66cc;">5  #993333; font-weight: bold; text-transform: uppercase;">END;
  #cc66cc;">6  #66cc66;">/
 
SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT column_name
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">FROM user_tab_cols@loopback
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">WHERE table_name #66cc66;">= #ff0000;">'T'
  #cc66cc;">4  #993333; font-weight: bold; text-transform: uppercase;">AND hidden_column #66cc66;">= #ff0000;">'YES';
 
COLUMN_NAME
#808080; font-style: italic;">------------------------------
SYS_STUBZH0IHA7K$KEBJVXO5LOHAS
 
SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT object_name#66cc66;">, object_type#66cc66;">, status
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">FROM user_objects
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">WHERE object_name #66cc66;">= #ff0000;">'P'
  #cc66cc;">4  #993333; font-weight: bold; text-transform: uppercase;">AND status #66cc66;">= #ff0000;">'INVALID';
 
no rows selected

Obviously, no invalidation took place. But, wait, this is expected… or not?

Since the database link is unidirectional, the remote database can’t inform the local one that something has changed. As a result, whatever the change on the remote database is, no immediate invalidation of the package takes place.

So, let’s do something on the local database that uses the same remote table and, as a result, requires the same (local) stub. For that purpose, let’s execute an anonymous PL/SQL block:

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">DECLARE
  #cc66cc;">2    c #993333; font-weight: bold; text-transform: uppercase;">NUMBER;
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">BEGIN
  #cc66cc;">4    #993333; font-weight: bold; text-transform: uppercase;">SELECT count#66cc66;">(#66cc66;">*#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">INTO c
  #cc66cc;">5    #993333; font-weight: bold; text-transform: uppercase;">FROM t@loopback;
  #cc66cc;">6  #993333; font-weight: bold; text-transform: uppercase;">END p;
  #cc66cc;">7  #66cc66;">/

What is the status of the body of the package P after the execution of the anonymous PL/SQL block?

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT object_name#66cc66;">, object_type#66cc66;">, status
  #cc66cc;">2  #993333; font-weight: bold; text-transform: uppercase;">FROM user_objects
  #cc66cc;">3  #993333; font-weight: bold; text-transform: uppercase;">WHERE object_name #66cc66;">= #ff0000;">'P'
  #cc66cc;">4  #993333; font-weight: bold; text-transform: uppercase;">AND status #66cc66;">= #ff0000;">'INVALID';
 
OBJECT_NAME     OBJECT_TYPE     STATUS
#808080; font-style: italic;">--------------- --------------- -------
P               #993333; font-weight: bold; text-transform: uppercase;">PACKAGE #993333; font-weight: bold; text-transform: uppercase;">BODY    INVALID

It is invalid. Why? Because during the compilation of the PL/SQL block the (local) database engine noticed that the (remote) table T has changed and, therefore, has updated the specification timestamp of the stub. Therefore, all objects that depends on that stub needed to be invalidated. There is no fine-grained invalidation for remote references!

SQL#66cc66;">> #993333; font-weight: bold; text-transform: uppercase;">SELECT o.remoteowner #993333; font-weight: bold; text-transform: uppercase;">AS referenced_owner#66cc66;">,
  #cc66cc;">2         o.name #993333; font-weight: bold; text-transform: uppercase;">AS referenced_name#66cc66;">,
  #cc66cc;">3         o.linkname #993333; font-weight: bold; text-transform: uppercase;">AS referenced_link_name#66cc66;">,
  #cc66cc;">4         to_char#66cc66;">(o.stime#66cc66;">, #ff0000;">'YYYY-MM-DD HH24:MI:SS'#66cc66;">) #993333; font-weight: bold; text-transform: uppercase;">AS specification_timestamp
  #cc66cc;">5  #993333; font-weight: bold; text-transform: uppercase;">FROM sys.obj$ o#66cc66;">,
  #cc66cc;">6       #66cc66;">(#993333; font-weight: bold; text-transform: uppercase;">SELECT p_obj#
  #cc66cc;">7        #993333; font-weight: bold; text-transform: uppercase;">FROM sys.dependency$
  #cc66cc;">8        #993333; font-weight: bold; text-transform: uppercase;">WHERE d_obj# #66cc66;">= #66cc66;">(#993333; font-weight: bold; text-transform: uppercase;">SELECT object_id
  #cc66cc;">9                        #993333; font-weight: bold; text-transform: uppercase;">FROM user_objects
 #cc66cc;">10                        #993333; font-weight: bold; text-transform: uppercase;">WHERE object_name #66cc66;">= #ff0000;">'P'
 #cc66cc;">11                        #993333; font-weight: bold; text-transform: uppercase;">AND object_type #66cc66;">= #ff0000;">'PACKAGE BODY'#66cc66;">)#66cc66;">) d
 #cc66cc;">12  #993333; font-weight: bold; text-transform: uppercase;">WHERE o.obj# #66cc66;">= d.p_obj#
 #cc66cc;">13  #993333; font-weight: bold; text-transform: uppercase;">AND o.name #66cc66;">= #ff0000;">'T';
 
REFERENCED_OWNER REFERENCED_NAME REFERENCED_LINK_NAME           SPECIFICATION_TIMESTAMP
#808080; font-style: italic;">---------------- --------------- ------------------------------ -----------------------
CHRIS            T               LOOPBACK.#993333; font-weight: bold; text-transform: uppercase;">LOCAL                 #cc66cc;">2016#66cc66;">-02#66cc66;">-06 01:#cc66cc;">34:06

In summary, if you have PL/SQL objects that references a remote table, the creation of an extension on the remote table, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of the PL/SQL object that references it.

A final thought… Static references to remote objects are, in general, bad news. There are a number of reasons to avoid them. Covering those reasons and the techniques that can be used to do so is out-of-scope for this post. But that subject is actually one that I might cover in a future post.