Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:plsql_sleep_19c

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
prog:plsql_sleep_19c [2019/05/22 09:38] – [DBMS_LOCK.sleep] gpipperrprog:plsql_sleep_19c [2023/02/01 12:12] (aktuell) – [DBMS_SESSION.sleep] gpipperr
Zeile 1: Zeile 1:
 +=====PL/SQL - Eine DB Session "schlafen" legen - Wie und welche Sleep Funktion in welcher Oracle DB Version einsetzen? =====
  
 +
 +**Aufgabe**: Eine Datenbank Session soll mit einer sleep Funktion eine gewisse Zeit pausiert werden.
 +
 +Z.B. in einem Monitoring Job, der jede Minute prüfen ob ein andere Job noch aktiv ist und keine Fehler geworfen hat. 
 +
 +<note warning>DBMS_LOCK.sleep - ab 18c bzw. in 19c / 23c auf keine Fall mehr verwenden! Gar nicht mehr offiziell in der Doku!</note>
 +
 +
 +
 +**Möglichkeiten:**
 +
 +**APEX Installiert:**
 +  * APEX_UTIL.pause ( aber <fc #800000>nur bis maximal 120 Sekunden!</fc> , Wait Event "PL/SQL lock timer") 
 +
 +**DB Version vor 18c:**
 +  * Java über java.lang.Thread.sleep  ( <fc #800000>Waits Events in der Statistik</fc> sichtbar, nicht verwirren lassen!)
 +  * DBMS_LOCK.sleep (<fc #800000>Sicherheitsproblem</fc> mit generellen Zugriff auf das Package DBMS_LOCK, Wait Event "PL/SQL lock timer"))
 +
 +
 +**DB Version ab 18c**
 +  * <fc #008080>**DBMS_SESSION.sleep**</fc>  ( <fc #008000>so sollte es schon immer sein ...</fc> ) 
 +
 +
 +
 +
 +----
 +
 +
 +
 +====APEX_UTIL.pause====
 +
 +Ist in der DB APEX installiert, kann auf APEX_UTIL zurückgegriffen werden.
 +
 +10 Sekunden warten:
 +<code plsql>
 +begin
 + APEX_UTIL.PAUSE(10);
 +end;
 +/
 +</code>
 +
 +Jetzt ist nur die Frage, wie das intern umgesetzt wird.
 +Das Synonym "APEX_UTIL" zeigt jedenfalls auf das Package "htmldb_util" und von da auf "WWV_FLOW_UTILITIES.PAUSE", dort wird dann "SYS.DBMS_LOCK.SLEEP(L_SECONDS);" gekapselt.
 +
 +Und alle Werte über 120 Sekunden werden mit dem Default 120 Sekunden überschrieben!
 +
 +D.h. die maximale Wartezeit beträgt 120 Sekunden.
 +
 +Eigene Routine um auch länger als 120s warten zu können und dann ab der Version 18 gleich  DBMS_SESSION.sleep verwenden zu können:
 +<code sql>
 +-- 
 +CREATE OR REPLACE 
 +PROCEDURE sleep_plsql(p_seconds IN NUMBER)
 +IS
 +  v_parts pls_integer;
 +  v_LastRound pls_integer;
 +  v_max_pos_seconds pls_integer:=120;
 +BEGIN
 +
 +
 +  $IF DBMS_DB_VERSION.VER_LE_12 $THEN
 +  v_max_pos_seconds:=120;
 +  $ELSE
 +    v_max_pos_seconds:=3600;
 +  $END
 +  
 +   IF p_seconds/v_max_pos_seconds > 1 THEN
 +      --- floor largest integer equal to or less than
 +      v_parts:=FLOOR(p_seconds/v_max_pos_seconds);
 +      -- return the remainder 
 +      v_LastRound:=MOD( p_seconds, v_max_pos_seconds );
 +   ELSE
 +      v_parts:=0;
 +      v_LastRound:=p_seconds;
 +   END IF;
 +
 +
 +   -- if we are on 18 use the DBMS_SESSION sleep methode!
 +   $IF DBMS_DB_VERSION.VER_LE_12 $THEN
 + 
 +          -- we need an oracle apex security context .-(
 +          -- put in your workspace
 +          apex_util.set_security_group_id (p_security_group_id => apex_util.find_security_group_id (p_workspace => 'GPI'));
 +         
 +          --  APEX_UTIL.PAUS cann only sleep may 120s!
 +          -- 
 +          IF v_parts > 0 THEN
 +              FOR i IN 1..v_parts
 +               loop
 +                 APEX_UTIL.PAUSE(120);
 +               END loop;
 +           END IF;
 +           ---
 +           IF v_LastRound > 0 THEN
 +              APEX_UTIL.PAUSE(v_LastRound);
 +           END IF; 
 +    $ELSE  
 +          --  DBMS_SESSION can only sleep may 3600s!
 +          -- 
 +          IF v_parts > 0 THEN
 +              FOR i IN 1..v_parts
 +               loop
 +                 DBMS_SESSION.sleep(3600);
 +               END loop;
 +          END IF;
 +          ---
 +          IF v_LastRound > 0 THEN
 +              DBMS_SESSION.sleep(v_LastRound);
 +          END IF;          
 +    $END
 +END;
 +/
 +
 +--- testen
 +
 +set serveroutput on
 +begin
 +dbms_output.put_line('-- Info start at '||to_char(systimestamp,'hh24:mi:SSxFF'));
 +sleep_plsql(11);
 +dbms_output.put_line('-- Info start at '||to_char(systimestamp,'hh24:mi:SSxFF'));
 +sleep_plsql(119);
 +dbms_output.put_line('-- Info start at '||to_char(systimestamp,'hh24:mi:SSxFF'));
 +sleep_plsql(123);
 +dbms_output.put_line('-- Info start at '||to_char(systimestamp,'hh24:mi:SSxFF'));
 +sleep_plsql(245);
 +dbms_output.put_line('-- Info start at '||to_char(systimestamp,'hh24:mi:SSxFF'));
 +sleep_plsql(545);
 +dbms_output.put_line('-- Info start at '||to_char(systimestamp,'hh24:mi:SSxFF'));
 +end;
 +/
 +
 +
 +</code>
 +
 +
 +===Problem ORA-20987: APEX - Package variable g_security_group_id must be set ====
 +
 +Fehler:
 +<code sql>
 +ORA-20987: APEX - Package variable g_security_group_id must be set. - Contact your application administrator.
 +</code>
 +
 +Lösung, Security ID setzen!
 +<code sql>
 +  -- we need an oracle apex security context .-(
 +  -- put in your workspace
 +  
 +  apex_util.set_security_group_id (p_security_group_id => apex_util.find_security_group_id (p_workspace => 'GPI'));
 +  
 +</code>
 +
 +Das ist dann leider nicht so schön, damit wird der Code doch sehr von der Umgebung abhängig.
 +
 +
 +=== Wait Event - PL/SQL lock timer ===
 +
 +In der DB ist <fc #800000>** "PL/SQL lock timer"**</fc> der Wait Event hinter diesem Aufruf in den Wait Statistiken. 
 +
 +----
 +
 +==== Java ====
 +
 +Verwendung der Java Klasse "java.lang.Thread"
 +
 +
 +<code sql>
 +create or replace PROCEDURE sleep_java(p_milli_seconds in number) 
 +  AS 
 +  LANGUAGE JAVA NAME 'java.lang.Thread.sleep(long)';
 +</code>
 +
 +
 +<fc #800000>**Nachteil:**</fc> In den AWR / Statspack Berichten wird dann OVJM gerne als häufigster Wait aufgezeigt, sind noch andere Java Komponenten im Einsatz verfälscht das stark das Ergebniss.
 +
 +Wie:
 +{{ :prog:oracle_ovjm_waits_with_java_sleep.png | OVJM Waits mit java.lang.Thread.sleep in der Datenbank }}
 +
 +----
 +
 +====DBMS_LOCK.sleep====
 +
 +
 +
 +Nachteil:
 +
 +  * Sicherheitsprobleme mit DBMS_LOCK habe dazu geführt das her keine Public Synonym in der DB für dieses Package vorliegt und damit ein direkter Grant benötigt wird!
 +
 +
 +<note warning>Ab 18c auf keine Fall mehr verwenden! Gar nicht mehr offiziell in der Doku!</note>
 +
 +----
 +
 +====DBMS_SESSION.sleep====
 +
 +Ab der Version 18c!
 +
 +Session "schläft" für n Sekunden, der Maximal Wert beträgt 3600 Sekunden (ansonsten gibt es ein ORA-38148: invalid time limit specified!).
 +
 +Aus der Doku //"The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value."
 +//
 +
 +Beispiel
 +<code sql>
 +
 +begin
 + dbms_session.sleep(10.45);
 +end;
 +
 +</code>
 +
 +
 +=== Problem! nur bis 3600s möglich! ===
 +
 +<code sql>
 +
 +SYS@GPI-?>BEGIN
 +  2   dbms_session.sleep(3601);
 +  3  END;
 +  4  /
 +BEGIN
 +*
 +ERROR at line 1:
 +ORA-38148: invalid time limit specified
 +ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 +ORA-06512: at "SYS.DBMS_SESSION", line 432
 +ORA-06512: at line 2
 +</code>
 +
 +Siehe dazu den Code oben mit APEX, hier nochmal die wichigen Teile für eine Umgebung die noch 12c und 19c für die Software verwendt:
 +<code sql>
 +PROCEDURE sleep_plsql(p_seconds in number)
 +is
 +
 +  v_parts pls_integer:=0;
 +  v_LastRound pls_integer:=0;
 +  v_max_pos_seconds pls_integer:=120;
 +  v_use_java boolean:=false;
 +
 +BEGIN
 +
 +  $IF DBMS_DB_VERSION.VER_LE_12 $THEN
 +        v_max_pos_seconds:=120;
 +  $ELSE
 +        v_max_pos_seconds:=3600;
 +  $END
 +
 +   IF p_seconds/v_max_pos_seconds > 1 THEN
 +      --- floor largest integer equal to or less than
 +      v_parts:=FLOOR(p_seconds/v_max_pos_seconds);
 +      -- return the remainder
 +      v_LastRound:=MOD( p_seconds, v_max_pos_seconds );
 +   ELSE
 +      v_parts:=0;
 +      v_LastRound:=p_seconds;
 +   END IF;
 +
 +    -- if we are on 18 use the DBMS_SESSION sleep methode!
 +    $IF DBMS_DB_VERSION.VER_LE_12 $THEN
 +    
 +    ... use apex sleep see code above 
 +    
 +      
 +    $ELSE 
 +       --  DBMS_SESSION can only sleep may 3600s!     
 +       -- 
 +          IF v_parts > 0 THEN
 +              FOR i IN 1..v_parts
 +               loop
 +                 DBMS_SESSION.sleep(3600);
 +               END loop;
 +          END IF;
 +          ---
 +          IF v_LastRound > 0 THEN
 +              DBMS_SESSION.sleep(v_LastRound);
 +          END IF;          
 +   $END      
 +
 +end;
 +</code>
 +
 +----
 +
 +
 +
 +
 +==== Quellen ====
 +
 +
 +  * https://oracle-base.com/articles/18c/dbms_session-sleep-18c#dbms_lock
 +  * https://stackoverflow.com/questions/2561671/sleep-function-in-oracle
 +  * https://oracledeli.wordpress.com/2017/03/31/plsql-sleep-without-using-dbms_lock/
prog/plsql_sleep_19c.txt · Zuletzt geändert: 2023/02/01 12:12 von gpipperr