5. März 2013

Einige Worte zu PL/SQL Abhängigkeiten oder: "Erweitere Deine Packages immer am Ende"

Some words on PL/SQL Dependencies - or "Always extend your packages at the botton"
Mit Oracle 11.1 wurde für PL/SQL Pakete das "fine grained dependency model" eingeführt. Kurz gesagt bedeutet das, das Änderungen an einem PL/SQL Paket nicht zwingend alle anderen Objekte, die das Paket irgendwie verwenden, invalidiert werden und neu kompiliert werden müssen. Ändert man im PL/SQL Paket die Prozedur P, so werden Objekte, welche die Prozedur A verwenden, nicht invalidiert. So weit, so gut: Das wollen wir testen.
create or replace package pkg_inv_test is
  function helloworld return varchar2;
  function today return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  function helloworld return varchar2 is begin return 'Hello World'; end helloworld;
  function today return date is begin return sysdate; end today;
end pkg_inv_test;
/

create or replace function get_today 
return date is
begin
  return pkg_inv_test.today;
end get_today;
/
Die Funktion GET_TODAY ist also abhängig vom Package PKG_INV_TEST Ein kurzer Test ...
select get_today from dual
/

GET_TODAY
-------------------
05.03.2013 10:31:13

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID
In Oracle10g würde jede Änderung an dem Package die Funktion invalidieren - in Oracle11g sollte das Invalidieren nur dann auftreten, wenn die Funktion PKG_INV_TEST.TODAY verändert wird. Auch dies testen wir kurz: Wir fügen der Funktion einen neuen Parameter mit Default-Wert hinzu.

create or replace package pkg_inv_test is
  function helloworld return varchar2;
  function today(p_diff in number default 1) return date;
end pkg_inv_test;
/


create or replace package body pkg_inv_test is
  function helloworld return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;
end pkg_inv_test;
/

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      INVALID

1 Zeile wurde ausgewählt.
Vor dem nächsten Aufruf wird diese Funktion nun automatisch neu kompiliert.
select get_today from dual;

GET_TODAY
-------------------
05.03.2013 10:38:57

1 Zeile wurde ausgewählt.

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 Zeile wurde ausgewählt.
So beim einfachen Ausprobieren sieht eine Invalidierung nicht schlimm aus - aber durch das Neukompilieren wird zum Einen der gesamte Status der Funktion zurückgesetzt - zum anderen darf die Funktion gerade nicht in Nutzung sein - wenn sie gerade für einen anderen User läuft, bekommt man die Änderung gar nicht durch. Änderugen an einer anderen Funktion im Package invalidieren GET_TODAY dagegen nicht - das ist der große Vorteil der fine-grained-dependencies in Oracle11g. Nun nehmen wir also eine Änderung allein an der Funktion HELLOWORLD vor - diese tangiert GET_TODAY nicht im Geringsten ...
create or replace package pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2;
  function today(p_diff in number default 1) return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;
end pkg_inv_test;
/

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 Zeile wurde ausgewählt.
Und siehe da: GET_TODAY bleibt VALID. Aber dann sollte es ja auch kein Thema sein, eine neue Funktion hinzuzufügen, oder ...?
create or replace package pkg_inv_test is
  -- Neue Funktion hier
  function new_function return varchar2;

  function helloworld(p_param in number default 1) return varchar2;
  function today(p_diff in number default 1) return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  -- Neue Funktion hier
  function new_function return varchar2 is begin return 'Das ist neu!'; end new_function;

  function helloworld(p_param in number default 1) return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;
end pkg_inv_test;
/


select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      INVALID

1 Zeile wurde ausgewählt.
Nanu? Die Funktion GET_TODAY hängt doch nur von der PKG_INV_TEST.TODAY ab ... und die wurde nicht angefasst. Trotzdem ist sie nun INVALID. Die Erklärung ist nicht einfach zu finden; im Advanced Application Developers Guide unter Invalidation of Dependent Objects wird man aber fündig. Dort heißt es zu Änderungen an einem Package, dass die Fine Grained Invalidation nicht gilt (Exception), wenn u.a. der Entry Point sich ändert (Dependent object references a package procedure or function whose call signature or entry-point number, changed.). In einer Fußnote kann man dann lesen, dass der "Entry Point" einer Funktion oder Prozedur sich aus der Position im Code des Package ergibt.
Also: Durch den Einbau der neuen Funktion zu Beginn des Package haben sich die Entry Points aller enthaltenen Prozeduren und Funktionen geändert - also haben wir die Fine Grained Invalidation dadurch ausgehebelt. Aber dann sollte es ja keine Invalidierung geben, wenn die neue Funktion am Ende des Package hinzugefügt wird ...
create or replace package pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2;
  function today(p_diff in number default 1) return date;

  -- Neue Funktion hier
  function new_function return varchar2;
end pkg_inv_test;
/


create or replace package body pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;

  -- Neue Funktion hier
  function new_function return varchar2 is begin return 'Das ist neu!'; end new_function;
end pkg_inv_test;
/

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 Zeile wurde ausgewählt.
Zusammengefasst (tl;dr) kann man sagen: Erweitere Deine Packages immer am Ende - alles, was vom Code hinter einer Änderung abhängig ist, wird invalidiert!
In database version 11.1, Oracle introduced the "fine grained dependency model". Briefly, this means, that a change to one function or procedure within a package does not affect objects depending on other components. So if we have the top level function F, which depends on A within package P (P.A) - only changes to P.A will invaldiate F. F will not be invalidated, when other components of P are being changed. So far - so good: Let's play with this ...
create or replace package pkg_inv_test is
  function helloworld return varchar2;
  function today return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  function helloworld return varchar2 is begin return 'Hello World'; end helloworld;
  function today return date is begin return sysdate; end today;
end pkg_inv_test;
/

create or replace function get_today 
return date is
begin
  return pkg_inv_test.today;
end get_today;
/
The top-level function GET_TODAY depends on TODAY within the package PKG_INV_TEST. All set and working ...
select get_today from dual
/

GET_TODAY
-------------------
05.03.2013 10:31:13

1 row selected.

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 row selected.
In Oracle10g, any change to PKG_INV_TEST would invalidate GET_TODAY - in 11g only changes to PKG_INV_TEST.TODAY should lead to an invalidiation - let's test this as well: We'll change GET_TODAY in order to add a new parameter with a default value.
create or replace package pkg_inv_test is
  function helloworld return varchar2;
  function today(p_diff in number default 1) return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  function helloworld return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;
end pkg_inv_test;
/

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      INVALID

1 row selected.
GET_TODAY is now invalid - as expected. When it's being called the next time, the database will recompile it automatically.
select get_today from dual;

GET_TODAY
-------------------
05.03.2013 10:38:57

1 row selected.

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 row selected.
At the first glance, an Invalidation is not a big thing - since the database recompiles the component automatically. But the recompilation will also reset the PL/SQL state - if the dependent object is another package (with global variables) - these will be reset. Also, the dependent object must not be used in order to become recompiled. So if GET_TODAY would be in use, we would have to wait until it's free in order to make the change. So it's always good practice to avoid invalidations as far as possible. Given this, a change to the other function within PKG_INV_TEST (HELLOWORLD) should not affect GET_TODAY at all.
create or replace package pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2;
  function today(p_diff in number default 1) return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;
end pkg_inv_test;
/

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 row selected.
As expected: GET_TODAY remains VALID. So it should also remain valid when we add a new component to the package, shouldn't it ...?
create or replace package pkg_inv_test is
  -- New function being added here ...
  function new_function return varchar2;

  function helloworld(p_param in number default 1) return varchar2;
  function today(p_diff in number default 1) return date;
end pkg_inv_test;
/

create or replace package body pkg_inv_test is
  -- New function being added here ...
  function new_function return varchar2 is begin return 'This is new!'; end new_function;

  function helloworld(p_param in number default 1) return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;
end pkg_inv_test;
/


select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      INVALID

1 row selected.
Hey ... what happened? GET_TODAY only depends on PKG_INV_TEST.TODAY - and this function did not change. But it has been invalidated anyway. The explanation for this behaviour is not easy to find, but the Advanced Application Developers Guide contains the relevant pieces in Invalidation of Dependent Objects. Table 18-2 (showing the fine-grained-dependecy behaviour for various ALTER commands) says in the row for ALTER PACKAGE, that fine grained invalidation does not apply (Exception), when the Entry point of a component is being changed (Dependent object references a package procedure or function whose call signature or entry-point number, changed.). The Footnote then explains that the "Entry Point" of a package component is based on its position in the package code.
Since we have added our new component at the beginning of the package, the code positions of all other components have been changed - therefore all entry points have been changed. And that means, that all dependent objects have been invalidated. So the invalidation should not occur when we add our new component at the end of the package ...
create or replace package pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2;
  function today(p_diff in number default 1) return date;

  -- New function being added here ...
  function new_function return varchar2;
end pkg_inv_test;
/


create or replace package body pkg_inv_test is
  function helloworld(p_param in number default 1) return varchar2 is begin return 'Hello World'; end helloworld;
  function today(p_diff in number default 1) return date is begin return sysdate; end today;

  -- New function being added here ...
  function new_function return varchar2 is begin return 'This is new!'; end new_function;
end pkg_inv_test;
/

select object_name, status from user_objects where object_name = 'GET_TODAY'
/

OBJECT_NAME                    STATUS
------------------------------ -------
GET_TODAY                      VALID

1 row selected.
Voilá! The summary (tl;dr) of this blog posting is very simple: Always extend your packages at the bottom! Everything, which depends on package code below your change, will be invalidated!

Keine Kommentare:

Beliebte Postings