23. April 2014

Tricks mit externen Tabellen: Unix "ps" per SELECT ausführen

External table tricks: Execute Unix "ps" command with SELECT
Heute geht es nochmals um die Möglichkeiten, die sich durch die Verwendung von externen Tabellen in der Datenbank ergeben. Mit Version 11.2 wurde ja die Möglichkeit geschaffen, ein "Präprozessor"-Kommando zu hinterlegen, welches ausgeführt wird, bevor die externe Datei tatsächlich als Tabelle ausgelesen wird. Die Standardanwendung hierfür wäre das Unix Utility gunzip - damit können externe Tabellen direkt auf .gz-Dateien erstellt werden; der Präprozessor packt sie unmittelbar vor dem Auslesen aus. So weit, so gut.
Ende 2012 hatte ich ein Blog-Posting veröffentlicht, welches zeigt, wie man mit dem Präprozessor auch eine Datei von einem Remote-System (per SSH) als externe Tabelle in die Datenbank einbinden kann; die Datei muss also gar nicht eigens auf den Datenbankserver kopiert werden.
Heute zeige ich, wie man diese Architektur nutzen kann, um Informationen als externe Tabelle aufzubereiten, die gar nicht als Datei vorliegen. Als Beispiel soll die Unix-Prozessliste dienen ...
$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Apr08 ?        00:01:58 init [3]
root         2     0  0 Apr08 ?        00:00:00 [kthreadd]
root         3     2  0 Apr08 ?        00:00:06 [migration/0]
root         4     2  0 Apr08 ?        00:00:31 [ksoftirqd/0]
root         5     2  0 Apr08 ?        00:00:00 [watchdog/0]
root         6     2  0 Apr08 ?        00:00:05 [migration/1]
:            :     :  : :     :               : :
Es ist nun schon recht naheliegend, dass dieses Kommando der "Präprozessor" sein soll - eine Datei, die vom Präprozessor umgewandelt werden soll, ist hier gar nicht nötig. Nun muss nur noch das Ausgabeformat geparst werden - und das sollte ja einfach sein ... ist es aber nicht ganz - denn das "ps"-Kommando nutzt Leerzeichen, um diese tabellenartige Formatierung zu generieren; das sieht zwar gut aus, ist aber als externe Tabelle nicht einfach zu interpretieren (die Anzahl der Leerzeichen zwischen zwei Feldern ist immer anders - und auch die "Feldpositionen" sind nicht zwingend fix). Besser wäre es, wenn die Felder durch Kommas oder Tab-Zeichen (\t) getrennt wären. Das folgende Kommando macht genau das.
$ ps -ef | awk 'OFS="\t" {$1=$1; print}'
Auf der Unix-Shell sieht die Ausgabe genauso aus. Schaut man sich den Output aber genau an, so erkennt man, dass die Felder nun durch Tabulatoren getrennt werden; und genau das brauchen wir für die externe Tabelle. Legt nun also zwei Verzeichnisse auf dem Datenbankserver an: oradir_exe und oradir_file.
$ mkdir /home/oracle/oradir_exe
$ mkdir /home/oracle/oradir_file
In das Verzeichnis oradir_exe kommt nun eine Datei namens getps mit unten stehendem Inhalt (cat getps). Im wesentlichen ist es das obige Kommando ps - aber nochmals leicht verändert, damit einige Details mehr angezeigt werden. Außerdem werden die Executables ps und awk mit ihrem kompletten Pfad angegeben. Zum Abschluß nicht vergessen, die Datei ausführbar zu machen.
$ cd /home/oracle/oradir_exe
$ cat getps
#!/bin/sh
/bin/ps -eo euser,pid,ppid,c,size,tty,time,cmd | /bin/awk 'OFS="\t" {$1=$1; print}'

$ chmod +x getps
$ ls -l
total 4
-rwxr-xr-x 1 oracle oinstall 92 Apr 23 14:47 getps
Ins Verzeichnis oradir_file kommt eine Datei namens file.txt - diese könnt Ihr mit touch erzeugen; die Inhalte (in dem Falle keine) sind völlig egal.
$ cd /home/oracle/oradir_file
$ touch file.txt
$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 23 14:34 file.txt
Damit sind die Vorbereitungen außerhalb der Datenbank abgeschlossen. Nun geht es in die Datenbank, um die externe Tabelle zu erzeugen. Der Einfachheit halber mache ich hier alles als SYS - eine Unix-Prozessliste ist sicherlich ohnehin nur für den DBA interessant. Zuerst also die Directory-Objekte erzeugen ...
drop directory oradir_exe
/

drop directory oradir_file
/

create directory oradir_exe as '/home/oracle/oradir_exe'
/

create directory oradir_file as '/home/oracle/oradir_file'
/
Dann die externe Tabelle. Man beachte die Zeile mit der PREPROCESSOR-Klausel. Hier wird eigens das Directory-Objekt ORADIR_EXE verwendet. Die Datei file.txt wird als LOCATION, also als Quelle der externen Tabelle angegeben; eine externe Tabelle wird eben so definiert. De-Facto ist diese Datei aber völlig uninteressant, denn das Executable kann seinen Output auch ohne erzeugen. Man kann auch deutlich den Vorteil erkennen, der sich dadurch ergibt, dass die Felder der Ausgabe nun durch Tabs getrennt werden: die externe Tabelle enthält nun nur noch die Klausel FIELDS TERMINATED BY '\t' .
drop table unix_ps
/

create table unix_ps(
  userid     varchar2(30),
  pid        number,
  ppid       number, 
  cpu_util   number,
  "SIZE"     number,
  tty        varchar2(10),
  "TIME"     varchar2(20),
  cmd        varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY oradir_file
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR ORADIR_EXE:'getps'
    nobadfile nologfile
    fields terminated by '\t'(
      userid   char,
      pid      char,
      ppid     char,
      cpu_util char,
      "SIZE"   char,
      tty      char,
      "TIME"   char,
      cmd      char
    )
   )
   LOCATION ('file.txt') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
Und das war's auch schon. Ihr könnt die externe Tabelle nun selektieren ...
select "CMD", "PID", "SIZE", "CPU_UTIL" 
from "UNIX_PS"
where "USERID" = 'oracle' and "CMD" like 'ora_%' 
order by "SIZE" desc;

CMD                         PID       SIZE   CPU_UTIL
-------------------- ---------- ---------- ----------
ora_dbw0_orcl              1803      10072          0
oracleorcl                 2477       5412          0
ora_pmon_orcl              1785       5116          0
ora_mmnl_orcl              1815       5056          0
oracleorcl                29308       4244          0
:                             :          :          :
Nach gleichem Schema können auch andere Informationen für SQL-Abfragen in der Datenbank verfügbar gemacht werden. Viel Spaß beim Ausprobieren.
In this blog posting, I will (again) talk about external tables and how these can be used within the database. Oracle 11.2 introduced the preprocessing capability for external tables - the external table definition is being extended with a preprocessor clause: The executable specified here will be executed, just before the external file is being read. The standard use case for this is the Unix gunzip command - using this as the executable, we can create external tables directly on compressed .gz files without unpacking them first.
In 2012, I published a blog posting which desribes how the preprocessor can be used in order to create an external table for a file residing on a remote server. In this example, the preprocessor grabs the file from the remote site using SSH. Thus, the files for external tables do not need to be placed on the database server itself.
Today I'll show, how the proprocessor feature for external tables can be used to create external tables based on the output of a Unix executable - with no static file at all. As example, I'll use the Unix ps command.
$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Apr08 ?        00:01:58 init [3]
root         2     0  0 Apr08 ?        00:00:00 [kthreadd]
root         3     2  0 Apr08 ?        00:00:06 [migration/0]
root         4     2  0 Apr08 ?        00:00:31 [ksoftirqd/0]
root         5     2  0 Apr08 ?        00:00:00 [watchdog/0]
root         6     2  0 Apr08 ?        00:00:05 [migration/1]
:            :     :  : :     :               : :
It's obvious, that the "ps" command will be the "preprocessor" in this case. We'll just have no file to be preprocessed; the executable will generate all the output itself. The output of the "ps" command looks nicely like a table ... but the formatting is done only with blanks - and this makes it really hard to create an external table definition for. The amount of blanks varies with the output itself and the field positions are variable as well. It would be much easier to have commas or Tab ( \t) characters as field separators - the following Unix sequence does the job for us.
$ ps -ef | awk 'OFS="\t" {$1=$1; print}'
Executed on the Unix shell, we hardly see a difference - but since the fields are not being separated by "tab", our external table definition will be much more easy. Now, let's create the required structures on the operating system side. First, we need to directories: oradir_exe and oradir_file.
$ mkdir /home/oracle/oradir_exe
$ mkdir /home/oracle/oradir_file
In oradir_exe we'll place a file named getps with the content shown below (cat getps). Basically it is the above mentioned ps command, but modified a bit in order to see more information. The used executables ps and awk are furthermore extended to their full path (which is required for the external table preprocessor). Finally, don't forget to make the file executable (chmod +x).
$ cd /home/oracle/oradir_exe
$ cat getps
#!/bin/sh
/bin/ps -eo euser,pid,ppid,c,size,tty,time,cmd | /bin/awk 'OFS="\t" {$1=$1; print}'

$ chmod +x getps
$ ls -l
total 4
-rwxr-xr-x 1 oracle oinstall 92 Apr 23 14:47 getps
In oradir_file we place a file named file.txt - you can create it with the touch command. This file must just exist - its contents are totally irrelevant.
$ cd /home/oracle/oradir_file
$ touch file.txt
$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 23 14:34 file.txt
This concludes the preparations on the operating system side. The next steps are being executed within the database. For simplicily I have done all steps as SYS. First, we'll create the Oracle directory objects corresponding to the Unix folders.
drop directory oradir_exe
/

drop directory oradir_file
/

create directory oradir_exe as '/home/oracle/oradir_exe'
/

create directory oradir_file as '/home/oracle/oradir_file'
/
Then the external table itself is being created. Note the PREPROCESSOR clause. It references the getps script we created before and the directory object it resides in. The other directory object and the file file.txt are referenced in the LOCATION clause, since an external table requires a file to read from. But in our case, the preprocessor does all the work. We can also see the benefit we have gained, by changing the field separators to tabs: we can just declare the FIELDS to be TERMINATED BY '\t' . The plain ps -ef output would be much more difficult to parse.
drop table unix_ps
/

create table unix_ps(
  userid     varchar2(30),
  pid        number,
  ppid       number, 
  cpu_util   number,
  "SIZE"     number,
  tty        varchar2(10),
  "TIME"     varchar2(20),
  cmd        varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY oradir_file
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR ORADIR_EXE:'getps'
    nobadfile nologfile
    fields terminated by '\t'(
      userid   char,
      pid      char,
      ppid     char,
      cpu_util char,
      "SIZE"   char,
      tty      char,
      "TIME"   char,
      cmd      char
    )
   )
   LOCATION ('file.txt') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
And that's it - the external table can be used (as always) by selecting it ...
select "CMD", "PID", "SIZE", "CPU_UTIL" 
from "UNIX_PS"
where "USERID" = 'oracle' and "CMD" like 'ora_%' 
order by "SIZE" desc;

CMD                         PID       SIZE   CPU_UTIL
-------------------- ---------- ---------- ----------
ora_dbw0_orcl              1803      10072          0
oracleorcl                 2477       5412          0
ora_pmon_orcl              1785       5116          0
ora_mmnl_orcl              1815       5056          0
oracleorcl                29308       4244          0
:                             :          :          :
And with the same approach, much more operating system information can be made available to SQL queries. Have fun trying it out.

2. April 2014

Veranstaltung "Unstrukturierte Daten in Oracle" im Mai 2014

Im Mai 2014 führen meine Kollegin Ulrike Schwinn und ich eine Veranstaltung (nicht nur) für SQL und PL/SQL Entwickler statt. Diesmal geht es um unstrukturierte Daten in der Oracle-Datenbank - das bedeutet, es werden Large Objects (BLOB, CLOB), XML und Oracle TEXT betrachtet - alles inkl. Oracle12c Update. 

Vielleicht sieht man sich ...? Wir freuen uns drauf.


Mai 2014: SQL and beyond

Unstrukturierte Daten in Oracle12c


Unstrukturierte Daten wie Bilder, Dokumente und Dateien sind neben den relationalen Tabellendaten normaler Bestandteil nahezu jeder Anwendung. Meist werden diese der Einfachheit halber ins Dateisystem gelegt, obwohl die Speicherung in der Datenbank viele Vorteile mit sich bringt.

Erfahren Sie auf diesem Oracle Database Day, welche Möglichkeiten Ihnen die neue Datenbankversion 12c zur Speicherung von und zum Umgang mit unstrukturierten Daten bietet. Dabei wird auf grundsätzliche Dinge wie das Large-Object-Management ebenso eingegangen wie auf die konkrete Nutzung der Daten durch XML-Funktionen, Volltextindizierung und mehr ...

Die Teilnahme an der Veranstaltung ist kostenlos. Melden Sie sich am besten gleich an.


Termine

  • 06.05.2014:
    Oracle Hauptverwaltung München
    Riesstr. 25
    D-80992 München (Anfahrt)

    [Anmeldung]
  • 07.05.2014:
    Oracle Niederlassung Frankfurt
    Robert-Bosch-Str. 5
    D-63303 Dreieich (Anfahrt)

    [Anmeldung]
  • 08.05.2014:
    Oracle Niederlassung in Hamburg
    Kühnehöfe 5
    D-22761 Hamburg (Anfahrt)

    [Anmeldung]

Agenda


11:30Registrierung & Kaffee
12:00Beginn der Veranstaltung
Unstrukturierte Daten in Oracle12c speichern
LOB-Management, Kompression, Performanceaspekte, ...
Mittagspause
Umgang mit XML in der Oracle-Datenbank
XMLTYPE in Oracle12c, Performance, XML-Standards (XQuery Fulltext), ...
Oracle TEXT: Neues in Oracle12c
Near Realtime Index, Automatische Spracherkennung, neue Query Operatoren, ...
16:00Fragen/Diskussion & Ende der Veranstaltung
This blog posting is about an event in Germany in german language ... and therefore in german only.

18. März 2014

Parallele Ausführung von Table Functions

Parallel Execution of Table Functions
Heute möchte ich etwas zum Thema Table Functions und deren parallele Ausführung schreiben. Im Zusammenspiel mit einem Kunden habe ich hier eine interessante Erfahrung gemacht - und die möchte ich gerne teilen.
Als Beispiel für eine Table Function nehme ich den String Tokenizer für PL/SQL, den ich vor einiger Zeit auf diesem Blog veröffentlicht habe. Nun braucht es eine Beispieltabelle, auf der wir arbeiten können: Das Schema SH enthält die Tabelle SUPPLEMENTARY_DEMOGRAPHICS, welche in der Spalte COMMENTS einige kleine Fließtexte enthält, die man sehr schön mit dem String Tokenizer zerlegen kann. Diese Tabelle duplizieren wir nun ein wenig ...
create table my_texte as select comments from SUPPLEMENTARY_DEMOGRAPHICS
/

insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);

select count(*) from my_texte
/

  COUNT(*)
----------
     72000
Darauf lässt sich nun arbeiten. Also nun der erste Test: Wieviele Tokens sind es insgesamt ...?
select count(*) from (
  select
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.61
Es sind knapp 1,5 Mio. Tokens und zum Zählen braucht (diese) Datenbank 7,6 Sekunden. Nun ist das ja eine Aufgabe, die sich eigentlich sehr gut parallelisieren lassen sollte (diese Maschine hat auch die nötige Anzahl CPUs) - probieren wir es ...
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.86
Das war wohl nix. Aber moment: Table Functions haben noch das Syntaxelement PARALLEL_ENABLE, welches im PL/SQL Code gesetzt sein sollte - im vorliegenden Quellcode ist das nicht der Fall. Also ändern wir im Type STRING_TOKENIZER (vorerst nur dort) die Funktionsdeklaration von ALL_TOKENS wie folgt ...
create type string_tokenizer as object(
  
  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
 is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;
end;
/
sho err
Erneuter Test ...
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:07.97
Schaut man sich den Ausführungsplan genau an, so steht in den "Notes" zwar drin, dass er parallelisiert, tatsächlich merkt man aber nichts davon. Nun konnte ich aber nicht glauben, dass die Datenbank eine solche Abfrage nicht parallelisieren kann. Table Functions wurden mit Oracle9i eingeführt - und deren ursprüngliche Anwendungsgebiete waren Ladeprozesse ins Data Warehouse. Hier wurden oft Table Functions eingesetzt, die einen CURSOR als Parameter entgegennehmen - und genau das wenden wir nun mal auch für den STRING_TOKENIZER an. Der PL/SQL-Code wird erweitert, so dass der Type eine neue Member-Funktion ALL_TOKENS_CUR bekommt.
create type string_tokenizer as object(

  :
  :

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) 
  return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens_cur (
    p_cursor in sys_refcursor, p_delim in varchar2
  ) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Die neue Funktion ALL_TOKENS_CUR arbeitet im Prinzip genauso wie die vorhandene Funktion ALL_TOKENS - allerdings bekommt diese einen Cursor übergeben, der in der Funktion ausgelesen (fetch) wird. Neben dem PARALLEL_ENABLE-Schlüsselwort sollte bei einem Input-Parameter vom Typ Cursor nun auch ein PARTITION BY angegeben werden. Mit dieser Klausel sagt man dem Optimizer, wie er die Ergebnismenge des Cursors auf die Parallel Query Slaves aufteilen soll. Mit ANY lässt man den Optimizer quasi selbst entscheiden. Da die neue Funktion eine andere Schnittstelle hat, müssen wir auch die SQL Query umschreiben ...
select count(*) from (
  select /*+ PARALLEL(4)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:02.04
Na, und das sieht doch ganz anders aus .. schon an der Ausführungszeit erkennt man, dass hier parallelisiert wurde. Nochmal den Test mit einem PARALLEL(8) ...
select count(*) from (
  select /*+ PARALLEL(8)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:01.05
Es kann also durchaus eine gute Idee sein, eine Table-Funktion so zu schreiben, dass sie einen CURSOR als Parameter entgegennimmt. Der Vollständigkeit halber findet Ihr hier nochmal den kompletten Code des PL/SQL String Tokenizers inklusive der neuen CURSOR-Funktionen.
drop type clob_tokenizer
/

drop type string_tokenizer
/

drop type token_list
/

drop type token_t
/

create type token_t as object(
  token_text varchar2(4000),
  start_pos  number,
  length     number
)
/

create type token_list as table of token_t
/

create type clob_tokenizer as object(
  value_string       clob,
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result,
  member function has_more_tokens return number,
  member function next_token(self in out nocopy clob_tokenizer) return varchar2,
  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body clob_tokenizer is
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens return number as
  begin
    if self.parser_current_pos <= dbms_lob.getlength(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy clob_tokenizer) return varchar2 is
    l_next_delim_pos   number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := dbms_lob.instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => (dbms_lob.getlength(self.value_string) - self.parser_current_pos) + 1,  
          offset  => self.parser_current_pos
        );
        parser_current_pos := dbms_lob.getlength(self.value_string) + 1; 
      else 
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => l_next_delim_pos  - self.parser_current_pos, 
          offset  => self.parser_current_pos
        );
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st clob_tokenizer := clob_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       clob_tokenizer;
    l_string   clob;
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := clob_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;

end;
/
sho err

create type string_tokenizer as object(
  value_string       varchar2(4000),
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result,
  member function has_more_tokens(self in out nocopy string_tokenizer) return number,
  member function next_token(self in out nocopy string_tokenizer) return varchar2,
  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens(self in out nocopy string_tokenizer) return number as
  begin
    if self.parser_current_pos <= length(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy string_tokenizer) return varchar2 as
    l_next_delim_pos   number;
    l_next_enclose_pos number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := substr(value_string, self.parser_current_pos);
        parser_current_pos := length(self.value_string) + 1; 
      else 
        l_token := substr(self.value_string, self.parser_current_pos, l_next_delim_pos  - self.parser_current_pos);
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Viel Spaß beim Ausprobieren.
Today I'd like to write something on Table Functions and their parallel execution. Together with a customer I had a very interesting experience, which I'd like to share.
We'll take the String Tokenizer for PL/SQL, which I published on this blog some time ago, as an example for a table function. Next, we need some data: The sample schema SH contains the table SUPPLEMENTARY_DEMOGRAPHICS and its column COMMENTS contains some nice texts which we'll shred with the String Tokenizer. This is a task which should be very easy to parallelize. In order to really see an effect, we'll duplicate the table rows a few times ...
create table my_texte as select comments from SUPPLEMENTARY_DEMOGRAPHICS
/

insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);
insert into my_texte (select * from my_texte);

select count(*) from my_texte
/

  COUNT(*)
----------
     72000
Ok ... lets's start then. First, we want to see, how man tokens there are and how long it takes.
select count(*) from (
  select
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:07.61
So, we habe about 1.500.000 tokens and a single thread needs (on this machine) about 7.5 seconds to count them. Now, since this kind of task should parallelize very easily, let's try it (assumed, that the database machine has the needed amount of CPUs).
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:07.86
No real effect. But one moment: Looking into the PL/SQL documentation for table functions, we can see that there is the PARALLEL_ENABLE keyword which we need to add to the function declaration. So - we adjust the PL/SQL code of the STRING_TOKENIZER type (at the moment only this one) as follows ...
create type string_tokenizer as object(
  
  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens (p_string in varchar2, p_delim in varchar2) 
  return token_list pipelined parallel_enable
 is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;
end;
/
sho err
Testing ...
select count(*) from (
  select /*+ PARALLEL(4) */
    tk.token_text
  from 
    my_texte t,
    table(sh.string_tokenizer.all_tokens(comments, ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:07.97
The execution plan tells us (in the Notes Section), that there was a parallel degree - but looking at the execution times, we don't see a real effect. I did not want to accept that the database is unable to parallelize this kind of task - so I investigated a bit of time into rewriting the table functions. Table Functions were introducted in Oracle9i - and its primary focus area was the Loading of Data Warehouses. In these scenarions, Table Functions often take a cursor as the input parameter - so I tried this approach also for the String Tokenizer. We'll add a new MEMBER function to the type declaration which does basically the same as the ALL_TOKENS method, but taking a SYS_REFCURSOR as input.
create type string_tokenizer as object(

  :
  :

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) 
  return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is

  :
  :

  static function all_tokens_cur (
    p_cursor in sys_refcursor, p_delim in varchar2
  ) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
In addition to the PARALLEL_ENABLE keyword we can also add the PARTITION BY clause, in which we can determine how the optimizer should distribute the cursor result set to the parallel query slaves. We choose ANY, which basically lets the optimizer decide. Since this function has a different signature, we also need to rewrite our SQL query.
select count(*) from (
  select /*+ PARALLEL(4)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:02.04
Ok .. this looks good. Seems that the parallelization now works. Let's do the second test with PARALLEL(8).
select count(*) from (
  select /*+ PARALLEL(8)*/
    tk.token_text
  from 
    table(sh.string_tokenizer.all_tokens_cur(cursor(select comments from my_texte), ' ')) tk
)
/

  COUNT(*)
----------
   1499824

1 row selected.

Elapsed: 00:00:01.05
Note that this will work only, if you really have the necessary CPU resources on your system. If there is only one CPU available, you will see differenz numbers. But we can see, that it can be a very good idea to rewrite a table function to use Cursors as input arguments. The parallelization behaviour for large amounts of data is much better. For the records: Here is the complete STRING_TOKENIZER and CLOB_TOKENIZER PL/SQL code including the new ALL_TOKENS_CUR functions.
drop type clob_tokenizer
/

drop type string_tokenizer
/

drop type token_list
/

drop type token_t
/

create type token_t as object(
  token_text varchar2(4000),
  start_pos  number,
  length     number
)
/

create type token_list as table of token_t
/

create type clob_tokenizer as object(
  value_string       clob,
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result,
  member function has_more_tokens return number,
  member function next_token(self in out nocopy clob_tokenizer) return varchar2,
  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body clob_tokenizer is
  constructor function clob_tokenizer (p_string in clob, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens return number as
  begin
    if self.parser_current_pos <= dbms_lob.getlength(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy clob_tokenizer) return varchar2 is
    l_next_delim_pos   number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := dbms_lob.instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => (dbms_lob.getlength(self.value_string) - self.parser_current_pos) + 1,  
          offset  => self.parser_current_pos
        );
        parser_current_pos := dbms_lob.getlength(self.value_string) + 1; 
      else 
        l_token := dbms_lob.substr(
          lob_loc => self.value_string, 
          amount  => l_next_delim_pos  - self.parser_current_pos, 
          offset  => self.parser_current_pos
        );
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in clob, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st clob_tokenizer := clob_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       clob_tokenizer;
    l_string   clob;
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := clob_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;

end;
/
sho err

create type string_tokenizer as object(
  value_string       varchar2(4000),
  delimiter          varchar2(10),
  parser_current_pos number,
  last_token         varchar2(4000),
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result,
  member function has_more_tokens(self in out nocopy string_tokenizer) return number,
  member function next_token(self in out nocopy string_tokenizer) return varchar2,
  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable,
  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any)
);
/
sho err

create or replace type body string_tokenizer is
  constructor function string_tokenizer (p_string in varchar2, p_delim in varchar2) return self as result as
  begin
    self.value_string := p_string;
    self.delimiter := p_delim;
    self.parser_current_pos := 1;
    self.last_token := null;
    return ;
  end;
    
  member function has_more_tokens(self in out nocopy string_tokenizer) return number as
  begin
    if self.parser_current_pos <= length(value_string) then 
      return 1;
    else 
      return 0;
    end if;
  end;

  member function next_token(self in out nocopy string_tokenizer) return varchar2 as
    l_next_delim_pos   number;
    l_next_enclose_pos number;
    l_token            varchar2(4000);
  begin
    if self.has_more_tokens() = 1 then 
      l_next_delim_pos := instr(self.value_string, self.delimiter, self.parser_current_pos);
      if l_next_delim_pos = 0 then
        l_token := substr(value_string, self.parser_current_pos);
        parser_current_pos := length(self.value_string) + 1; 
      else 
        l_token := substr(self.value_string, self.parser_current_pos, l_next_delim_pos  - self.parser_current_pos);
        parser_current_pos := l_next_delim_pos + length(self.delimiter);
      end if;
    else 
      l_token := null;
    end if;
    self.last_token := l_token;
    return l_token;
  end;

  static function all_tokens (p_string in varchar2, p_delim in varchar2) return token_list pipelined parallel_enable is
    l_st string_tokenizer := string_tokenizer(p_string, p_delim);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    while l_st.has_more_tokens = 1 loop
      l_startpos := l_st.parser_current_pos;
      l_token := l_st.next_token();
      pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
    end loop;
    return;
  end;

  static function all_tokens_cur (p_cursor in sys_refcursor, p_delim in varchar2) return token_list pipelined parallel_enable (partition p_cursor by any) is
    l_st       string_tokenizer;
    l_string   varchar2(4000);
    l_startpos number;
    l_token    varchar2(4000);
  begin
    loop
      fetch p_cursor into l_string;  
      exit when p_cursor%notfound;
     
      l_st := string_tokenizer(l_string, p_delim);
      while l_st.has_more_tokens = 1 loop
        l_startpos := l_st.parser_current_pos;
        l_token := l_st.next_token();
        pipe row (token_t(l_token, l_startpos, nvl(length(l_token),0)));
      end loop;
    end loop;
    return;
  end;
end;
/
sho err
Have fun!

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