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 ...

4. Februar 2014

SQL anstatt "opatch -lsinventory" - mit Oracle12c

SQL query instead of "opatch -lsinventory" - Oracle12c
Im neuen Datenbankrelease 12c gibt es, wie immer, einige neue Funktionen, die ein Schattendasein pflegen. Dazu gehört wohl auch das neue PL/SQL-Paket DBMS_QOPATCH. Es erlaubt das Auslesen des "Oracle-Inventory" mit SQL - möchte man sich über die installieren Patches informieren, muss man sich nun also nicht mehr am Betriebssystem einloggen - es geht ganz bequem mit SQL.
Mit ein paar Tricks ging das auch schon vor Oracle12c - so hatte ich bereits vor einigen Jahren ein Blog Posting veröffentlicht, welches die Vorgehensweise mit Hilfe eines BFILE und einigen XML-Funktionen erläutert. Nun ist das also out-of-the-box Teil der Datenbank - so weit, so gut.
Schauen wir mal ins Package DBMS_QOPATCH hinein. Es fällt recht schnell auf, dass fast alle Funktionen einen XMLTYPE zurückliefern ...
:
FUNCTION GET_OPATCH_FILES RETURNS XMLTYPE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 PNUM                           VARCHAR2                IN
FUNCTION GET_OPATCH_INSTALL_INFO RETURNS XMLTYPE
FUNCTION GET_OPATCH_LIST RETURNS XMLTYPE
FUNCTION GET_OPATCH_LSINVENTORY RETURNS XMLTYPE
FUNCTION GET_OPATCH_OLAYS RETURNS XMLTYPE
:
... was für ein schnelles Nachsehen nicht so angenehm ist.
SQL> select dbms_qopatch.GET_OPATCH_LSINVENTORY from dual;

GET_OPATCH_LSINVENTORY
-----------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850" standalone='yes'?>
<InventoryInstance>
  <oracleHome>
    <UId>OracleHome-75909c1d-7269-45d1-802e-75deb9104053</UId>
    <targetTypeId>oracle_home</targetTypeId>
    <patchingModel>oneoff</patchingModel>
    <path>/opt/oracle/product/12.1.0/db</path>
    <targetTypeId>oracle_home</targetTypeId>
    <inventoryLocation>/opt/oraInventory</inventoryLocation>
    <isShared>false</isShared>
  </oracleHome>
  <patches>
    <patch xmlns:xsi="http://www.w3.org/2001/XM
DBMS_QOPATCH bringt auch ein XSLT-Stylesheet mit (DBMS_QOPATCH.GET_OPATCH_XSLT), welches das XML in ein "plain text" Format umwandelt ...
select xmltransform(
  dbms_qopatch.get_opatch_lsinventory, 
  dbms_qopatch.get_opatch_xslt
) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home       : /opt/oracle/product/12.1.0/db
Inventory         : /opt/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c                                    12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                                         12.1.0.1.0
Sun JDK                                                     1.6.0.37.0
oracle.swd.oui.core.min                                     12.1.0.1.0
Installer SDK Component                                     12.1.0.1.0
Oracle One-Off Patch Installer                              12.1.0.1.0
Oracle Universal Installer                                  12.1.0.1.0
Oracle USM Deconfiguration                                  12.1.0.1.0
Oracle Configuration Manager Deconfiguration                10.3.1.0.0
:
Aber etwas in einem Plain Text zu finden, ist nicht unbedings eleganter als in XML. Was wir wollen, wäre eine strukturierte Sicht auf die Informationen - schließlich sind wir in einer Datenbank. Zum Glück haben wir ja die SQL/XML-Funktionen, mit denen das Zerlegen des XML-Dokumentes ein Kinderspiel ist. Man muss sich "lediglich" mit den XML-Tags ein wenig auseinandersetzen - diese haben sich übrigens geändert, so dass die SQL-Kommandos im erwähnten Blog-Posting nicht mehr funktionieren. Aber fangen wir gleich an: Als erstes bauen wir eine View, welche die installierten Einzelpatches (oneoffs) anzeigt.
select
  patch_id,
  apply_time,
  rollbackable
from
  xmltable(
    '//patch'
    passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
      patch_id     number                   path 'patchID',
      apply_time   timestamp with time zone path 'appliedDate',
      rollbackable varchar2(10)             path 'rollbackable'
  );

  PATCH_ID APPLY_TIME                          ROLLBACKAB
---------- ----------------------------------- ----------
  16527374 08.11.13 14:02:02,000000 +01:00     true
  17027533 08.11.13 10:24:12,000000 +01:00     true
Schon mal gar nicht schlecht. Aber das XML enthält noch mehr Informationen; bspw. ist auch enthalten, welche Bugs ganz konkret mit den Patches gefixt wurden. Auch das lässt sich schnell aufbereiten ...
select
  patch_id,
  bug_id,
  bug_desc
from
  xmltable(
    '//patch'
     passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
     patch_id number path 'patchID',
     bugs xmltype path 'bugs/bug'
  ) p,
  xmltable(
    'bug'
    passing p.bugs
    columns
      bug_id number path '@id',
      bug_desc varchar2(500) path 'description'
  );

  PATCH_ID     BUG_ID BUG_DESC
---------- ---------- --------------------------------------------------------
  16527374   16527374 [12100-LIN64-130318]CDB ORA-07445 EM EXPRESS HOME GOT IO
  17027533   17034172 LX64_MAIN_CDB ORA-7445 R DUE TO RANDOM SGA CORRUPTION
  17027533   16694728 MSGQ LSM1 HITS ORA-600 [KJBLPGORM !ANTILOCK]
  17027533   16448848 LGSB WIDETAB ORA-600[17147] & ORA-600[600] & SQL
  17027533   16863422 ORA-600 [KXDAM_NOTIFY_CELL IOCTLFAILED] DURING RPM UPGRA
  17027533   16634384 LOGMINER BUILDER ASSERTS KRVUATLA20 AFTER UNREGISTERING 
         :          : :
Ob ein bestimmter Bug in meiner Datenbank nun gefixt ist oder nicht, dass findet man nun ganz einfach heraus - schließlich arbeiten wir mit SQL. Natürlich kann man sich mit diesen SQL-Anweisungen ganz einfach ein paar schöne Views bauen - ich denke an DBA_INSTALLED_PATCHES, DBA_FIXED_BUGS oder ähnliches.
The new database version Oracle12c contains, as always, many new functions which are not highlighted all around - in all the presentations and whitepapers. One of these is the new PL/SQL package DBMS_QOPATCH. It allows to read the Oracle Software Repository ("Oracle Inventory") with SQL. If you want to know which one-off patches are installed on the current system, you can now query this with SQL - logging into the operating system is no longer required.
Honestly, this was already possible before Oracle12c - some years ago, I publised a blog posting, which described how to achieve this with a BFILE and some SQL/XML functions. Now, we have this out-of-the-box - so far, so good.
Let's have a look at DBMS_QOPATCH. Most functions return XMLTYPE ...
:
FUNCTION GET_OPATCH_FILES RETURNS XMLTYPE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 PNUM                           VARCHAR2                IN
FUNCTION GET_OPATCH_INSTALL_INFO RETURNS XMLTYPE
FUNCTION GET_OPATCH_LIST RETURNS XMLTYPE
FUNCTION GET_OPATCH_LSINVENTORY RETURNS XMLTYPE
FUNCTION GET_OPATCH_OLAYS RETURNS XMLTYPE
:
... but dealing with XMLTYPE in order to see which patches have been installed, is a bit cumbersome.
SQL> select dbms_qopatch.GET_OPATCH_LSINVENTORY from dual;

GET_OPATCH_LSINVENTORY
-----------------------------------------------------------------------------
<?xml version="1.0" encoding="CP850" standalone='yes'?>
<InventoryInstance>
  <oracleHome>
    <UId>OracleHome-75909c1d-7269-45d1-802e-75deb9104053</UId>
    <targetTypeId>oracle_home</targetTypeId>
    <patchingModel>oneoff</patchingModel>
    <path>/opt/oracle/product/12.1.0/db</path>
    <targetTypeId>oracle_home</targetTypeId>
    <inventoryLocation>/opt/oraInventory</inventoryLocation>
    <isShared>false</isShared>
  </oracleHome>
  <patches>
    <patch xmlns:xsi="http://www.w3.org/2001/XM
DBMS_QOPATCH also provides a stylesheet (DBMS_QOPATCH.GET_OPATCH_XSLT), which formats the information as a plain text document ...
select xmltransform(
  dbms_qopatch.get_opatch_lsinventory, 
  dbms_qopatch.get_opatch_xslt
) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home       : /opt/oracle/product/12.1.0/db
Inventory         : /opt/oraInventory
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 12c                                    12.1.0.1.0
Installed Products ( 131)

Oracle Database 12c                                         12.1.0.1.0
Sun JDK                                                     1.6.0.37.0
oracle.swd.oui.core.min                                     12.1.0.1.0
Installer SDK Component                                     12.1.0.1.0
Oracle One-Off Patch Installer                              12.1.0.1.0
Oracle Universal Installer                                  12.1.0.1.0
Oracle USM Deconfiguration                                  12.1.0.1.0
Oracle Configuration Manager Deconfiguration                10.3.1.0.0
:
But finding something within this plain text would be still cumbersome. We want structured information - we are within a database.
Luckily, we can still use SQL/XML functions, work directly on the XML output and project all the information into columns of a SQL SELECT result. First, we want to see, which "oneoff" patches have been installed.
select
  patch_id,
  apply_time,
  rollbackable
from
  xmltable(
    '//patch'
    passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
      patch_id     number                   path 'patchID',
      apply_time   timestamp with time zone path 'appliedDate',
      rollbackable varchar2(10)             path 'rollbackable'
  );

  PATCH_ID APPLY_TIME                          ROLLBACKAB
---------- ----------------------------------- ----------
  16527374 08.11.13 14:02:02,000000 +01:00     true
  17027533 08.11.13 10:24:12,000000 +01:00     true
Looks good, doesn't it? Now we want to see the individual bugs which have been fixed by applying the patches ...
select
  patch_id,
  bug_id,
  bug_desc
from
  xmltable(
    '//patch'
     passing dbms_qopatch.GET_OPATCH_LSINVENTORY
    columns
     patch_id number path 'patchID',
     bugs xmltype path 'bugs/bug'
  ) p,
  xmltable(
    'bug'
    passing p.bugs
    columns
      bug_id number path '@id',
      bug_desc varchar2(500) path 'description'
  );

  PATCH_ID     BUG_ID BUG_DESC
---------- ---------- --------------------------------------------------------
  16527374   16527374 [12100-LIN64-130318]CDB ORA-07445 EM EXPRESS HOME GOT IO
  17027533   17034172 LX64_MAIN_CDB ORA-7445 R DUE TO RANDOM SGA CORRUPTION
  17027533   16694728 MSGQ LSM1 HITS ORA-600 [KJBLPGORM !ANTILOCK]
  17027533   16448848 LGSB WIDETAB ORA-600[17147] & ORA-600[600] & SQL
  17027533   16863422 ORA-600 [KXDAM_NOTIFY_CELL IOCTLFAILED] DURING RPM UPGRA
  17027533   16634384 LOGMINER BUILDER ASSERTS KRVUATLA20 AFTER UNREGISTERING 
         :          : :
Looking for a specific bug is now so easy, since this is a SQL result. Of course, these SQL queries could be extended very easily - all you have to do is to look into the XML documents in order to determine the XML tags actually being used. And in the next step we could create some views like "DBA_INST_PATCHES" or "DBA_FIXED_BUGS" ...

Beliebte Postings