22. Februar 2010

Statistische Funkionen: Alles auf einmal mit DBMS_STAT_FUNCS

English title: Statistic functions: DBMS_STAT_FUNCS

Die Tage habe ich eine nette PL/SQL-Funktion gefunden, die "auf einen Rutsch" alle möglichen Aggregate für eine Tabellenspalte berechnet - sie ist im Package DBMS_STAT_FUNCS (Dokumentation). Am besten guckt man sich das am Beispiel an.
These days I found a nice litte function which computes various aggregates for a (numeric) table column at once. It's contained in the package DBMS_STAT_FUNCS (Documentation). Let's have a look ...
SQL> desc DBMS_STAT_FUNCS

PROCEDURE SUMMARY
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_OWNERNAME                    VARCHAR2                IN
 P_TABLENAME                    VARCHAR2                IN
 P_COLUMNNAME                   VARCHAR2                IN
 P_SIGMA_VALUE                  NUMBER                  IN     DEFAULT
 S                              RECORD                  OUT
   COUNT                        NUMBER                  OUT
   MIN                          NUMBER                  OUT
   MAX                          NUMBER                  OUT
   RANGE                        NUMBER                  OUT
   MEAN                         NUMBER                  OUT
   CMODE                        NUM_TABLE               OUT
   VARIANCE                     NUMBER                  OUT
   STDDEV                       NUMBER                  OUT
   QUANTILE_5                   NUMBER                  OUT
   QUANTILE_25                  NUMBER                  OUT
   MEDIAN                       NUMBER                  OUT
   QUANTILE_75                  NUMBER                  OUT
   QUANTILE_95                  NUMBER                  OUT
   PLUS_X_SIGMA                 NUMBER                  OUT
   MINUS_X_SIGMA                NUMBER                  OUT
   EXTREME_VALUES               NUM_TABLE               OUT
   TOP_5_VALUES                 N_ARR                   OUT
   BOTTOM_5_VALUES              N_ARR                   OUT
Da wird ein RECORD-Datentyp zurückgegeben; zum Ausprobieren muss man also ein wenig PL/SQL drumherumbauen ...
The result is an OUT parameter of a RECORD type. So test it we need to wrap a bit of PL/SQL around ...
set serveroutput on

declare
  v_sum  dbms_stat_funcs.summaryType;
  v_extv dbms_stat_funcs.num_table;
  v_topn dbms_stat_funcs.n_arr;
  v_botn dbms_stat_funcs.n_arr;
begin
  dbms_stat_funcs.summary(
    P_OWNERNAME   => user,
    P_TABLENAME   => 'CUSTOMERS',
    P_COLUMNNAME  => 'CUST_CREDIT_LIMIT',
    S             => v_sum
  );


  dbms_output.put_line('COUNT:             '||v_sum.COUNT);
  dbms_output.put_line('MIN:               '||v_sum.MIN);
  dbms_output.put_line('MAX:               '||v_sum.MAX);
  dbms_output.put_line('RANGE:             '||v_sum.RANGE);
  dbms_output.put_line('MEAN:              '||v_sum.MEAN);
  for i in v_sum.cmode.first..v_sum.cmode.last loop
    dbms_output.put_line('CMODE['||i||']:          '||v_sum.CMODE(i));
  end loop;
  dbms_output.put_line('VARIANCE:          '||v_sum.VARIANCE);
  dbms_output.put_line('STDDEV:            '||v_sum.STDDEV);
  dbms_output.put_line('QUANTILE_5:        '||v_sum.QUANTILE_5);
  dbms_output.put_line('QUANTILE_25:       '||v_sum.QUANTILE_25);
  dbms_output.put_line('MEDIAN:            '||v_sum.MEDIAN);
  dbms_output.put_line('QUANTILE_75:       '||v_sum.QUANTILE_75);
  dbms_output.put_line('QUANTILE_95:       '||v_sum.QUANTILE_95);
  dbms_output.put_line('PLUS_X_SIGMA:      '||v_sum.PLUS_X_SIGMA);
  dbms_output.put_line('MINUS_X_SIGMA:     '||v_sum.MINUS_X_SIGMA);
  for i in 1..least(v_sum.extreme_values.count, 9) loop
    dbms_output.put_line('EXTREME_VALUES['||i||']: '||v_sum.EXTREME_VALUES(i));
  end loop;
  for i in v_sum.top_5_values.first..v_sum.top_5_values.last loop
    dbms_output.put_line('TOP_VALUES['||i||']:     '||v_sum.TOP_5_VALUES(i));
  end loop;
  for i in v_sum.bottom_5_values.first..v_sum.bottom_5_values.last loop
    dbms_output.put_line('BOTTOM_VALUES['||i||']:  '||v_sum.BOTTOM_5_VALUES(i));
  end loop;
end;
/

COUNT:             55500
MIN:               1500
MAX:               15000
RANGE:             13500
MEAN:              6153,261261261261261261261261261261261261
CMODE[1]:          1500
VARIANCE:          12936897,23619725962110509579833351432837
STDDEV:            3596,789851547802009993634287000834918826
QUANTILE_5:        1500
QUANTILE_25:       3000
MEDIAN:            7000
QUANTILE_75:       9000
QUANTILE_95:       11000
PLUS_X_SIGMA:      16943,6308159046672912421641222637660178
MINUS_X_SIGMA:     -4637,108293382144768719641599741243495239
TOP_VALUES[1]:     15000
TOP_VALUES[2]:     15000
TOP_VALUES[3]:     15000
TOP_VALUES[4]:     15000
TOP_VALUES[5]:     15000
BOTTOM_VALUES[1]:  1500
BOTTOM_VALUES[2]:  1500
BOTTOM_VALUES[3]:  1500
BOTTOM_VALUES[4]:  1500
BOTTOM_VALUES[5]:  1500
Das man das alles mit SQL auch selbst bauen kann, ist ja klar - aber welches SQL setzt die Datenbank hier im Hintergrund ab ...? Naja - kein Problem: Finden wir's raus ...
It's obvious that you could also write your own SQL in order to get those results. But I'm now keen on the actual SQL the database uses here ... Now let's trace it ...
SQL> alter session set sql_trace = true;

Session altered.

SQL> @summary.sql

SQL> exit
Mit dem ALTER SESSION-Kommando wird ein SQL Trace aktiviert - die Datenbank schreibt also alles SQL, was im Hintergrund abgesetzt wird, in eine Tracedatei. Jene Tracedatei liegt dann auf dem Datenbankserver in der sog. user-dump-destination. Um da ranzukommen, muss man nun also Zugriff auf den Datenbankserver haben ... (oder dieses Posting hier lesen). Hat man die Datei, so macht man sie zuerst mit dem tkprof-Werkzeug lesbar ...
The ALTER SESSION command starts a SQL trace. The database will now write each SQL statement which is being executed in the background into a tracefile (actually not only the SQL statement but also additional information). This tracefile is located in the user-dump-destination on the database server's filesystem. To get it you need access to the database server (or read this blog posting). Convert the file into a readable format with the tkprof utility.
$ tkrpof orcl_ora_2672.trc output.txt
Den zweiten Dateinamen könnt Ihr frei wählen. Wenn Ihr dann die Datei output.txt mit einem Texteditor öffnet und euch darin ein wenig umseht, solltet Ihr das SQL recht schnell finden ...
You can choose a random filename as the second parameter. Now open the file output.txt with a text editor and look around - you should find the background SQL quickly ...
with aux_summary as (
  select
    count(CUST_CREDIT_LIMIT) as count_V ,
    min(CUST_CREDIT_LIMIT) as min_V,    
    max(CUST_CREDIT_LIMIT) as max_V,
    max(CUST_CREDIT_LIMIT)- min(CUST_CREDIT_LIMIT) as range_V,    
    avg(CUST_CREDIT_LIMIT) as mean_V,
    variance(CUST_CREDIT_LIMIT) as  variance_V, 
    stddev(CUST_CREDIT_LIMIT) as stddev_V,    
    percentile_cont(0.05) within group (order by CUST_CREDIT_LIMIT) as quantile_5_V,    
    percentile_cont(0.25) within group (order by CUST_CREDIT_LIMIT) as quantile_25_V,
    median(CUST_CREDIT_LIMIT) as median_V,
    percentile_cont(0.75) within group (order by CUST_CREDIT_LIMIT) as quantile_75_V,
    percentile_cont(0.95) within group (order by CUST_CREDIT_LIMIT) as quantile_95_V,
    (avg(CUST_CREDIT_LIMIT) + :1 * stddev(CUST_CREDIT_LIMIT)) as plus_x_sigma_V,
    (avg(CUST_CREDIT_LIMIT) - :1 * stddev(CUST_CREDIT_LIMIT)) as minus_x_sigma_V
  from "SH".CUSTOMERS
) 
:
Das SQL ist etwas länger .. zum Ermitteln der Extreme Values und der Top- und Bottom-5-Values muss er etwas komplexeres SQL bauen. Aber man sieht sehr schön, was man mit SQL-Standardfunktionen so alles anfangen kann ...
This SQL query is a bit more complex - for calculating the extreme values the top- and bottom-5-values the database needs to do some complex query. But it's very nice to see what's possible with standard SQL.

8. Februar 2010

Betriebssystem-Zugriff mit SQL und PLSQL: Neue Version verfügbar

New version 0.9 available: Executing OS commands from the database

Nach über einem Jahr habe ich heute eine neue Version des PL/SQL-Pakets für den Zugriff auf Dateien, Verzeichnisse und Betriebssystem-Kommandos veröffentlicht. Download wie immer über die Projektseite auf sourceforge.net. Neue Features sind:
  • Neue Pipelined Functions für das Listing von Verzeichnisinhalten: Wesentlich bessere Performance
  • Komplexe Shell-Kommandos (besonders auf Unix wichtig) werden nun unterstützt; siehe OS_COMMAND.SET_EXEC_IN_SHELL.
  • Eine SQL Developer-Erweiterung ist nun ebenfalls dabei.
The last version was more than a year ago - today I released the new version 0.9 of my package for file access and operating system interaction. You can download it from the project website at sourceforge.net. Some new features:
  • There are new Pipelined Functions for directory listings - these have better performance and are more memory-efficient.
  • Complex Shell-Commands (which are common on Unix platforms) are now supported - see the OS_COMMAND.SET_EXEC_IN_SHELL call.
  • An extension for Oracle SQL Developer is also included.
Was ist das Besondere an diesem Paket - es gibt doch UTL_FILE? Ganz einfach: Schaut euch das hier an:
Why such a package - there is UTL_FILE? The answer is below:
SQL> select file_name, file_size, last_modified 
  2  from table(file_pkg.get_file_list_p(file_pkg.get_file('/'))) 
  3  order by 1;

FILE_NAME                       FILE_SIZE LAST_MODIFIED
------------------------------ ---------- ----------------
.autofsck                               0 08.02.2010 09:42
bin                                  4096 18.01.2010 18:20
boot                                 1024 18.01.2010 18:17
dev                                  3240 08.02.2010 09:46
etc                                  4096 08.02.2010 09:46
home                                 4096 18.01.2010 18:20
lib                                  4096 18.01.2010 18:20
lost+found                          16384 05.05.2009 20:17
media                                4096 09.01.2009 02:19
misc                                    0 08.02.2010 09:42
mnt                                  4096 09.01.2009 02:19
net                                     0 08.02.2010 09:42
opt                                  4096 08.02.2010 09:43
proc                                    0 08.02.2010 09:41
root                                 4096 08.02.2010 10:16
sbin                                12288 18.01.2010 18:20
selinux                                 0 08.02.2010 09:41
srv                                  4096 09.01.2009 02:19
stage                                   5 10.11.2009 14:10
swapfile                       1610612736 08.02.2010 09:43
sys                                     0 08.02.2010 09:41
tmp                                  4096 08.02.2010 11:16
usr                                  4096 18.01.2010 18:17
var                                  4096 18.01.2010 18:18
wget-log                              495 08.02.2010 09:46

25 Zeilen ausgewählt.
Feedback ist wie immer sehr erwünscht ...
Feedback is very appreciated ...

1. Februar 2010

PL/SQL-gesteuerte Import-Operationen mit der Data Pump

English title: Import operations using PL/SQL and DBMS_DATAPUMP

Im letzten Blog Posting hatte ich die Möglichkeit vorgestellt, Export-Vorgänge mit dem Paket DBMS_DATAPUMP aus PL/SQL heraus zu starten. Heute zeige ich ein analoges Beispiel für den Import. Voraussetzung ist, dass die Export-Datei schon vorhanden ist. Es muss eine Data Pump(!) Exportdatei sein, mit "klassischen" Dumpfiles wird die heute vorgestellte Prozedur nicht funktionieren.
In the previous blog posting I showed how to initiate an export operation with PL/SQL code using the PL/SQL package DBMS_DATAPUMP. In this posting I'll show how to perform the corresponding import operation. A prerequisite for today a an existing data pump(!) export dumpfile. This procedure will not work with "classic" export dumpfiles.
Wie beim letzten Mal muss ein Directory-Objekt vorhanden sein - die Data Pump arbeitet ja serverseitig.
As in the previous blog posting you need a directory object. Data Pump works at the server side.
create directory dp_test_dir as '/home/oracle'
/
Dann kommt die PL/SQL-Prozedur, die den Dump mit Hilfe von DBMS_DATAPUMP importiert. Als Parameter geben wir den Dateinamen und die zu importierende Tabelle an. Wenn das Dumpfile mehr als diese Tabelle enthält, wird trotzdem nur die angegebene Tabelle importiert. Wichtig hier ist der Abschnitt, in dem die TABLE_EXISTS_ACTION festgelegt wird: Wir geben hier an, dass die Zeilen dann hinzugefügt (APPEND) werden sollen. Andere Varianten wären SKIP oder REPLACE.
The next step is the PL/SQL procedure which imports the dumpfile using DBMS_DATAPUMP. The procedure expects the filename as well as the name of the table to be imported as its parameters. Only the specified tables are being imported. The section where the TABLE_EXISTS_ACTION is being specified is interesting. This determined what the data pump API should do if the table to be imported is already there. We choose APPEND in this case - which means that the rows in the import dumpfile are being appended to the table. Other options would be SKIP or REPLACE.
create or replace procedure import_file(
  p_file_name in varchar2,
  p_table_name in varchar2
)
is
  v_dp_handle number;
  pragma AUTONOMOUS_TRANSACTION; 
begin
    -- Create Data Pump Handle - "TABLE IMPORT" in this case
    v_dp_handle := dbms_datapump.open(
      operation      => 'IMPORT',
      job_mode       => 'TABLE'
    );
    
  -- Specify target file - %U adds a unique number to the file name
  dbms_datapump.add_file(
    handle         => v_dp_handle,
    filename       => p_file_name,
    directory      => 'DP_TEST_DIR'
  );

  -- Export only those tables specified in the procedure's parameter
  dbms_datapump.metadata_filter(
    handle         => v_dp_handle,
    name           => 'NAME_LIST',
    value          => p_table_name
  );

  -- IMPORTANT: Specify the "table exists" action. In our case the 
  -- rows should be appended to the existing ones. Other action types
  -- would be "REPLACE" or "SKIP"
  dbms_datapump.set_parameter(
    handle         => v_dp_handle,
    name           => 'TABLE_EXISTS_ACTION',
    value          => 'APPEND'
  );

  -- Do it!
  dbms_datapump.start_job(
    handle        => v_dp_handle
  );
  commit;
end;
/
sho err
Wichtig ist wieder das COMMIT am Ende (wegen der autonomen Transaktion, welche DBMS_DATAPUMP erwartet). Dann testen wir mal ...
The COMMIT at the end is important (as well as with the export operation). This is due to the autonomous transaction which DBMS_DATAPUMP requires. Let's try ...
SQL> select * from emp
              *
FEHLER in Zeile 1:
ORA-00942: Tabelle oder View nicht vorhanden

SQL> exec IMPORT_FILE('EMP_20100105-043120.dmp','EMP');

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.80   800           20
 7499 ALLEN      SALESMAN   7698 20.02.81  1600   300     30
 7521 WARD       SALESMAN   7698 22.02.81  1250   500     30
    : :          :             : :            :     :      :

14 rows selected.

SQL> exec IMPORT_FILE('EMP_20100105-043120.dmp','EMP');

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
 7369 SMITH      CLERK      7902 17.12.80   800           20
 7499 ALLEN      SALESMAN   7698 20.02.81  1600   300     30
 7521 WARD       SALESMAN   7698 22.02.81  1250   500     30
    : :          :             : :            :     :      :

28 rows selected.
Auch hier ergeben sich interessante Möglichkeiten: Dumpfiles können nun (bspw. mit APEX) eingespielt werden, ohne dass man Betriebssystem-Zugriff auf den Datenbankserver benötigt. Man müsste nur das Dumpfile über eine Web-Applikation hochladen - den BLOB dann mit UTL_FILE in das Verzeichnis schreiben und mit DBMS_DATAPUMP dann einspielen. Die Data Pump erlaubt hier wesentlich flexiblere Möglichkeiten als das "alte" Export/Import. DBMS_DATAPUMP bietet noch zahlreiche Möglichkeiten, den Vorgang zu parametrisieren: So können neben Schemanamen auch Storage-Informationen beim Import angepasst werden. Ein altes Problem beim "klassischen" Export/Import war, wenn die Tabellen auf dem Zielsystem in einem anderen Tablespace liegen sollten. Das geht bei der Data Pump mit REMAP_TABLESPACE ganz einfach.
With this approach dumpfile can be imported using a Web GUI (say: APEX). The file could be uploaded via a browser - the BLOB needs then to be written to the file system with UTL_FILE and finally the import process can be triggered with DBMS_DATAPUMP. Operating system access to the database server is no longer necessary. Data Pump offers beyond this much more flexibility than the "classic" export/import: There are several options to parametrize the process. For instance: An "old" and well known problem of "classic" export/import is that the tablespace for a table changes in the target system. Data Pump handles this very smoothly with REMAP_TABLESPACE.
Viel Spaß damit!
Have fun!

Beliebte Postings