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!

Kommentare:

Carsten hat gesagt…

Hallo Carsten,

benötige ich bestimmte Berechtigungen, um deine Table-Function nutzen zu können? Oder benötige ich eine andere Version als 10.2.0.4.0?

Wenn ich die Funktion kompilieren möchte, bekomme ich u.a. die Meldung, dass "dbms_space.space_usage" eine falsche Anzahl Parameter verwendet.

Grüße,
Carsten

Carsten Czarski hat gesagt…

Hallo Carsten,

dieser Code läuft nur in 11g; man müsste den Teil für die Securefiles entfernen (das ist eine der SPACE_USAGE-Prozeduren), damit die Funktion auch in 10g läuft ...

Ich seh' zu, dass ich die Tage eine 10g-Variante poste ...

-Carsten

Anonym hat gesagt…

Servus,
ich versuche gerade, die Funktion in einer 11.2.0.3.0 EE zu nutzen.
Jedoch bekomme ich immer "no rows selected" zurück. (Egal welcher User bzw. welche Schema.)

Habe ich irgendwas übersehen?
Grüße
Ralph

Carsten Czarski hat gesagt…

Hallo,

wie genau hast Du die Funktion denn aufgerufen ... und als welcher User ...?

Beste Grüße

-Carsten

Anonym hat gesagt…

Versuch A): Als User sys die Funktion erstellt und aufgerufen mit:
select * from table(get_space_info('SAPR3.SOFFCONT1'));

Versuch B): Als User SAPR§ die Funktion erstellt und aufgerufen mit:
select * from table(get_space_info('SOFFCONT1'));

Beides habe ich in zwei verschiedenen Instanzen probiert.

Wie gesagt, leider beide Male ohne Ergebnis.

Grüße - Ralph

Carsten Czarski hat gesagt…

Hallo Ralph,

die erste Variante "SAPR3.SOFFCONT1" geht nicht. Man muss als der Eigentümer der Tabelle angemeldet sein. Grund ist, dass ich keine Dictionary View "ALL_SEGMENTS" gibt - nur "USER_SEGMENTS" und "DBA_SEGMENTS". Insofern habe ich die Prozedur so gebaut, dass der Eigentümer sie verwenden muss ...

Die zweite Variante (wenn man als SAPR3 angemeldet ist) und die Tabelle SOFFCONT1 im Schema SAPR3 liegt, sollte gehen ... auch auf einer 11.2.0.3

Beste Grüße

-Carsten

Anonym hat gesagt…

Servus Carsten,
war wohl doch zu heiß die letzten Tage. Hab heute Morgen nochmal von vorne angefangen und siehe da, es funktioniert.

Hier noch ein kleines Script für eine wohlformatierte Ausgabe:


spool C:\_sql_output\table_size_.log

set linesize 512
set heading on

column SEGMENT_NAME format a33
column COLUMN_NAME format a30 wrap
column PARTITION_NAME format a18 wrap
column SEGMENT_TYPE format a14
column ALLOC_BYTES format 999G999G999G999
column FREE_BYTES format 999G999G999G999


-- select * from table(get_space_info(''));

select SEGMENT_NAME,
COLUMN_NAME,
PARTITION_NAME,
SEGMENT_TYPE,
ALLOC_BYTES/1024/1024 as ALLOC_MB,
FREE_BYTES/1024/1024 as FREE_MB
from table(get_space_info(''));

select sum(ALLOC_BYTES/1024/1024/1024) as "SUM ALLOC_GB"
from table(get_space_info(''));

column SEGMENT_NAME clear
column COLUMN_NAME clear
column PARTITION_NAME clear
column SEGMENT_TYPE clear
column ALLOC_BYTES clear
column FREE_BYTES clear

spool off




Danke & Grüße
Ralph

Beliebte Postings