Benutzerdefinierte ILM ADO Bedingungen mit PL/SQL

In meinem post über ILM storage tiering habe ich über ADO-Bedingungen geschrieben, die wir für Storage Tiering Policies angeben können, wenn Datensegmente in einen Read Only Tablespace verschoben werden. Andere Möglichkeit, Storage Tiering mit einer Bedingung zu versehen wäre eine benutzerdefinierte PL/SQL-Funktion, die einen Boolean-Wert zurückgibt. Wie funktioniert das? Und können wir auf die Heat Map Information zurückgreifen?

Leider gibt die Dokumentation auf den ersten Blick nicht so viel Information her:

ON
Use this clause to specify a PL/SQL function that returns a boolean value. For function_name, specify the name of the function. The policy will take effect when the function returns TRUE.

Ein gutes Beispiel ist im Blog von Uwe Hesse zu finden. Das Beispiel verwendet eine einfache Funktion im Sinne einer EIN/AUS-Logik. Wahrscheinlich war es auch der Grund, warum ich den entscheidenden Punkt lange übersehen hatte. I habe mich gefragt, ob es oft Sinn macht, eine benutzerdefinierte Bedingung zu definieren, die nicht in Kenntniss von Objekten ist, für die die Bedingung überprüft wird? Wenn nicht, wie übergeben wir die Informationen zu diesen Objekten? Und endlich habe ich den Parameter der Funktion gesehen (der gar nicht verwendet wurde)! Die Funktion akzeptiert OBJECT_ID als Parameter.

Seit dem war es wie mit dem Baader-Meinhof Phänomen (Frequency Illusion): ich habe auch ein anderes gutes Beispiel im MOS Doc ID 1967038.1 gefunden und sogar eine Dokumentationsseite, die – zwar nur im Beispiel – die Existenz des Parameters für die Policy-Funktion zeigt.

Das schauen wir uns an. Wir nehmen denselben Setup, wie für den vorherigen Beitrag über ILM Storage Tiering. Dieselbe Tabelle SCOTT.EMPLOYEE und derselbe Tablespace LOW_COST_STORE. We wollen, nachdem unsere benutzerdefinierte Bedingung als positiv evaluiert wurde, die Table zum neunen Tablespace verschieben. Wir versuchen mal die ADO-Bedingung „AFTER 10 DAY OF NO MODIFICATION“ nachzubauen.

Zuerst erstellen wir die Funktion zum Auswerten der gewünschten Bedingung. Mit der als Parameter übergebenen OBJECT_ID können wir aus der View USER_HEAT_MAP_SEGMENT die letzte Modifizierungszeit abfragen. Hier müssen wir den Wert vom ILM-Paramater POLICY_TIME überprüfen, damit wir mit dessen Änderung für Testzwecke umgehen können.

Nach dem Erstellen der Funktion, können wir bereits die Policy erstellen, die diese Funktion verwendet und die Informationen aus dem Data Dictionary anzeigen lassen. Beachten Sie, dass die Datenbank in dem Fall kein Wissen über unsere Bedingung hat (CONDITION_DAYS=0).

CREATE OR REPLACE FUNCTION f_custom (p_object_id number) RETURN BOOLEAN IS
  2  v_time NUMBER;
  3  v_days_no_modification NUMBER := 10;
  4  v_ilm_parameter number;
  5  BEGIN
  6    SELECT  sysdate-SEGMENT_WRITE_TIME
  7    INTO    v_time
  8    FROM    user_heat_map_segment
  9    WHERE   object_name = (SELECT object_name FROM user_objects WHERE object_id=p_object_id);
 10  
 11    SELECT value
 12    INTO v_ilm_parameter
 13    FROM dba_ilmparameters WHERE name = 'POLICY TIME';
 14  
 15    IF v_ilm_parameter = 1 /*DBMS_ILM_ADMIN.POLICY_IN_SECONDS*/ THEN
 16  	 v_time:=v_time*60*60*24;
 17    END IF;
 18  
 19    RETURN v_time > v_days_no_modification;
 20  
 21  END;
 22  /

Function created.

SCOTT> -- Storage tiering with user defined condition function
SCOTT> ALTER TABLE scott.employee1  ILM ADD POLICY TIER TO low_cost_store ON f_custom;

Table altered.

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Show ILM policy information
SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> SELECT policy_name, action_type, scope, tier_tablespace
  2    ,      condition_type, condition_days, custom_function
  3    FROM   user_ilmdatamovementpolicies;

POLICY_NAME ACTION_TYPE SCOPE	TIER_TABLESPACE  CONDITION_TYPE         CONDITION_DAYS  CUSTOM_FUNCTION
----------- ----------- ------- ---------------- ---------------------- --------------  ------------------
P261	    STORAGE     SEGMENT LOW_COST_STORE	 USER DEFINED           0               F_CUSTOM


SCOTT> 
SCOTT> SELECT policy_name, object_name, object_type, enabled
  2    FROM user_ilmobjects;

POLICY_NAME OBJECT_NAME          OBJECT_TYPE	    ENABLED
----------- -------------------- ------------------ -------
P261	    EMPLOYEE1            TABLE              YES

Jetzt stellen wir das Heat Map wieder für Testzwecke auf die Sekundeneinheiten um, aktualisieren die Tabelle und lassen die Heat Map Information wegschreiben. Die Abfrage von USER_HEAT_MAP_SEGMENT zeigt die letzte Änderungszeit WRITE_TIME für die Tabelle EMPLOYEE1. Wir führen ILM manuell aus und prüfen, wo die Table EMPLOYEE1 jetzt ist. Nichts passiert? Die Abfrage zeigt weiterhin den Tablespace USERS an. Ist aber auch richtig so – wir haben keine 10 Sekunden abgewartet. Die View USER_ILMEVALUATIONDETAILS bestätigt das: PRECONDITION NOT SATISFIED.

SCOTT> EXECUTE dbms_ilm_admin.customize_ilm(DBMS_ILM_ADMIN.POLICY_TIME, DBMS_ILM_ADMIN.ILM_POLICY_IN_SECONDS);

PL/SQL procedure successfully completed.

SCOTT> -- 2. set start date
SCOTT> EXECUTE DBMS_ILM_ADMIN.SET_HEAT_MAP_START(sysdate -50);

PL/SQL procedure successfully completed.

SCOTT> 
SCOTT> UPDATE scott.employee1 e SET e.empno = e.empno+1;

3584 rows updated.

SCOTT> commit;

Commit complete.

SCOTT> 
SCOTT> EXECUTE dbms_ilm.flush_all_segments;

PL/SQL procedure successfully completed.

SCOTT> SELECT	object_name, to_char(segment_write_time,'DD-MON-YY hh24:mi:ss') write_time
  2  FROM	user_heat_map_segment
  3  WHERE	object_name ='EMPLOYEE1';

OBJECT_NAME	     WRITE_TIME
-------------------- ---------------------------
EMPLOYEE1	     21-OCT-17 11:03:50

SCOTT> 
SCOTT> declare
  2  v_executionid number;
  3  begin
  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
  5  		 execution_mode => dbms_ilm.ilm_execution_offline,
  6  		 task_id   => v_executionid);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Table EMPLOYEE1 was NOT moved to tablespace LOW_COST_STORE
SCOTT> --------------------------------------------------------------------------------------------------
SCOTT> select tablespace_name, segment_name
  2  from   user_segments
  3  where  segment_name='EMPLOYEE1';

TABLESPACE_NAME 	       SEGMENT_NAME
------------------------------ --------------------
USERS			       EMPLOYEE1

SCOTT> 
SCOTT> select task_id, object_name, selected_for_execution from user_ilmevaluationdetails;

   TASK_ID OBJECT_NAME		SELECTED_FOR_EXECUTION
---------- -------------------- ------------------------------------------
       219 EMPLOYEE1		PRECONDITION NOT SATISFIED

Dann warten wir 10 Sekunden und wiederholen den Versuch. Jetzt wurde die Tabelle EMPLOYEE1 in den neuen Tablespace verschoben.

SCOTT> EXECUTE	dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SCOTT> 
SCOTT> declare
  2  v_executionid number;
  3  begin
  4    dbms_ilm.execute_ILM (ILM_SCOPE => dbms_ilm.SCOPE_SCHEMA,
  5  		 execution_mode => dbms_ilm.ilm_execution_offline,
  6  		 task_id   => v_executionid);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SCOTT> ----------------------------------------------------------------------------------------------------
SCOTT> -- Table EMPLOYEE1 is now moved to tablespace LOW_COST_STORE
SCOTT> --------------------------------------------------------------------------------------------------
SCOTT> select tablespace_name, segment_name
  2  from   user_segments
  3  where  segment_name='EMPLOYEE1';

TABLESPACE_NAME 	       SEGMENT_NAME
------------------------------ --------------------
LOW_COST_STORE		       EMPLOYEE1

SCOTT> 
SCOTT> SELECT task_id, to_char(start_time, 'dd/mm/yyyy hh24:mi:ss') as start_time, u.STATE
  2  FROM user_ilmtasks u;

   TASK_ID START_TIME	       STATE
---------- ------------------- ---------
       219 21/10/2017 11:03:52 COMPLETED
       220 21/10/2017 11:04:13 COMPLETED

SCOTT> 
SCOTT> select task_id, job_name, job_state, to_char(completion_time, 'dd/mm/yyyy hh24:mi:ss') completion
  2  from user_ilmresults;

   TASK_ID JOB_NAME	   JOB_STATE			       COMPLETION
---------- --------------- ----------------------------------- ------------------------------
       220 ILMJOB4212	   COMPLETED SUCCESSFULLY	       21/10/2017 11:04:14

SCOTT> 
SCOTT> select task_id, object_name, selected_for_execution from user_ilmevaluationdetails;

   TASK_ID OBJECT_NAME		SELECTED_FOR_EXECUTION
---------- -------------------- ------------------------------------------
       219 EMPLOYEE1		PRECONDITION NOT SATISFIED
       220 EMPLOYEE1		SELECTED FOR EXECUTION

Es funktioniert also. Wenn man statt zu warten, bis der Schwellenwert beim Tablespace erreicht wird, die Storage Tiering Policies wie für Compression Tiering auch auf Heat Map Information basieren will, kann man es mit Hilfe benutzerdefinierter Funktionen erreichen. Leider verliert man dabei ein gutes Stück Transparenz und Selbstdokumentation, weil die Bedingungen nicht im Data Dictionary gespeichert werden.

Schreibe einen Kommentar

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

*