Function Based Indizes können Trigger und Stored PL/SQL invalidieren!

updated on

Im Zuge einer Tabellenreorganisation mittels ALTER TABLE MOVE ONLINE sind plötzlich invalide Objekte aufgetreten, die zu Problemen im (RAC) Applikationsbetrieb geführt haben. Bei der Suche nach der Ursache sind einige interessante Zusammenhänge aufgefallen, die genauer analysiert werden.

Einfaches Test-Environment

Um festzustellen, welche Objekte und warum betroffen werden, nutzen wir ein einfaches Test-Environment bestehend aus:

  • Der Tabelle T1
  • Stored functions und stored procedures
  • Verschiedenen Triggern
  • verschiedenen Indizes – darunter ein Function Based Index

Zuerst eine Tabelle mit einigen Datensätzen (der Dateninhalt ist egal) sowie einen Index für den PK anlegen.

create table trigtest.t1 as (select * from dba_objects);

create index TRIGTEST.T1_PK on TRIGTEST.T1 (OBJECT_ID);

Dann erzeugen wir eine stored function und eine stored procedure:

create or replace function trigtest.t1_func return number
is
  v_ret number;
begin
  begin
    select count(*) into v_ret from trigtest.t1;
    exception when others then v_ret := -1;
  end;
  return(v_ret);
end;
/

create or replace procedure trigtest.t1_proc
is
begin
  insert into trigtest.t1(object_id) values(1);
  commit;
end;
/

Im nächsten Schritt erzeugen wir mehrere Trigger. Welche (before/after insert/update/delete) es sind, spielt keine Rolle, nur der Trigger Code ist relevant!

create or replace trigger trigtest.t1_trig_null before insert on trigtest.t1
for each row
declare
  v_username varchar2(128);
begin
  null;
end;
/

create or replace trigger trigtest.t1_trig_t1 before update on trigtest.t1
for each row
declare
  v_username varchar2(128);
begin
  begin
    select max(object_name) into v_username
      from trigtest.t1;
  end;
end;
/

create or replace trigger trigtest.t1_trig_t1_func after delete on trigtest.t1
for each row
declare
  v_num number;
begin
  v_num := trigtest.t1_func();
end;
/

create or replace trigger trigtest.t1_trig_t1_proc after update on trigtest.t1
for each row
begin
  trigtest.t1_proc();
end;
/

Kontrolle, ob alle Objekte valid sind:

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;

OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

Auswirkung von CREATE und DROP von Function Based Indizes

Wir erzeugen zuerst einen Function Based Index, den wir wieder droppen. Dabei überprüfen wir, was dabei passiert ist.
Hinweis: das ist in allen Oracle Datenbank Editonen erlaubt!

create index trigtest.t1_idx on trigtest.t1(upper(subobject_name));

Erneute Kontrolle, ob alles valid ist:

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;

OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_IDX          INDEX       VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

Soweit schaut alles gut aus, doch was passiert, wenn man den Function Based Index droppt?

drop index trigtest.t1_idx;

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;

OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     INVALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     INVALID
T1_TRIG_T1_PROC TRIGGER     INVALID

Der Trigger mit „NULL“ als Code sowie die Trigger mit Aufrufen von Stored PL/SQL werden INVALID! Wir kompilieren die Objekte …

alter trigger trigtest.t1_trig_null compile;

alter trigger trigtest.t1_trig_t1_func compile;

alter trigger trigtest.t1_trig_t1_proc compile;

select object_name,object_type,status from dba_objects where owner='TRIGTEST' order by 1,2;

OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

…und es ist wieder alles gut!

Auswirkung von ALTER TABLE MOVE ONLINE

Für den nächsten Versuch legen wir den Function Based Index wieder an und verifizieren, was bei einem ALTER TABLE MOVE ONLINE passiert. Online Operationen sind der Oracle Enterprise Edition vorbehalten und dienen dazu, dass man im laufenden Betrieb reorganisieren kann! Eigentlich ein zentrales KEY-Feature der Oracle Enteprise Edition.

create index trigtest.t1_idx on trigtest.t1(upper(subobject_name));

Kontrolle, ob alles valid ist:

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;
 
OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_IDX          INDEX       VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

Jetzt reorganisieren wir die Tabelle mittels ALTER TABLE MOVE ONLINE:

alter table trigtest.t1 move ONLINE;

Welchen Status haben die Objekte jetzt?

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;
 
OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_IDX          INDEX       VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     INVALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     INVALID
T1_TRIG_T1_PROC TRIGGER     INVALID

Sowohl die Tabelle als auch der Index sind VALID. Die Trigger mit NULL bzw. Aufruf von Stored Objekten sind jedoch INVALID! Also wieder reparieren und überprüfen:

alter trigger trigtest.t1_trig_null compile;

alter trigger trigtest.t1_trig_t1_func compile;

alter trigger trigtest.t1_trig_t1_proc compile;

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;
 
OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_IDX          INDEX       VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

Was passiert, wenn wir die Tabelle nur MOVEN (ohne Online)

Wenn man keine Enterprise Edition hat, kann man die Tabelle ebenfalls MOVEN, jedoch nicht online. Was passiert dann mit den Objekten?

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;
 
OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_IDX          INDEX       VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

Diese bleiben VALID, allerdings ist das nicht ganz die Wahrheit. Beim ALTER TABLE MOVE gehen die Indizes auf UNUSABLE, hier der Beweis:

select status 
  from dba_indexes 
 where index_name = 'T1_IDX';

STATUS
--------
UNUSABLE

Also müssen wir nach dem TABLE MOVE noch den Index reparieren:

alter index trigtest.t1_idx rebuild online;

select status 
  from dba_indexes 
 where index_name = 'T1_IDX';

STATUS
--------
VALID

Der Index ist jetzt wieder OK, wie schaut es mit den Objekten aus?

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;
 
OBJECT_NAME     OBJECT_TYPE STATUS
--------------- ----------- ------
T1              TABLE       VALID
T1_FUNC         FUNCTION    VALID
T1_IDX          INDEX       VALID
T1_PK           INDEX       VALID
T1_PROC         PROCEDURE   VALID
T1_TRIG_NULL    TRIGGER     VALID
T1_TRIG_T1      TRIGGER     VALID
T1_TRIG_T1_FUNC TRIGGER     VALID
T1_TRIG_T1_PROC TRIGGER     VALID

Hier ist ebenfalls alles OK. Der REBUILD hat keine Spuren hinterlassen!

Erste Analyse – was lernen wir daraus?

Anscheinend führt das DROPPEN von FBI sowie ALTER TABLE MOVE ONLINE dazu, dass Objekte INVALID werden. Die Frage ist jetzt allerdings: Warum?

Schauen wir in DBA_DEPENDENCIES:

NAME            TYPE      REFERENC REFERENCED_NAME              REFERENCE DEPE
--------------- --------- -------- ---------------------------- --------- ----
T1_FUNC         FUNCTION  SYS      STANDARD                     PACKAGE   HARD
T1_TRIG_NULL    TRIGGER   SYS      STANDARD                     PACKAGE   HARD
T1_TRIG_T1      TRIGGER   SYS      STANDARD                     PACKAGE   HARD
T1_TRIG_T1_FUNC TRIGGER   SYS      STANDARD                     PACKAGE   HARD
T1_FUNC         FUNCTION  SYS      SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE   HARD
T1_PROC         PROCEDURE SYS      SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE   HARD
T1_FUNC         FUNCTION  TRIGTEST T1                           TABLE     HARD
T1_PROC         PROCEDURE TRIGTEST T1                           TABLE     HARD
T1_TRIG_NULL    TRIGGER   TRIGTEST T1                           TABLE     HARD
T1_TRIG_T1      TRIGGER   TRIGTEST T1                           TABLE     HARD
T1_TRIG_T1_FUNC TRIGGER   TRIGTEST T1                           TABLE     HARD
T1_TRIG_T1_PROC TRIGGER   TRIGTEST T1                           TABLE     HARD
T1_TRIG_T1_FUNC TRIGGER   TRIGTEST T1_FUNC                      FUNCTION  HARD
T1_TRIG_T1_PROC TRIGGER   TRIGTEST T1_PROC                      PROCEDURE HARD

Stored PLSQL – egal ob Trigger, Function oder Procedure – haben eine Referenz auf SYS.STANDARD, weil dies die Basis für PL/SQL Code ist. Zusätzlich haben die Funktionen und Prozeduren noch eine Referenz auf SYS.SYS_STUB_FOR_PURITY_ANALYSIS (hat mit den Pragmas von PLSQL zu tun). Diese Referenzen interessieren uns nicht weiter. Alle Dependencies sind HARD – somit führen diese bei Änderungen eines Objekts intern zur Invalidierung von abhängigen Objekten.

Reduzieren wir die Abfrage auf das Relevante:

select owner, name, type, referenced_name 
  from dba_dependencies 
 where owner='TRIGTEST' 
   and REFERENCED_OWNER='TRIGTEST';

OWNER    NAME            TYPE      REFERENCED_NAME
-------- --------------- --------- ---------------
TRIGTEST T1_TRIG_T1_FUNC TRIGGER   T1
TRIGTEST T1_FUNC         FUNCTION  T1
TRIGTEST T1_PROC         PROCEDURE T1
TRIGTEST T1_TRIG_T1      TRIGGER   T1
TRIGTEST T1_TRIG_NULL    TRIGGER   T1
TRIGTEST T1_TRIG_T1_PROC TRIGGER   T1
TRIGTEST T1_TRIG_T1_FUNC TRIGGER   T1_FUNC
TRIGTEST T1_TRIG_T1_PROC TRIGGER   T1_PROC

Was bedeutet dies nun?

Alle Objekte sind von der Tabelle T1 abhängig, soweit so klar. Wenn die Tabelle modifiziert wird, werden die abhängigen Objekte potentiell invalidiert. Die Trigger T1_FUNC und T1_PROC haben noch eine Abhängigkeit auf die PL/SQL Funktion und Procedure – auch klar soweit. Was fehlt hier eigentlich? Richtig, die Indizes! Dieser scheinen überhaupt nicht auf!

Das ist normal. Ein Index gehört zur Tabelle, auf der er erzeugt wurde. Aber somit gibt es auch keine Abhängigkeit der Triggers T1_TRIG_NULL,T1_TRIG_T1_FUNC und T1_TRIG_T1_PROC mit den Indizes, oder?

Leider findet man in DBA_DEPENDENCIES nicht alle Informationen. Für Function Based Indizes gibt es noch die SYS.ICOLDEP$ Tabelle!

select * 
  from sys.icoldep$ 
 where obj# in (select object_id 
                  from dba_objects 
                 where object_name='T1_IDX');

      OBJ#        BO#    INTCOL#
---------- ---------- ----------
     77166      77150          3

OK, dass ist jetzt schlecht lesbar. Mit dem folgenden Statement wird es klarer:

select do1.object_name, do2.object_name, dtc.column_name
  from sys.icoldep$ icd join dba_objects do1 
                          on (icd.obj#=do1.object_id)
                        join dba_objects do2 
                          on (icd.bo# =do2.object_id)
                        join dba_tab_columns dtc 
                          on (do2.object_name=dtc.table_name and icd.intcol#=dtc.column_id)
 where icd.obj# in (select object_id 
                      from dba_objects 
                     where object_name='T1_IDX');

OBJECT_NAME OBJECT_NAME COLUMN_NAME
----------- ----------- --------------
T1_IDX      T1          SUBOBJECT_NAME

Aah! Hier versteckt sich die Abhängigkeit zwischen Tabelle T1 und dem Function Based Index.

Debugging: Session Trace für DROP Function Based Index

Die detailierte Analyse des Session Trace Files für das DROP des FBI würde den Rahmen sprengen, daher sind hier nur die Highlights.

Der DROP erzeugt intern knapp 70 Recursive SQL Statements, die Interessantesten sind:

  • Das Cleanup der SYS.ICOLDEP$ Tabelle (obj#=Objekt_ID des FBI)
delete from icoldep$ where obj#=:1
  • Sowie ein Update auf obj$ (DBA_OBJECTS) für drei Objekte
update obj$ set obj#=:4,  type#=:5, ctime=:6, mtime=:7, stime=:8, status=:9, dataobj#=:10, flags=:11, oid$=:12, spare1=:13, spare2=:14, spare3=:15, signature=:16, spare7=:17, spare8=:18, spare9=:19, dflcollid=decode(:20,0,null,:20), creappid=:21, creverid=:22, modappid=:23, modverid=:24, crepatchid=:25, modpatchid=:26 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null

Relevante Bind Variable Inhalte

obj#=77153,77155,77156
status=6 (=INVALID)

Welche Objekte wurden invalidiert?

select object_id, object_name 
  from dba_objects 
 where owner='TRIGTEST'

 OBJECT_ID OBJECT_NAME
---------- ------------------------------
     77150 T1
     77151 T1_FUNC
     77152 T1_PROC
     77153 T1_TRIG_NULL
     77154 T1_TRIG_T1
     77155 T1_TRIG_T1_FUNC
     77156 T1_TRIG_T1_PROC
     77165 T1_PK

Natürlich unsere drei Trigger. Warum gerade diese drei, werden wir später herauszufinden versuchen. Die Abfrage davor:

select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp,
       nvl(d.property,0), o.type#, o.subname, o.status, d.d_attrs
  from dependency$ d, obj$ o   
 where d.p_obj#=:1  
   and (d.p_timestamp=nvl(:2,d.p_timestamp) or d.property=2)  
   and o.owner#=nvl(:3,o.owner#)
   and d.d_obj#=o.obj#
 order by o.obj#

liefert alle Abhängigkeiten, die man auch in DBA_DEPENDENCIES finden kann. Invalidiert werden aber nur die drei Trigger. Der Grund dafür könnte der Inhalt der Spalte dependency$.d_attrs sein (RAW Data Type) – später mehr dazu.

Schauen wir uns jetzt die Trigger genauer an

Weitere Tests haben ergeben, dass es nur vom Trigger Text abhängt. Code von Triggern, die invalid werden:

  • T1_TRIG_NULL: null;
  • T1_TRIG_T1_FUNC: v_num := trigtest.t1_func();
  • T1_TRIG_T1_PROC: trigtest.t1_proc();

Code vom Trigger, der nicht invalid wird:

  • T1_TRIG_T1 : select max(object_name) into v_username from trigtest.t1;

Was fällt uns dabei auf?

Wenn der Trigger Code eine Referenz auf die Tabelle hat, wird dieser NICHT invalid! Sobald diese Referenz überhaupt nicht (NULL;) oder nur indirekt (via Stored PL/SQL) gegeben ist, werden die Trigger invalid.

Prüfen wir das ganze mit einem neuen Trigger, der sowohl die Tabelle referenziert, als auch eine Prozedur aufruft:

create or replace trigger trigtest.t1_trig_t1_tab_func 
 after insert on trigtest.t1
   for each row
declare
  v_username varchar2(128);
begin
  select max(object_name) into v_username from trigtest.t1;
  trigtest.t1_proc();
end;
/

Jetzt den FBI droppen:

drop index trigtest.T1_IDX;

und überprüfen.

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;

OBJECT_NAME         OBJECT_TYPE STATUS
------------------- ----------- -------
T1                  TABLE       VALID
T1_FUNC             FUNCTION    VALID
T1_PK               INDEX       VALID
T1_PROC             PROCEDURE   VALID
T1_TRIG_NULL        TRIGGER     INVALID
T1_TRIG_T1          TRIGGER     VALID
T1_TRIG_T1_FUNC     TRIGGER     INVALID
T1_TRIG_T1_PROC     TRIGGER     INVALID
T1_TRIG_T1_TAB_FUNC TRIGGER     VALID

Der neue Trigger T1_TRIG_T1_TAB_FUNC bleibt VALID. Somit ist unsere Hypothese bestätigt. Enthält der Trigger eine direkte Referenz auf die Tabelle, bleibt dieser valide, ansonsten INVALID.

Der Trigger T1_TRIG_T1_FUNC ist beispielsweise ein AFTER DELETE Trigger. Was passiert, wenn wir jetzt Records löschen?

delete from trigtest.t1 where owner='SYSTEM';

commit;

select object_name, object_type, status 
  from dba_objects 
 where owner='TRIGTEST' 
 order by 1,2;

OBJECT_NAME         OBJECT_TYPE STATUS
------------------- ----------- -------
T1                  TABLE       VALID
T1_FUNC             FUNCTION    VALID
T1_PK               INDEX       VALID
T1_PROC             PROCEDURE   VALID
T1_TRIG_NULL        TRIGGER     INVALID
T1_TRIG_T1          TRIGGER     VALID
T1_TRIG_T1_FUNC     TRIGGER     VALID
T1_TRIG_T1_PROC     TRIGGER     INVALID
T1_TRIG_T1_TAB_FUNC TRIGGER     VALID

Der Trigger T1_TRIG_T1_FUNC wurde erfolgreich revalidiert. Das Gleiche passiert mit den anderen Triggern, sobald diese wieder benötigt werden.

Wie kann man ermitteln, welche Objekte voraussichtlich invalid werden?

Die folgende Abfrage ermittelt, welche Objekte durch ein drop functionbased Index bzw. einem ALTER TABLE MOVE ONLINE voraussichtlich invalid werden.

select b.owner             owner,
       b.object_name       name,
       b.object_type       type,
       decode(bitand(a.property, 3), 2, 'REF', 'HARD') dependency_type,
       a.property,
       a.d_attrs,
  from dependency$ a,
      dba_objects b
where a.p_obj# in ( select object_id
                     from dba_objects
                    where owner       = 'TRIGTEST')
  and a.d_obj# = b.object_id(+)
      /* Die folgenden Dependency$ Attribute fueren in der Regel zur Invalidierung */
  and a.property = 1
  and d_attrs is null
order by 1,2,3;

Die Analyse hat ergeben, dass jene Trigger invalid werden, bei denen PROPERTY=1 sowie D_ATTRS null ist.

Was ist also das Problem?

Die Trigger selbst sind nicht das Problem, sondern weitere Kaskadierungen, die dazu führen, dass auch PL/SQL Packages invalid werden. Bei der Applikation, die diese Analyse ausgelöst hat, wurden in Summe mehrere 100 Objekte invalid. Wenn die Applikation im RAC läuft, kann das recompile sehr lange dauern und dadurch den Betrieb stören. Notfalls muss man im RAC die Applikation sogar stoppen.

Der Auslöser für die Analyse war eine Applikation im RAC, bei der ab dem Zeitpunkt eines ALTER TABLE MOVE ONLINE die meisten Sessions mit folgendem Error Stack geflogen sind:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of has been invalidated
ORA-04061: existing state of package body "SHOP.RTSUTIL" has been invalidated
ORA-04065: not executed, altered or dropped package body "SHOP.RTSUTIL"
ORA-06508: PL/SQL: could not find program unit being called: "SHOP.RTSUTIL"
ORA-06512: at "SHOP.CONFIG_BUNDLE_PROFILES", line 549
ORA-06512: at "SHOP.EVENT_REPORT_INSERT", line 331
ORA-04088: error during execution of trigger 'SHOP.EVENT_REPORT_INSERT'
ORA-06512: at "SHOP.INSERT_EVENT", line 781
ORA-06512: at line 1

Dadurch ist es zu Datenverlust gekommen. Erst nach dem Stoppen der Applikation war das Recompile der PL/SQL Objekte erfolgreich.

Das Verhalten ist zumindest ab Oracle 11g bis inkl. Oracle 19c reproduzierbar.

Referenzen

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

This site uses Akismet to reduce spam. Learn how your comment data is processed.