28. Januar 2009

Wie groß ist eigentlich eine Tabelle ...?

English title: How big is a table ...?

Jüngst habe ich ein paar Tests mit den in 11g neuen Möglichkeiten zur Kompression von Tabellen gemacht. Und dabei wollte ich natürlich wissen, wie groß die Tabellen jeweils komprimiert und unkomprimiert sind. Während ich mich da durchgearbeitet habe, stellte ich fest, dass zum Ermitteln der tatsächlichen Tabellengröße mehr als eine einfache Abfrage nötig ist. Es lohnt sich auf jeden Fall, für eine generische Prozedur etwas mehr Aufwand zu investieren ...
These days I made some experiments with the new table compression feature in Oracle11g. And I wanted (of course) to know about the effect of compressing a table - so I had to determine the sizes of a compressed and a non-compressed table as well. While I worked trough that I realized that this is not that easy task as I assumed - derminining the size of a table (particular for tables containing lobs or partitioned tables) requires some effort ...
Der erste Ansatz (klar), ist ein Blick auf USER_SEGMENTS:
The first approach is to have a look at USER_SEGMENTS:
select bytes from user_segments where segment_name = 'MEINE_TABELLE'
Das funktioniert allerdings nur bei "normalen" heap-organisierten Tabellen. Bei anderen Tabellentypen kommt nix zurück. Und LOB-Spalten fehlen ebenfalls ...
  • Bei partitionierten Tabellen werden die Segmente nach den Partitionen benannt
  • LOB-Spalten werden in LOB-Segmenten gespeichert, die wiederum andere Namen haben.
    Übrigens: LOB-Spalten kommen auch schnell dazu, wenn mit XMLTYPE oder mit Spatial-Datentypen (SDO_GEOMETRY) gearbeitet wird.
  • Index-Organisierte-Tabellen (IOT) sind bei den Indexsegmenten zu finden ...
  • ...
Man muss also zuerst herausfinden, welche Segmente an der Tabelle "hängen" und deren Größen dann ermitteln.
This works for "normal" heap-organized tables, but for other tables this just returns no rows at all. And lob columns are also missing ...
  • Segments for partitioned have the partition's names
  • LOB columns are stored in LOB segments having other names.
    BTW: LOB columns are also often involved when XMLTYPE or spatial datatypes are present. wird.
  • Index-Organisierte-Tabellen (IOT) are stored as Index segments ...
  • ...
We therefore have to find out which segments are connected to the table and then we can determine those segment sizes ...
Außerdem liefert USER_SEGMENTS einfach nur die Größe der für die jeweiligen Segmente allokierten Extents zurück. Wie diese Extents gefüllt sind, geht aus dieser View nicht hervor. So kann es sein, dass beim Einfügen von Zeilen in eine Tabelle ein Extent von bspw. 64MB allokiert, dieses aber kaum gefüllt wird. USER_SEGMENTS zeigt dann 64MB an; in Wirklichkeit ist die Tabelle aber viel kleiner ...
The USER_SEGMENTS view furthermore just returns the size of the allocated extents. There is no information about how they are filled up with data. A table having one extend of 64MB allocated and containing only one row shows up as segment of 64MB size - which is correct, since USER_SEGMENTS returns the allocated size of a segment. But in reality the table might be much smaller ...
Man müsste also zusätzlich ermitteln, wie stark die Extents gefüllt sind, wieviel Speicherplatz also tatsächlich verwendet wird - und das geht mit dem Package DBMS_SPACE: Die darin enthaltene Prozedur UNUSED_SPACE liefert derlei Information für Tabellen-, Index oder Cluster-Segmente zurück. Für LOB-Segmente gibt es die Prozedur DBMS_SPACE.SPACE_USAGE; in 11g gibt es davon für Securefiles noch eine zusätzliche Variante. Bei Securefiles werden auch wesentlich genauere Zahlen zurückgeliefert als bei Basicfiles.
We therefore have to determine the actual segment usage - and for this purpose the PL/SQL package DBMS_SPACE is present. Its procedure UNUSED_SPACE analyzes table, cluster and index segments and returns information about unused and used blocks and bytes. For LOB segments there is another procedure: SPACE_USAGE which is overloaded for Basicfile lobs as well as for securefile lobs. For Securefile Lobs the returned numbers are much more detailed. is
Anhand dieser Überlegungen habe ich mir die folgende PL/SQL Table Function GET_SPACE_INFO geschrieben - sie sucht zur angegebenen Tabelle alle zugehörigen Segmente, die Größen und (via DBMS_SPACE) den "Füllgrad" heraus. Die folgende Table Function enthält auch Code für Securefiles, ist also 11g geschrieben. Wenn man sie auch in 10g verwenden möchte, müsste man den Code für die Securefiles entfernen ...
With those thoughts in mind I wrote the following PL/SQL table function GET_SPACE_INFO. For a given table it looks up all dependent segments and analyzes them with DBMS_SPACE. The code is written for Oracle11g and this version does not run in 10g due to the code handling securefile LOBs. The run it in 10g the securefile LOB code has to be removed.
drop type space_info_ct
/

drop type space_info_t
/

create type space_info_t as object(
  segment_name   varchar2(32),
  column_name    varchar2(4000),
  partition_name varchar2(32),
  segment_type   varchar2(32),
  alloc_bytes    number,
  free_bytes     number
)
/

create type space_info_ct as table of space_info_t
/


create or replace function get_space_info(
  p_table_name in varchar2
) return space_info_ct pipelined is
  v_bytes        number := null;
  v_free_bytes   number := null;
  v_ind_columns  varchar2(4000) := '';

  procedure get_unused_basiclob_space(
    p_segment_name   in varchar2,
    p_segment_type   in varchar2,
    p_partition_name in varchar2 default null
  ) is 
    v_unformatted_blocks   number;
    v_unformatted_bytes    number;
    v_fs1_blocks           number;
    v_fs1_bytes            number;
    v_fs2_blocks           number;
    v_fs2_bytes            number;
    v_fs3_blocks           number;
    v_fs3_bytes            number;
    v_fs4_blocks           number;
    v_fs4_bytes            number;
    v_full_blocks          number;
    v_full_bytes           number;
  begin
    dbms_space.space_usage(
      segment_owner             => sys_context('USERENV', 'CURRENT_USER'),
      segment_name              => p_segment_name,
      segment_type              => p_segment_type,
      unformatted_blocks        => v_unformatted_blocks,
      unformatted_bytes         => v_unformatted_bytes,
      fs1_blocks                => v_fs1_blocks,
      fs1_bytes                 => v_fs1_bytes,
      fs2_blocks                => v_fs2_blocks,
      fs2_bytes                 => v_fs2_bytes,
      fs3_blocks                => v_fs3_blocks,
      fs3_bytes                 => v_fs3_bytes,
      fs4_blocks                => v_fs4_blocks,
      fs4_bytes                 => v_fs4_bytes,
      full_blocks               => v_full_blocks,
      full_bytes                => v_full_bytes,
      partition_name            => p_partition_name
    );
    v_free_bytes := round(v_unformatted_bytes + (v_fs2_bytes * 0.25) + (v_fs3_bytes * 0.5) + (v_fs4_bytes * 0.75));
  exception 
    when others then 
      v_free_bytes := null;
  end get_unused_basiclob_space;

  procedure get_unused_lob_space(
    p_segment_name   in varchar2,
    p_segment_type   in varchar2,
    p_partition_name in varchar2 default null
  ) is 
    v_seg_size_blocks   number;
    v_seg_size_bytes    number;
    v_used_blocks       number;
    v_used_bytes        number;
    v_expired_blocks    number;
    v_expired_bytes     number;
    v_unexpired_blocks  number;
    v_unexpired_bytes   number;
  begin
    dbms_space.space_usage(
      segment_owner             => sys_context('USERENV', 'CURRENT_USER'),
      segment_name              => p_segment_name,
      segment_type              => p_segment_type,
      segment_size_blocks       => v_seg_size_blocks,
      segment_size_bytes        => v_seg_size_bytes,
      used_blocks               => v_used_blocks,
      used_bytes                => v_used_bytes,
      expired_blocks            => v_expired_blocks,
      expired_bytes             => v_expired_bytes,
      unexpired_blocks          => v_unexpired_blocks,
      unexpired_bytes           => v_unexpired_bytes,
      partition_name            => p_partition_name
    );
    v_free_bytes := v_seg_size_bytes - v_used_bytes;
  exception 
    when others then 
      v_free_bytes := null;
  end get_unused_lob_space;

  procedure get_unused_space(
    p_segment_name   in varchar2,
    p_segment_type   in varchar2,
    p_partition_name in varchar2 default null
  ) is 
    v_tot_blocks           number;
    v_tot_bytes            number;
    v_unused_blocks        number;
    v_unused_bytes         number;
    v_last_used_extent_fid number;
    v_last_used_extent_bid number;
    v_last_used_block      number;
  begin
    dbms_space.unused_space(
      segment_owner             => sys_context('USERENV', 'CURRENT_USER'),
      segment_name              => p_segment_name,
      segment_type              => p_segment_type,
      total_blocks              => v_tot_blocks,
      total_bytes               => v_tot_bytes,
      unused_blocks             => v_unused_blocks,
      unused_bytes              => v_unused_bytes,
      last_used_extent_file_id  => v_last_used_extent_fid,
      last_used_extent_block_id => v_last_used_extent_bid,
      last_used_block           => v_last_used_block,
      partition_name            => p_partition_name
    );
    v_free_bytes := v_unused_bytes;
  exception 
    when others then 
      v_free_bytes := null;
  end get_unused_space;
begin
  -- Step I: Partitioned table segments
  for i in (
    select 
      us.segment_name, 
      us.segment_type, 
      us.bytes, 
      utp.partition_name 
    from user_segments us, user_tab_partitions utp
    where utp.table_name = p_table_name 
     and us.partition_name = utp.partition_name
     and segment_type like 'TABLE%'
  ) loop
    get_unused_space(
      p_segment_name   => i.segment_name, 
      p_segment_type   => i.segment_type, 
      p_partition_name => i.partition_name
    );
    pipe row (
     space_info_t(
      i.segment_name, 
      null,
      i.partition_name,
      i.segment_type, 
      i.bytes, 
      v_free_bytes 
     )
    );
  end loop;

  -- Step II: Unpartitioned heap table segment
  begin
    select bytes into v_bytes from user_segments
    where segment_name = p_table_name and partition_name is null;
    get_unused_space(
      p_segment_name   => p_table_name,
      p_segment_type   => 'TABLE'
    );
    pipe row (
     space_info_t(
      p_table_name, 
      null,
      null,
      'TABLE', 
      v_bytes, 
      v_free_bytes
     )
    );
  exception 
    when NO_DATA_FOUND then null;
      -- IOT segments are fetched in the INDEX section
      -- Clustered tables are fetched in the CLUSTER section
  end;
 
  -- Step III: Lob segments
  for i in (
    select distinct 
      als.segment_name, 
      als.segment_type,
      als.bytes, 
      al.column_name, 
      als.partition_name,
      al.securefile
    from user_segments als, user_lobs al
    where 
      al.table_name = p_table_name and al.segment_name = als.segment_name
  ) loop 
    if i.securefile = 'YES' then 
      get_unused_lob_space(
        p_segment_name   => i.segment_name, 
        p_segment_type   => case when i.segment_type = 'LOBSEGMENT' then 'LOB' else i.segment_type end,
        p_partition_name => i.partition_name
      );
    else 
      get_unused_basiclob_space(
        p_segment_name   => i.segment_name, 
        p_segment_type   => case when i.segment_type = 'LOBSEGMENT' then 'LOB' else i.segment_type end,
        p_partition_name => i.partition_name
      );
    end if;  
    pipe row (
     space_info_t(
      i.segment_name, 
      i.column_name,
      i.partition_name,
      i.segment_type, 
      i.bytes, 
      v_free_bytes 
     )
    );
  end loop;

  -- Step IV: Cluster segments

  for i in (
    select 
      us.segment_name, 
      us.segment_type, 
      us.bytes
    from user_segments us, user_clusters uc, user_tables ut
    where ut.table_name = p_table_name 
     and ut.cluster_name = uc.cluster_name
     and uc.cluster_name = us.segment_name
  ) loop
    get_unused_space(
      p_segment_name   => i.segment_name, 
      p_segment_type   => i.segment_type
    );
    pipe row (
     space_info_t(
      i.segment_name, 
      null,
      null,
      i.segment_type, 
      i.bytes, 
      v_free_bytes 
     )
    );
  end loop;

  -- Step V: Index (plus Lobindex) segments
  for i in (
    select 
      ai.index_name, 
      ais.segment_name, 
      ais.segment_type, 
      ais.bytes, 
      ais.partition_name
    from user_indexes ai, user_segments ais
    where ais.segment_name = ai.index_name and ai.table_name = p_table_name
  ) loop
    -- collect indexed columns for an index segment
    v_ind_columns := '';
    for j in (
      select column_name 
      from user_ind_columns
      where index_name = i.index_name
    ) loop
      v_ind_columns := v_ind_columns || j.column_name || ',';
    end loop;
    v_ind_columns := substr(v_ind_columns, 1, length(v_ind_columns) - 1);
    get_unused_space(
      p_segment_name   => i.segment_name, 
      p_segment_type   => case when i.segment_type = 'LOBINDEX' then 'INDEX' else i.segment_type end,
      p_partition_name => i.partition_name
    );
    pipe row (
     space_info_t(
      i.segment_name, 
      v_ind_columns,
      i.partition_name,
      i.segment_type, 
      i.bytes, 
      v_free_bytes 
     )
    );
  end loop;
  return;
end;
/
sho err
Nach dem Erstellen kann man die Table Function wie folgt nutzen (die im Beispiel abfragte Tabelle DOKUMENT_TAB enthält eine (Basicfile) BLOB-Spalte und einen Index für den Pimärschlüssel).
The function can be used as follows (the here used table DOKUMENT_TAB contains a LOB column (basicfile) and an index for the primary key column) ...
SQL> desc dokument_tab
 Name                                      Null?    Typ
 ----------------------------------------- -------- ---------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                      NOT NULL VARCHAR2(100)
 BESCHREIBUNG                                       VARCHAR2(4000)
 MIMETYPE                                  NOT NULL VARCHAR2(100)
 DOKUMENT                                  NOT NULL BLOB

SQL> select * from table(get_space_info('DOKUMENT_TAB'));

SEGMENT_NAME                COLUMN_NAME        PARTITION_NAME   SEGMENT_TYPE   ALLOC_BYTES FREE_BYTES
--------------------------- ------------------ ---------------- -------------- ----------- ----------
DOKUMENT_TAB                                                    TABLE                65536          0
PK_DOKUMENT_TAB             ID                                  INDEX                65536      32768
SYS_LOB0000153809C00005$$   DOKUMENT                            LOBSEGMENT        10485760     516096
SYS_IL0000153809C00005$$                                        LOBINDEX             65536      32768
Ein schönes Beispiel ist übrigens die Tabelle SALES im Beispielschema SH; daran sieht man, wieviele Segmente bei sehr großen partitionierten Tabellen vorhanden sind. Interessant sind die Ergebnisse auch bei Tabellen mit Geodaten (Spatial). Die Koordinaten befinden sich hier in einem VARRAY, welches als LOB gespeichert wird ..
Another nice example is the SALES table in the SH sample schema. This shows nicely that a partitioned table contains many segments (each partition is a segment). The following example shows a table with a spatial data column (SDO_GEOMETRY). The spatial coordinates are stored within a VARRAY and this is stored as a LOB - which gives a LOB segment.
SQL> select * from table(get_space_info('M_ADMIN_AREA4'));

SEGMENT_NAME              COLUMN_NAME                PARTITION_NAME  SEGMENT_TYPE   ALLOC_BYTES FREE_BYTES
------------------------- -------------------------- --------------- -------------- ----------- ----------
M_ADMIN_AREA4                                                        TABLE             23068672          0
SYS_LOB0000125635C00014$$ "GEOMETRY"."SDO_ORDINATES"                 LOBSEGMENT        66060288     942080
SYS_LOB0000125635C00013$$ "GEOMETRY"."SDO_ELEM_INFO"                 LOBSEGMENT           65536          0
SYS_IL0000125635C00014$$                                             LOBINDEX             65536      32768
SYS_IL0000125635C00013$$                                             LOBINDEX             65536      32768
Viel Spaß beim Experimentieren mit der Funktion ...
Have fun!

13. Januar 2009

Verzögerte Constraint-Prüfung: deferrable Constraints

English title: Circular Foreign Key Constraints with "deferred Constrains"

Wusstet Ihr schon, dass man einen Constraint in der Oracle-Datenbank deferred deklarieren kann, also dass er "verzögert" geprüft werden soll?
Did you know ... that a database constraint can be checked deferred?
Zunächst stellt sich die Frage, was "verzögert" überhaupt bedeutet - die Antwort ist einfach - ein als deferred deklarierter Constraint wird erst beim COMMIT überprüft und nicht sofort beim Ausführen des jeweiligen DML-Kommandos. Daber wozu braucht man das?
The first question arising is what deferred means: The answer is simple. Deferred means that the particular constraint is checked after the transaction is finished with a COMMIT. The "normal" behaviour is immediate checking just after the DML command was executed.
Angenommen, wir haben zwei Tabellen: KUNDE und ADRESSEN. Ein Kunde kann mehrere Adressen haben, daher die 1:n-Beziehung. Aber eine der Adressen soll als "primäre" Anschrift deklariert werden. Insofern würde man die Tabellen wie folgt anlegen ...
Imagine two tables: KUNDEN (for Customers) and and ADRESSEN (for addresses). One customer can have multiple addresses but one of these addresses must be marked as "primary" address. So the tables are being created as follows ...
create table kunden(
  id       number (10)   not null,
  name     varchar2(200) not null,
  prim_adr number(10)    not null,
  constraint pk_kunden primary key (id)
)
/

create table adressen(
  id       number(10)    not null,
  kunde_id number(10)    not null,
  strasse  varchar2(200),
  plz      number(5),
  ort      varchar2(200),
  constraint pk_adressen primary key (id)
)
/

alter table kunden add constraint fk_kunde_prim_adr foreign key (prim_adr) 
  references adressen (id)
/

alter table adressen add constraint fk_adressen_kunde foreign key (kunde_id)
  references kunden(id)
/
... was eine schöne Überkreuzbeziehung darstellt. Das Problem ist nun nur, dass man hier keinen einzigen Satz eingefügt bekommt. Denn um einen Kunden zu erzeugen, muss erstmal eine Adresse vorhanden sein; und um eine Adresse anlegen zu können, braucht man einen Kunden - Die Katze beißt sich also in den Schwanz.
... which means that you cannot insert any row. The foreign keys model a circular reference - to create a customer you need the primary address. But you need the customer in order to create an address. So you're chasing your own tail.
Es gibt allerdings schon seit Oracle8i eine Lösung dafür - den als deferred markierten Constraint - was bedeutet, dass die Prüfung erst beim COMMIT erfolgt. Und zwar gibt es hierfür zwei Einstellungen. Zunächst muss ein Constraint als deferrable deklariert sein. Zusätzlich muss die Ausführung dann noch auf deferred gesetzt werden. Für dieses Beispiel würde man die Fremdschlüssel-Constraints dann so anlegen:
But there is a solution. Since Oracle8i there are deferrable constraints - the constraint is created in that manner that it's checking is being performed after the commit. This is being achieved in two steps: First the constraint must be marked as deferrable and then the check setting must be changed from immediate to deferred. The following constraint definitions do both:
alter table kunden add constraint fk_kunde_prim_adr foreign key (prim_adr) 
  references adressen (id)
  deferrable initially deferred
/

alter table adressen add constraint fk_adressen_kunde foreign key (kunde_id)
  references kunden(id)
  deferrable initially deferred
/
Nun kann man Sätze in die Tabellen einfügen - das folgende Beispiel fügt einen Kunden ein, "vergißt" aber dann die Adressen ...
Now you can create rows - the following example creates a customer - but omits the address. The commit command raises the error message.
SQL> insert into kunden values (1, 'Testkunde', 1);

1 Zeile wurde erstellt.

SQL> commit;
commit
*
FEHLER in Zeile 1:
ORA-02091: Transaktion wurde zurückgesetzt
ORA-02291: Integritäts-Constraint (PARTNER.FK_KUNDE_PRIM_ADR) verletzt -
übergeordneter Schlüssel nicht gefunden
Wenn man zusätzlich die Adressen einfügt, ist alles in Ordnung:
If the addresses get created in the same transaction everything is well:
SQL> insert into kunden values (1, 'Testkunde', 1);

1 Zeile wurde erstellt.

SQL> insert into adressen values (1, 1, 'Musterstr. 1','80912','München');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.
Obwohl hier also mit Fremdschlüsseln ein "Zirkelschluß" implementiert wurde, kann man trotzdem problemlos Sätze einfügen - die Prüfung findet eben erst mit Abschluß der Transaktion statt. Das ist, finde ich, ein sehr nettes Feature der Oracle-Datenbank.
So the circular reference is not a problem anymore - the constraint checking isbeing deferred to the end of the transaction. This is IMHO a very nice database feature.
Wenn ein Constraint als deferrable deklariert ist, kann er dennoch sofort (immediate) geprüft werden - dass geht dann wie folgt:
Deferred constraints also allow immediate checking - you just have to switch it (for your session).
SQL> set constraint fk_kunde_prim_adr immediate;

Constraint wurde festgelegt.
... oder für alle ...
... or for all constraints ...
SQL> set constraint all immediate;

Constraint wurde festgelegt.
Mit set constraint [[Name]|all] [deferred|immediate] kann das Verhalten der Constraints pro Session umgeschaltet werden - Grundvoraussetzung ist jedoch, dass der Constraint deferrable ist.
The set constraint [[Name]|all] [deferred|immediate] command switches the checking behaviour for one or all constraints between immediate and deferred. It's only valid for constraints created as deferrable.

7. Januar 2009

Session Tracefile ansehen: Nur mit SQL ... und jetzt auch in Oracle9i und Oracle10g

English title: Viewing Tracefiles with SQL and PL/SQL

Erstmal ein gutes Neues Jahr 2009 an alle!
Zu diesem Thema gab es im Oktober 2008 schonmal ein Posting, welches jedoch nur in Oracle11g lauffähig war. Damals habe ich versprochen, für Oracle10g und Oracle9i etwas nachzuliefern - und hier ist es:
First things first: Happy new Year 2009!
Last October I published a posting about viewing tracefiles with SQL and PL/SQL but this was only "supported" for Oracle11g. I promised to post a version for Oracle9i and Oracle10g as well - here it is ...
Man kommt als Entwickler des öfteren in die Situation, sich eine Tracedatei ansehen zu müssen ... zwei Beispiele:
  • Wenn man einen SQL Trace aktiviert, um ein SQL-Kommando zu analysieren, werden die Informationen in die Tracedatei geschrieben
  • Wenn man eine Java Stored Procedure laufen lässt und diese eine Exception auslöst, landet der Java-Fehler-Stack im Tracefile
Und in diesen Fällen bedeutet das, dass man die Datenbankumgebung verlassen, sich an der Datenbankmaschine anmelden, die Tracedatei suchen und ggfs. mit tkprof aufbereiten muss - erst dann kann man sich die Inhalte ansehen. Das ist zumindest mal aufwändig und manchmal hat man auch gar keine Login-Daten für das Betriebssystem der Datenbankmaschine. Speziell bei Application Express hat man vielfach nur den Zugang über die Web-Oberfläche.
Sometimes there are situations where developers have to look into the database's tracefiles. Here are two examples ...
  • If you activate a SQL trace to analyze a particular SQL command then this information is being written into a tracefile
  • If a java stored procedure (java in the database) throws an exception the java error stack is by default written to a tracefile
... and this means that you have to log into the operating system of the database machine, change to the tracefile directory, lookup the file, process it with tkprof (when necessary) and finally view the results. This costs at least time and in some cases you don't even have credentials for the operating system.
In Oracle11g gibt es eine nette, sehr hilfreiche Kleinigkeit: die View V$DIAG_INFO. Diese gibt den Namen des für die aktuelle Session gültigen Tracefiles wie folgt heraus:
In Oracle11g there's a nice very helpful new view: V$DIAG_INFO shows the name of the current sessions' tracefile as follows:
SQL> select value from v$diag_info where name='Default Trace File'
  2  /

VALUE
--------------------------------------------------------------------------------
/oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6328.trc
In Oracle10g geht das auch - ist aber nicht so bequem. Man muss man dazu ein wenig SQL schreiben ...
This is also possible in Oracle10g. But then some SQL is required ...
select 
  c.value || file_pkg.get_path_separator || 
  lower(d.instance_name) || 
  case
    when b.server = 'DEDICATED' then '_ora_' 
    else '_'||lower(vs.name)||'_'
  end ||
  to_char(a.spid, 'fm99999')
  || '.trc' 
from v$process a, v$session b, v$parameter c, v$instance d, v$shared_server vs
where a.addr = b.paddr and b.audsid = userenv('sessionid') and c.name = 'user_dump_dest' and a.addr = vs.paddr (+);
Als Ausgangspunkt habe ich das SQL dieses Blog-Postings von Pas Apicalla verwendet. Ich musste es allerdings ein wenig erweitern, so dass es auch für Shared Server-Sessions den richtigen Dateinamen liefert. Achtet darauf, dass Ihr SELECT-Privilegien an den beteiligten V$-Views habt.
I started using the SQL query in this blog posting from Pas Apicalla. I extended it a bit so that it returns the correct filename also for shared server sessions. Make sure that you have SELECT privileges on the here used V$-Views.
Damit entfällt das Suchen auf dem Server - das Tracefile-Verzeichnis kann durchaus mal viele Dateien enthalten. Man kann die View aber auch ganz anders nutzen ...
Ok - with this you don't have to search for the tracefile - you already know its name ... but this information can furthermore be used to simplify the whole process.
Ich nehme dazu mal wieder das Package zum Ausführen von Betriebssystem-Kommandos zur Hilfe - damit kann man die Datei direkt auslesen und sogar das tkprof-Utility direkt aus der Datenbank starten. Das folgende PL/SQL-Paket TRACE_HELPER macht genau das.
I'll -again- take the package for operating system interaction in order to create a PL/SQL package which does all the stuff automatically.
create or replace package trace_helper is
  function get_session_trace_file return clob;
  function get_session_tkprof_trace(
    p_recursive_sql  in number default 1,
    p_explain        in varchar2 default null,
    p_sort           in varchar2 default null
  ) return clob;
  procedure set_output_tempfile_prefix(p_prefix in varchar2);
end trace_helper;
/
sho err

create or replace package body trace_helper is
  C_TKPROF_COMMAND constant varchar2(200) := '/oracle/u01/app/oracle/product/11.1.0/bin/tkprof';
  C_OUTFILE_PREFIX constant varchar2(200) := '/tmp/tkprof_out';

  g_outfile_prefix varchar2(200) := C_OUTFILE_PREFIX;

  procedure set_output_tempfile_prefix(p_prefix in varchar2) is
  begin
    g_outfile_prefix := p_prefix;
  end set_output_tempfile_prefix;
  function get_trc_file_name return varchar2 is 
    v_filename varchar2(32767);
  begin
    $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN  
      select 
        c.value || file_pkg.get_path_separator || 
        lower(d.instance_name) || 
        case
          when b.server = 'DEDICATED' then '_ora_' 
          else '_'||lower(vs.name)||'_'
        end ||
        to_char(a.spid, 'fm99999')
        || '.trc' into v_filename
      from v$process a, v$session b, v$parameter c, v$instance d, v$shared_server vs where a.addr = b.paddr 
      and b.audsid = userenv('sessionid') 
      and c.name = 'user_dump_dest'
      and a.addr = vs.paddr (+);
    $ELSE                                  
      SELECT value into v_filename FROM v$diag_info WHERE name = 'Default Trace File';
    $END
    return v_filename;
  end get_trc_file_name;

  function get_session_trace_file return clob is
  begin
    return file_pkg.get_file(get_trc_file_name).get_content_as_clob('iso-8859-1');
  end get_session_trace_file;

  function get_session_tkprof_trace(
    p_recursive_sql  in number default 1,
    p_explain        in varchar2 default null,
    p_sort           in varchar2 default null
  ) return clob is
    v_tkprof_command varchar2(32767) := C_TKPROF_COMMAND;
    v_tkprof_success number;
    v_tkprof_content clob;

    v_output_file    file_type;
  begin
    v_output_file := file_pkg.get_file(
      g_outfile_prefix || substr(get_trc_file_name, instr(get_trc_file_name, '/', -1) + 1)
    );
    v_tkprof_command := v_tkprof_command || ' ' || get_trc_file_name || ' ' || v_output_file.file_path;

    if p_explain is not null then
      v_tkprof_command := v_tkprof_command || ' explain=' || p_explain;
    end if;
    if p_recursive_sql = 0 then 
      v_tkprof_command := v_tkprof_command || ' sys=no';
    end if;
    if p_sort is not null then
      v_tkprof_command := v_tkprof_command || ' sort=' || p_sort;
    end if;  

    v_tkprof_success := os_command.exec(v_tkprof_command);
    v_tkprof_content := v_output_file.get_content_as_clob('iso-8859-1');
    v_output_file := v_output_file.delete_file();

    return v_tkprof_content;
  end get_session_tkprof_trace; 
end trace_helper;
/
sho err
Das entstandene Package sieht so aus ...
The resulting package looks like this:
FUNCTION GET_SESSION_TKPROF_TRACE RETURNS CLOB
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_RECURSIVE_SQL                NUMBER                  IN     DEFAULT
 P_EXPLAIN                      VARCHAR2                IN     DEFAULT
 P_SORT                         VARCHAR2                IN     DEFAULT
FUNCTION GET_SESSION_TRACE_FILE RETURNS CLOB
PROCEDURE SET_OUTPUT_TEMPFILE_PREFIX
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_PREFIX                       VARCHAR2                IN
ACHTUNG: Die Zeile mit dem Pfad zum tkprof Executable müsst Ihr an euere Umgebung anpassen ...
Attention: The line containing the path to the tkprof executable must be adjusted to your environment before using the package.
  C_TKPROF_COMMAND constant varchar2(200) := '/oracle/u01/app/oracle/product/11.1.0/bin/tkprof';
  • Die Funktion GET_SESSION_TRACE_FILE liest das Tracefile der aktuellen Session aus und gibt es als CLOB zurück. Das ist bspw. bei Java in der Datenbank hilfreich, wenn eine Exception ausgelöst wurde und die Details im Tracefile stehen ...
  • Die Function GET_SESSION_TKPROF_TRACE gibt eine mit dem tkprof-Werkzeug aufbereitete Version des Tracefile als CLOB zurück. Während dieses Prozesses wird von eine temporäre Datei (diese nimmt den aufbereiteten Text auf) erzeugt, deren Inhalte werden in einen CLOB kopiert und dieser dann zurückgegeben. Schließlich wird die temporäre Datei gelöscht. Das Verzeichnis, in welches diese temporäre Datei abgelegt wird, könnt Ihr mit der Konstante C_OUTFILE_PREFIX oder mit der Prozedur SET_OUTPUT_TEMPFILE_PREFIX beeinflussen.
  • Die Prozedur SET_OUTPUT_TEMPFILE_PREFIX legt den Pfad und das Namens-Präfix für die eben erwähnte temporäre Datei fest.
Damit das ganze funktioniert, braucht euer Datenbankschema nun noch einige Privilegien ... das folgende Skript müsst Ihr als DBA ausführen und den TRCTEST durch euren DB User ersetzen.
  • The function GET_SESSION_TRACE_FILE reads just the tracefile content and returns it as a CLOB. This is helpful when java stored procedures throw exceptions - you then see the java error stack in the returning CLOB.
  • The function GET_SESSION_TKPROF_TRACE executes tkprof on the session's tracefile and returns the tkprof output as a CLOB. During this process a temporary file with the tkprof output is being created, its contents are then being copied into a CLOB and finally it's being deleted. The directory into which this temporary file is placed, is determined by the PL/SQL constant C_OUTFILE_PREFIX - so you might want to adjust this according to your environment. The directory and the filename prefix can also be adjusted by the procedure SET_OUTPUT_TEMPFILE_PREFIX.
  • The procedure SET_OUTPUT_TEMPFILE_PREFIX is used to set the directory and filename prefix for the temporary file generated by tkprof.
Your database schema needs some privileges in order to use the package. Just run the following script as the SYS user and change the TRCTEST user to the database user you're working with.
 
-- execute privilege for the "tkprof" utility
begin
  dbms_java.grant_permission( 
    'TRCTEST',
    'SYS:java.io.FilePermission',
    '/oracle/u01/app/oracle/product/11.1.0/bin/tkprof', 
    'execute' 
  );
end;
/

-- read privilege for the tracefile directory
declare
  v_diag_dir varchar2(4000);
begin
  select value into v_diag_dir 
  from v$diag_info where name = 'Diag Trace';
 
  dbms_java.grant_permission( 
    'TRCTEST',
    'SYS:java.io.FilePermission',
    v_diag_dir || '/-', 
    'read' 
  );
end;
/

-- read and write privileges for a temporary directory
-- the temporary files for the tkprof output are placed here
begin
  dbms_java.grant_permission( 
    'TRCTEST',
    'SYS:java.io.FilePermission',
    '/tmp/-',
    'read,write' 
  );
end;
/

-- this grants write permission on STDIN
begin
   dbms_java.grant_permission(
     grantee =>           'TRCTEST',
     permission_type =>   'SYS:java.lang.RuntimePermission',
     permission_name =>   'writeFileDescriptor',
     permission_action => null
   );
end;
/

-- this grants read permission on STDOUT
begin
   dbms_java.grant_permission(
     grantee =>           'TRCTEST',
     permission_type =>   'SYS:java.lang.RuntimePermission',
     permission_name =>   'readFileDescriptor',
     permission_action => null
   );
end;
/
Fertig. Testen ...
That's it ... here's a test ...
SQL> alter session set sql_trace=true;

Session altered.

SQL> select ... from ...;

:
:

SQL> select trace_helper.get_session_tkprof_trace from dual;

GET_SESSION_TKPROF_TRACE
--------------------------------------------------------------------------------

TKPROF: Release 11.1.0.7.0 - Production on Wed Oct 22 02:36:46 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: /oracle/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6380.trc
Sort options: default

********************************************************************************

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers f ...
:
Ihr bekommt die Infos also direkt im SQL*Plus oder dem Werkzeug, mit dem Ihr gerade arbeitet - ein Wechseln der Umgebung ist nicht mehr nötig ...
You can now see the tracefile information within SQL*Plus (or your database development tool) - changing the environment is no longer necessary.

Beliebte Postings