Netcos AG   XCP™ Blackboard
Tip 3   Tip 3 Tip 5  Tip 5
Tip No. 4   Objekt in der DB mit dem Package DMBS_SPACE analysieren (ab 9i)
Mit dem DBMS_SPACE-Package lässt sich einfach und schnell die Blockverwendung von Objekten in der DB bestimmen.
Das Package wird über dbmsutil.sql beim Aufruf von catproc.sql erzeugt.
Die Procedures des Packages:
  1. DBMS_SPACE.UNUSED_SPACE — Informationen über ungenützten Platz im Objekt unterhalb der High Water Mark
  2. DBMS_SPACE.FREE_BLOCKS — Informationen über freie Blöcke im Objekt mit Free List Verwaltung
  3. DBMS_SPACE.SPACE_USAGE — Informationen über freie Blöcke im Objekt mit Automatischer Verwaltung
Verwendung DBMS_SPACE.UNUSED_SPACE (Definition)
Mit der Procedure lassen sich Informationen über ungenutzten Platz im Objekt unterhalb der High Water Mark ermitteln.
Die HW (High Water Mark) markiert die Grenze zwischen den verwendeten und den unbenützten Blöcken im letzten Extents eines DB Objektes.
Bei einem Full Table Scan wird immer bis zur HW gelesen. Befinden sich leere Blöcke unterhalb der HW, werden unnötige Lese Zugriffe durchgeführt. Diese leeren Blöcke entstehen zum Beispiel durch das Lüschen von Daten.
  1. Ermitteln der Objekte mit viel Platz unterhalb der HW
  2. Freigeben des Platzes mit "ALTER TABLE/INDEX/CLUSTER DEALLOCATE UNUSED"

Beispiel: 
set serveroutput on;
declare
 v_total_blocks number;
 v_total_bytes number;
 v_unused_blocks number;
 v_unused_bytes number;
 v_last_used_extent_file_id number;
 v_last_used_extent_block_id number;
 v_last_used_block number;
 begin
  dbms_space.unused_space ('SCOTT'
                          ,'EMP'
                          ,'TABLE'
                          ,v_total_blocks
                          ,v_total_bytes
                          ,v_unused_blocks
                          ,v_unused_bytes
                          ,v_last_used_extent_file_id
                          ,v_last_used_extent_block_id
                          ,v_last_used_block);
  dbms_output.put_line('Gesamt Größe Bytes       = '||v_total_bytes);
  dbms_output.put_line('Anzahl ungenutzter Blöcke = '||v_unused_blocks);
  dbms_output.put_line('Ungenützte Bytes          = '||v_unused_bytes);
  dbms_output.put_line('Letzter genützter Block   = '||v_last_used_block);
end;
/
ALTER TABLE scott.emp DEALLOCATE UNUSED;
Download Beispiel


Verwendung DBMS_SPACE.FREE_BLOCKS (Definition)
Anzeige der Anzahl der Blöcke auf der Freelist eines Objektes in der Datenbank (nur für Objekte mit Free List Verwaltung). Gleicher Wert wie in DBA_TABLES.EMPTY_BLOCKS nach der Analyse der Table mit "analyze table"
Alle Blöcke, die den Wert PCTUSED des Objektes unterschreiten, werden auf die Free List des Objekts gesetzt und stehen für das Einfügen von neuen Daten zur Verfügung.
Die Procedure hilft dabei zu ermitteln, ob PCTUSED zu klein gewählt wurde. Dazu ist zusätzlich die Spalte AVG_SPACE und AVG_ROW_LEN in DBA_TABLES zu beachten. Ist AVG_SPACE nah bei dem Wert von AVG_ROW_LEN und PCTUSED klein, kann es günstiger sein, das Objekt mit einem größeren Wert von PCTUSED neu anzulegen.

Beispiel: 
set serveroutput on
declare
   free_blocks number;
begin
   DBMS_SPACE.FREE_BLOCKS('SCOTT'
                          , 'EMP'
                          ,'TABLE'
                          ,0
                          ,free_blocks);
   dbms_output.put_line('Freie Blöcke = '||free_blocks);
end;
/
Download Beispiel


Verwendung DBMS_SPACE.SPACE_USAGE (Definition)
Wird für den Tablepace des Objektes das neue Feature "Automatic Segment Space Management" verwendet, kann die Anzahl der Blöcke mit dem jeweiligen Füllgrad eines Objektes in der Datenbank angezeigt werden.
Der Parameter PCTUSED wird nicht mehr benötigt. Die Verwaltung des Füllgrades eines Blockes erfolgt über ein Bitmap-Verfahren. Dazu sind zusätzliche Blöcke in Extent notwendig und es ist ein kleiner Platzverlust damit verbunden (ca. 1-2%).
Beispiel: 
set serveroutput on

declare
 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 => 'SCOTT'
		       ,segment_name  => 'ASSMTAB'
                       ,segment_type  => 'TABLE'
                       ,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);


 dbms_output.put_line(' Unformatierte Blöcke    = '||v_unformatted_blocks);
 dbms_output.put_line(' Unformatierte Bytes     = '||v_unformatted_bytes);
 dbms_output.put_line(' Blöcke Füllgrad 0-25    = '||v_fs1_blocks);
 dbms_output.put_line(' Bytes  Füllgrad 0-25    = '||v_fs1_bytes );
 dbms_output.put_line(' Blöcke Füllgrad 25-50%  = '||v_fs2_blocks);
 dbms_output.put_line(' Bytes  Füllgrad 25-50%  = '||v_fs2_bytes );
 dbms_output.put_line(' Blöcke Füllgrad 50-75%  = '||v_fs3_blocks);
 dbms_output.put_line(' Bytes  Füllgrad 50-75%  = '||v_fs3_bytes );
 dbms_output.put_line(' Blöcke Füllgrad 75-100% = '||v_fs4_blocks);
 dbms_output.put_line(' Bytes  Füllgrad 75-100% = '||v_fs4_bytes );
 dbms_output.put_line(' Blöcke Füllgrad 100%    = '||v_full_blocks);
 dbms_output.put_line(' Bytes  Füllgrad 100%    = '||v_full_bytes );

end;
/
©2005 netcos AG www.netcos.de