24. Februar 2014

Monitoring eigenen, langlaufenden PL/SQL-Codes mit V$SESSION_LONGOPS

Monitor own, long-running PL/SQL Code with V$SESSION_LONGOPS
Wer schon mal längerlaufende PL/SQL Prozeduren gebaut hat, kennt das Problem: Wie kann man etwas über den aktuellen Status seiner Prozedur erfahren? Ist sie schon fast fertig - oder immer noch am Anfang und es geht nicht voran?
DBMS_OUTPUT hilft nicht wirklich weiter: Denn DBMS_OUTPUT ist ein Puffer, der gefüllt wird, während die Prozedur läuft - aber erst nach Abschluß derselben liest SQL*Plus diesen mit den entsprechenden READ_LINE Funktionan aus und stellt die Inhalte auf dem Bildschirm dar. Und die Inhalte sind nur in der gleichen Datenbanksession sichtbar - mit einer anderen Sitzung kommt man an die Werte nicht heran.
Man könnte nun noch darüber nachdenken, mit autonomen Transaktionen in Tabellen oder mit UTL_FILE in eine Datei zu schreiben. Aber eigentlich gibt es ein "Standardwerkzeug" hierfür: die Dictionary View V$SESSION_LONGOPS. In diese schreibt die Oracle-Datenbank ohnehin schon die Status langlaufender SQL-Operationen (bspw. bei einem CREATE TABLE AS SELECT oder bei einem CREATE INDEX). Im PL/SQL Paket DBMS_APPLICATION_INFO befindet sich die Prozedur SET_SESSION_LONGOPS, mit der man auch selbst einen Eintrag in V$SESSION_LONGOPS vornehmen kann. Allerdings ist die Schnittstelle "etwas" umständlich. Es gibt recht viele Parameter, von denen man einige nicht braucht und andere kompliziert "von einem Aufruf zum nächsten" gereicht werden müssen. Instrumentiert man seinen Code damit, so wird das alles sehr schnell ... sehr unübersichtlich - und das ist wohl einer der Gründe, warum V$SESSION_LONGOPS von PL/SQL-Entwicklern kaum genutzt wird.
Ich schlage daher ein Wrapper-Package für DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS vor. Ziel ist es, einfach nutzbare Funktionsaufrufe bereitzustellen. So könnte es aussehen.
create or replace package pkg_session_longops is
  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2);
  procedure do_update (p_opname in varchar2, p_status in number);
end pkg_session_longops;
/
sho err

create or replace package body pkg_session_longops is
  type t_array is table of number index by varchar2(255);
  g_arr_rindex t_array;
  g_arr_slno   t_array;
  g_arr_total  t_array;

  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2) is
    l_rindex binary_integer := dbms_application_info.set_session_longops_nohint;
    l_slno   binary_integer;
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => 0,
      totalwork    => p_target,
      target_desc  => 'no target',
      units        => p_units
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
    g_arr_total(p_opname) := p_target;
  end do_init;
      
  procedure do_update (p_opname in varchar2, p_status in number) is
    l_rindex binary_integer := g_arr_rindex(p_opname);
    l_slno   binary_integer := g_arr_slno(p_opname);
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => p_status,
      totalwork    => g_arr_total(p_opname),
      target_desc  => 'no target',
      units        => null
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
  end do_update;
end pkg_session_longops;
/
sho err
Das Ergebnis ist ein PL/SQL Package PKG_SESSION_LONGOPS:
PROCEDURE DO_INIT
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_OPNAME                       VARCHAR2                IN
 P_TARGET                       NUMBER                  IN
 P_UNITS                        VARCHAR2                IN
PROCEDURE DO_UPDATE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_OPNAME                       VARCHAR2                IN
 P_STATUS                       NUMBER                  IN
Mit DO_INIT initialisieren wir eine Zeile in V$SESSION_LONGOPS, mit DO_UPDATE wird sie mit einem neuen Status aktualisiert. Eingebaut in eine langlaufende PL/SQL Prozedur, könnte es so aussehen.
create or replace procedure do_longrun as
begin
  pkg_session_longops.do_init('DO_LONGRUN', 300, 'seconds');
  for i in 1..30 loop
    apex_util.pause(10);
    pkg_session_longops.do_update('DO_LONGRUN', (i * 10));
  end loop;
end;
Das sieht doch schon sehr einfach aus. Startet man die Prozedur, so kann man den Status während der Laufzeit (auch von einer anderen Datenbanksession aus) bequem verfolgen ...
SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        60        300 seconds

1 Zeile wurde ausgewählt.

SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        70        300 seconds

1 Zeile wurde ausgewählt.
Eigentlich ganz einfach, oder ...? Und das beste ist, dass man sich in der "Standard-Infrastruktur" bewegt; man stellt seine Informationen also über die Dictionary-View bereit, in die der Datenbankadministrator ohnehin hineinsieht, wenn er sich über langlaufende Operationen informieren möchte. In dem Wrapper-Package habe ich mich bemüht, die Aufrufe möglichst einfach zu gestalten; einige der Parameter von DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS wie bspw. die TARGET_ID, habe ich gar nicht verwendet - wer möchte, kann sich das Paket ja entsprechend erweitern ...
Everyone, who already created long-running PL/SQL procedures, knows about the problem: How can I determine the current status of my procedure? Is is close to finish, or is it hanging?
DBMS_OUTPUT is not really helpful - due to the fact, that invocations of DBMS_OUTPUT.PUT_LINE actually "fill a buffer", and after the PL/SQL call finished, SQL*Plus will retrieve the buffer contents with DBMS_OUTPUT.READ_LINE. At that time, the information will be visible on the screen. The buffer is furthermore session-private, so we cannot access them from another session.
Other alternatives would be to use autonomous transactions in order to perform INSERTs into a table or to use UTL_FILE to write something into a file on the database servers' filesystem. But the Oracle database already offers a very good tool for this purpose: V$SESSION_LONGOPS. Oracle itself writes status information for long running SQL statements into that view (you might check that during a long running CREATE TABLE AS SELECT or CREATE INDEX). The PL/SQL package DBMS_APPLICATION_INFO contains the procedure SET_SESSION_LONGOPS, which allows to maintain own status information within V$SESSION_LONGOPS. But its API is rather cumbersome and complex - you need to pass a lot of parameters - and some of them are not easy to understand. Instrumenting own code with direct invocations of SET_SESSION_LONGOPS would lead to confusing and hard-readable programs very quickly.
So I'd propose a wrapper package for DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS. The goal is to provide an easy interface for PL/SQL developers.
create or replace package pkg_session_longops is
  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2);
  procedure do_update (p_opname in varchar2, p_status in number);
end pkg_session_longops;
/
sho err

create or replace package body pkg_session_longops is
  type t_array is table of number index by varchar2(255);
  g_arr_rindex t_array;
  g_arr_slno   t_array;
  g_arr_total  t_array;

  procedure do_init (p_opname in varchar2, p_target in number, p_units in varchar2) is
    l_rindex binary_integer := dbms_application_info.set_session_longops_nohint;
    l_slno   binary_integer;
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => 0,
      totalwork    => p_target,
      target_desc  => 'no target',
      units        => p_units
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
    g_arr_total(p_opname) := p_target;
  end do_init;
      
  procedure do_update (p_opname in varchar2, p_status in number) is
    l_rindex binary_integer := g_arr_rindex(p_opname);
    l_slno   binary_integer := g_arr_slno(p_opname);
  begin
    dbms_application_info.set_session_longops(
      rindex       => l_rindex,
      slno         => l_slno,
      op_name      => p_opname,
      target       => 0,
      context      => 0,
      sofar        => p_status,
      totalwork    => g_arr_total(p_opname),
      target_desc  => 'no target',
      units        => null
    );
    g_arr_rindex(p_opname) := l_rindex;
    g_arr_slno(p_opname) := l_slno;
  end do_update;
end pkg_session_longops;
/
sho err
The package looks like this:
PROCEDURE DO_INIT
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_OPNAME                       VARCHAR2                IN
 P_TARGET                       NUMBER                  IN
 P_UNITS                        VARCHAR2                IN
PROCEDURE DO_UPDATE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_OPNAME                       VARCHAR2                IN
 P_STATUS                       NUMBER                  IN
DO_INIT initializes a new row in V$SESSION_LONGOPS - the current status of that row would be zero. Subsequent invocations of DO_UPDATE lead to updated status information. A long-running PL/SQL procedure, instrumented with these calls would look like this:
create or replace procedure do_longrun as
begin
  pkg_session_longops.do_init('DO_LONGRUN', 300, 'seconds');
  for i in 1..30 loop
    apex_util.pause(10);
    pkg_session_longops.do_update('DO_LONGRUN', (i * 10));
  end loop;
end;
Looks quite simple, doesn't it? The contents of V$SESSION_LONGOPS can now be easily monitored (also from other database sessions).
SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        60        300 seconds

1 row selected.

SQL> select opname, sofar, totalwork, units from v$session_longops
  2* where opname = 'DO_LONGRUN' and sofar < totalwork

OPNAME                         SOFAR  TOTALWORK UNITS
------------------------- ---------- ---------- --------------------------------
DO_LONGRUN                        70        300 seconds

1 row selected.
This is easy, isn't it? And we reporting our status to a standard monitoring infrastructure within Oracle. Database administrators seeking information on long running jobs, typically look into V$SESSION_LONGOPS. When creating this package, my primary goal was to create a simple API, so I did not use some of the SET_SESSION_LONGOPS parameters (such as the TARGET_ID parameter). But if somebody likes, the package can be easily extended ...

Kommentare:

Anonym hat gesagt…

Hallo Carsten,
der Aufruf der originalen Prozedur ist ja wirklich kompliziert. Ein ähnliches Verfolgen einer Session kann man aber auch über die "einfachen" Prozeduren SET_ACTION, SET_CLIENT_INFO und SET_MODULE des gleichen Packages erreichen (wobei deren Werte dann allerdings im View v$Session wiederzufinden sind).
Gruß, Niels Hecker

Carsten Czarski hat gesagt…

Hallo Niels,

Das ist richtig - und darüber hatte ich in der Tat auch schon nachgedacht. Ich habe mich dann aus zwei Gründen für die (aufwändigere) V$SESSION_LONGOPS entschieden ...

1) im Gegensatz zu V$SESSION kann jeder User sie selektieren (ohne zusätzliche Rechte)
2) man nutzt die gleiche View wie die Datenbank selbst - wie im Posting beschrieben ...

Beste Grüße

Carsten

Unknown hat gesagt…

Danke für den wrapper. Er hat mich zu einer Klasse inspiriert, die ich noch einfacher im Handling finde:

create or replace type longops as object (
gv_operation varchar2(2000),
-- The target, e.g. Number of seconds, interations etc
gv_target number,
-- units
gv_units varchar2(255),

gv_sofar number,

-- returned by dbms_application_info to identify the longops record
gv_rindex integer,
gv_slno integer,

-- --------------------------------------------------------------------------
-- Constructor
-- Initializes variables and start the timer.
--
constructor function longops(
pv_operation in varchar2, -- A unique name of the operation
pv_target in number, -- A target. E.g. time or max iterations
pv_units in varchar2 -- Units (seconds, minutes, iterations, cubes)
) return self as result,

-- --------------------------------------------------------------------------
-- do_update
-- Update the work done so far
member procedure do_update(
pv_sofar in number -- work done so far
)
)
/

create or replace type body longops
as
-- -----------------------------------------------------------------------
-- Constructor
--
constructor function longops(
pv_operation in varchar2, -- A unique name of the operation
pv_target in number, -- A target. E.g. time or max iterations
pv_units in varchar2 -- Units (seconds, minutes, iterations, cubes)
) return self as result
is
begin
self.gv_rindex := dbms_application_info.set_session_longops_nohint;
self.gv_slno := null;
self.gv_operation := substr(pv_operation, 1, 2000);
self.gv_target := pv_target;
self.gv_units := substr(pv_units, 1, 255);

sys.dbms_application_info.set_session_longops(
rindex => self.gv_rindex,
slno => self.gv_slno,
op_name => self.gv_operation,
target => 0,
context => 0,
sofar => 0,
totalwork => self.gv_target,
target_desc => 'no target',
units => self.gv_units
);
return;
end longops;

-- --------------------------------------------------------------------------
-- do_update
-- Update the work done so far
member procedure do_update(
pv_sofar in number -- work done so far
)
is
begin
self.gv_sofar := pv_sofar;

sys.dbms_application_info.set_session_longops(
rindex => self.gv_rindex,
slno => self.gv_slno,
op_name => self.gv_operation,
target => 0,
context => 0,
sofar => self.gv_sofar,
totalwork => self.gv_target,
target_desc => 'no target',
units => null
);
end;

end;
/

rem Usage:
declare
lv_longops longops;
begin
lv_longops := new longops('DO_LONGOPS', 10, 'iterations');
for i in 1..10 loop
apex_util.pause(10);
lv_longops.do_update(i);
end loop;
end;
/

Beliebte Postings