Logo Oracle Deutschland   DBA Community  -   Oktober 2012
Datenbank Speicherplatz überwachen mit dem Segment Advisor
von Ulrike Schwinn, ORACLE Deutschland B.V. & Co KG

Wie kritisch ist die Belegung des Speicherplatzes innerhalb der Tablespaces? Können vielleicht einige Segmente verkleinert bzw. reorganisiert werden, die unnötigen Platz verbrauchen? Um Antworten auf diese Fragen zu erhalten, gibt es die Möglichkeit unterschiedliche Data Dictionary Views wie DBA_SEGMENTS, DBA_EXTENTS und DBA_FREE_SPACE abzufragen bzw. Packages wie DBMS_SPACE zur detaillierten Analyse zu verwenden. Jeder DBA hat sicherlich eigene Skripte zusammengestellt, um einen Überblick über den verbrauchten Speicherplatz zu erhalten.

Zusätzlich gibt es in der Oracle Datenbank spezielle mitgelieferte Advisors, die bei der Analyse des Speicherplatzes und der Objekte eine gewisse Unterstützung liefern können - den sogenannten Segment und den Compression Advisor. Beide sind ohne zusätzliche Lizenz in jeder Edition enthalten. Der Compression Advisor im Gegensatz zum Segment Advisor steht allerdings nur in der Package Implementierung und ab 11g Release 2 ohne zusätzliches Download zur Verfügung. Ist man beispielsweise an detaillierten Informationen zu Komprimierungsraten der Tabellen interessiert, sollte man den Compression Advisor zurate ziehen. Folgende Artikel geben weitere Informationen zum Compression Advisor.

Möchte man allerdings generelle Informationen bzw. Empfehlungen über den Speicherplatzverbrauch der Tablespaces und Objekte erhalten, die verkleinert werden sollen, kann man den Segment Advisor verwenden. Dieser Advisor ist installiert und fertig konfiguriert, so dass kein spezielles Setup nötig ist. Den graphischen Einstieg findet man beispielsweise in Enterprise Manager Cloud Control über Administration => Oracle Scheduler => Maintenance Tasks.



Nutzt man den Link Chained Row Analysis (rechts unten) erhält man sogar einen Überblick über die Chained Rows, die während der Analyse der Tablespaces und Segmente gefunden wurden. Hier im Tipp liegt der Fokus auf der Linemode Variante um die Funktionsweise des Segment Advisors zu erklären.

Die Hauptaufgabe des Segment Advisors besteht in der Identifikation von Tablespaces und der zugehörigen Segmente, die zu viel Speicherplatz allokieren und die Auflistung der entsprechenden Kommandos zur Freigabe von Speicherplatz. Bei den Segmenten handelt es sich um Tabellen, Partitionen, zugehörige Indizes und Large Objects. Die notwendigen Statistiken für die Analyse werden aus dem AWR (bzw. ADDM Report) und aus Sample Statistiken des Segments abgeleitet.
Hinweis: Hierfür wie auch für die gesamte Nutzung des Segment Advisors ist keine Lizenz erforderlich.

Wie funktioniert nun der Segment Advisor im Detail? Eine wichtige Voraussetzung zum Funktionieren des Segment Advisors ist das Setzen des Parameters STATISTICS_LEVEL auf den Wert TYPICAL oder ALL. Der empfohlene Defaultwert ist TYPICAL. Diese Einstellung ist notwendig, um das Sammeln der entsprechenden Advisor Statistiken zu aktivieren.


Die Analyse erfolgt entweder automatisch zum Beispiel in der Automated Maintenance Task oder manuell. Die Automated Maintenance Tasks werden dabei entweder im Enterprise Manager selbst oder über folgende Aufrufe konfiguriert und überprüft.
BEGIN
  dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', 
                              operation => NULL, window_name => null);
END;
/
## Oder alle einschalten
BEGIN
  dbms_auto_task_admin.enable();
END;
/
Einen Überblick über alle automatischen Maintenance Tasks erhält man übrigens über die View DBA_AUTOTASK_CLIENT.
SQL> SELECT client_name, status, attributes FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
ATTRIBUTES
---------------------------------------------------------------------------
auto optimizer stats collection                                  ENABLED
ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor                                               ENABLED
ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor                                               ENABLED
ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL
Die automatische Analyse - auch Automatic Segment Advisor - umfasst nicht alle Segmente, sondern nur diejenigen, die eine hohe Wachstumsrate und eine hohe Aktivität aufweisen und in einem Tablespace liegen, der das Critical oder Warning Threshold überschreitet. Um unabhängig vom meist nächtlichen Lauf der automatischen Maintenance Task zu sein, lässt sich der Automatic Segment Advisor auch unabhängig vom Maintenance Window mit folgender DBMS_SPACE Prozedur starten.
SQL> execute dbms_space.auto_space_advisor_job_proc;
Alle Ergebnisse der Analysen - automatisch oder manuell erzeugt - werden auf den Enterprise Manager (Segment Advisor Recommendations) Pages oder in den entsprechenden DBA_ADVISOR_ Views bzw. über die ASA_RECOMMENDATIONS Funktion des Packages DBMS_SPACE gelistet. Sie beinhalten Empfehlungen und Kommandos wie zum Beispiel ONLINE SHRINK Kommandos für Tabellen und Indizes, eine Liste der Tabellen, die exzessives Row Chaining aufweisen usw. Beim Automatic Segment Advisor Lauf werden sogar OLTP Compression Empfehlungen für Tabellen gegeben, die mindestens 10 MB groß sind und mindestens 3 Indizes aufweisen. Beachten Sie bei der Nutzung, dass spezielle Angaben von Tablespaces und/oder Segmenten oder eine Begrenzung für den Umfang der Analyse hierbei nicht möglich sind.

Wie gerade schon erwähnt, reicht ein einfacher Blick auf die Enterprise Manager Page, um einen Überblick über die Ergebnisse zu erhalten. Ein Drilldown zu den entsprechenden Detail Pages gibt dann die Resultate aus. Eine andere Methode um die Ergebnisse einzusehen, liefert die ASA_RECOMMENDATIONS Funktion, wie folgendes Beispiel zeigt.
SQL> SELECT tablespace_name, segment_owner, segment_name, 
     recommendations, C1 Kommando 
     FROM TABLE (dbms_space.asa_recommendations(all_runs => 'TRUE',  
                 show_manual => 'TRUE', show_findings => 'FALSE'));

TABLESPACE_NAME                SEGMENT_OWNE SEGMENT_NAME
------------------------------ ------------ ------------------------------
RECOMMENDATIONS
---------------------------------------------------------------------------
KOMMANDO
---------------------------------------------------------------------------
EXAMPLE                        SH           CUST_BIG
Enable row movement of the table SH.CUST_BIG and perform shrink, estimated 
savings is 247990931 bytes.
alter table "SH"."CUST_BIG" shrink space

EXAMPLE                        SH           I_SALES1_TIME
Perform shrink, estimated savings is 34655061 bytes.
alter index "SH"."I_SALES1_TIME" shrink space

EXAMPLE                        SH           SALES
Move object SH.SALES partition SALES_Q3_1999 to flash storage, estimated I/O 
wait reduction is 4 millisec.

SOE                            SOE          INVENTORIES
Compress object SOE.INVENTORIES, estimated savings is 90177536 bytes.
alter table "SOE"."INVENTORIES"  compress for oltp
Da das Argument SHOW_MANUAL auf TRUE gesetzt ist, werden alle Empfehlungen - manuell und automatisch generierte - angezeigt.

Um Informationen über bestimmte Tablespaces bzw. die dort gespeicherten Segmente zu erhalten, die nicht in der automatischen Analyse berücksichtigt werden - kann man eine manuelle Analyse im Enterprise Manager oder über das Package DBMS_ADVISOR durchführen. Im Enterprise Manager wird dann automatisch ein Scheduler Job erzeugt. Im folgenden Beispiel wird eine Tablespace Analyse mit der manuellen Methode und mit dem Package DBMS_ADVISOR durchgeführt.
declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  id number;
  begin
  name:='Tablespace_Analyse';
  dbms_advisor.create_task (
    advisor_name     => 'Segment Advisor', 
    task_id          => id,
    task_name        => name,
    task_desc        => 'Segment Advisor Beispiel');

  dbms_advisor.create_object (
    task_name        => name,
    object_type      => 'TABLESPACE',
    attr1            => 'EXAMPLE',
    attr2            => NULL,
    attr3            => NULL,
    attr4            => NULL,
    attr5            => NULL,
    object_id        => obj_id);
  dbms_advisor.set_task_parameter(
    task_name        => name,
    parameter        => 'recommend_all',
    value            => 'TRUE');
  dbms_advisor.execute_task(name);
  end;
/
Mögliche Attributwerte für den Objekttyp (siehe OBJECT_TYPE) sind beispielsweise Tablespace, Index, Tabelle, Partition, Lob, Materialized Views oder Materialized View Log. Die Schreibweise für den Objekttyp findet sich in der View DBA_ADVISOR_OBJECT_TYPES. Möchte man eine Analyse auf Tabellen Ebene durchführen, müssen somit in der Prozedur CREATE_OBJECT folgende Attributwerte gesetzt werden: OBJECT_TYPE auf 'TABLE', ATTR1 auf den entsprechenden Schema Namen (z.B. 'HR'), ATTR2 auf den Objekt Namen (z.B. 'EMPLOYEES'). Ein Beispiel dazu findet sich im Administrator Guide in Kapitel 19. Die Ergebnisse lassen sich dann wieder einfach über die Funktion ASA_RECOMMENDATIONS abfragen oder auf den Enterprise Manager Pages finden. Bei den Empfehlungen werden sogar I/O Statistiken berücksichtigt wie unsere Ergebnisliste von oben zeigt.

Der Segment Advisor kann eine gute und einfache Hilfestellung bei der Analyse der Tablespaces und Segmente sein. Dabei muss berücksichtigt werden, dass nur permanente Tablespaces bzw. Segmente berücksichtigt werden können; temporäre oder Undo Tablespaces können damit nicht analysiert werden. Die generierten Kommandos reichen dabei von COMPRESS bzw. SKRINK Operation bis zur Nutzung des Package DBMS_REDEFINITION.

Möchten Sie noch mehr über den Segment Advisor erfahren, kann folgender Link hilfreich sein:



Zurück zur Community-Seite