SYSAUX Growth Oracle 11.2.0.4

Dies ist der detaillierte Beitrag zum Thema SYSAUX Growth Oracle.  Es gibt auch eine kürzere Zusammenfassung:  (Link)

Auf unseren gepatchten SOLARIS Systemen mit Oracle 11.2.0.4, aktuelles Patchlevel, erlebten wir die Auswirkungen eines von Oracle dokumentierten Bugs.

Bug 14373728 „Old Statistics not Purged from SYSAUX Tablespace“

Es fällt auf, dass SYSAUX immer größer wird, auch wenn keine Daten mehr hinzukommen. Im EM sieht das dann so aus:

Der dem Piechart zugrunde liegende Befehl:

Hier fällt sofort der exzessive Speicherbedarf der AWR Daten auf. Bei einer DB ohne Größenwachstum durch die Nutzerdaten ist es sehr unangenehm, wenn SYSAUX in die Größenordnung der Daten kommt.

Ob unsere Datenbank dieses Problem hat, können wir  überprüfen mit dem globalen Script zur Anzeige von „verwaisten Snapshots“ sowie deren Speicherbedarf:

Ausgabe von 10_dyn_SHOW_WRH_snapshotrange.sql:

Database  = DB4711  READONLY 1
BEFORE : Freespace in SYSAUX is 6876,875 MB
BEFORE : Groesse AWR Objekte in SYSAUX is 322,5 MB
GLOBAL : Anzahl verwaister Records ist 7888 MinOrphaned is 21261 MaxOrphaned is 21929
Anzahl Records insgesamt(ACTIVE_SESSION_HISTORY) 9346 TOTAL_HISTROWS(EVENT_HOSTOGRAM) 609967
—-
Vorhandene snaps ->
PARTITION NAME SNAP_ID DBID
————————— ——- ———-
Loopcnt 1 Partition_name WRH$_ACTIVE_1943918590_0
i 1 dbid 1943918590 : WRH$_ACTIVE_1943918590_0 MinInPartition 21261 MaxInPartition 22136
—
Anzahl verwaister Records ist 7888 MinOrphaned is 21261 MaxOrphaned is 21929
—
you should execute: DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE ( low_snap_id ->21261; high_snap_id ->21929; dbid ->1943918590);

—> Finished report

Das Script ermittelt die Parameter, mit denen man explizit das Systemscript DROP_SNAPSHOT_RANGE  aufrufen könnte  ( eigentlich DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE ).

LINK zum Script (10_dyn_SHOW_WRH_snapshotrange.sql)

Das müssen wir jedoch nicht explizit aufrufen,  auch das haben wir automatisiert, hier das Script, welches gleich das Doing übernimmt (im Prinzip gleiches Script nur READONLY = 0)

LINK zu 11_dyn_DROP_WRH_snapshotrange.sql 

Ausgabe:

Database = DB4711 Clipping is : 2 Readonly : 0
BEFORE : Freespace in SYSAUX is 6876,875 MB
BEFORE : Groesse AWR Objekte in SYSAUX is 322,5 MB
Vorhandene snaps ->
PARTITION NAME SNAP_ID DBID
————————— ——- ———-
Loopcnt 1 Partition_name WRH$_ACTIVE_1943918590_0
i 1 dbid 1943918590 : WRH$_ACTIVE_1943918590_0 MinInPartition 21261 MaxInPartition 22136
—
START : Anzahl verwaister Records ist 7888 MinOrphaned is 21261 MaxOrphaned is 21929
—
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE ( low_snap_id ->21261; high_snap_id ->21929; dbid ->1943918590);

FINAL CALL DROP_SNAPSHOT_RANGE 21261, 21929, 1943918590
—> Finished part

Dieser Vorgang (im Kern der Aufruf von DROP_SNAPSHOT_RANGE) zieht heftig Resourcen.  Auf Maschinen, die ein paar Wochen aus dem Zeitfenster gelaufene, nicht gepurgte SnapIds haben, mag es angehen. Haben Sie diesen Usecase, nutzen Sie das Script.

Aber auf unseren Maschinen mit angehäuften Gigabytes von verwaisten Records passierte folgendes: Wenn man es „am Stück“ laufen lässt, platzen alle Systemparameter aus den Nähten. Man muss üblicherweise die für Produktion angepassten Systemparameter erhöhen. Temp vergrößern ( > 700MB) , REDO (>= 256MB) , UNDO wird bei 3-4 GB knapp, 6 GB reichten knapp. Aber auch das ist keine Garantie dafür, dass die DB überhaupt noch nutzbar ist. Weiterhin geht die Laufzeit unkontrolliert in mehrere Stunden. Die Relationen sind halt recht kompliziert bei einer Unzahl von WR?$_*-Objekten. Dies drängt den Schritt auf, nicht alle Snapshots auf einmal zu löschen. Daher gibt es  Varianten des Scriptes: eine Variante fragt interaktiv nach dem Bereich der zu löschenden Snapids,  eine andere Variante führt automatisch ein Clipping um einen einstellbaren Wert durch, z.B werden maximal 500 verwaiste IDs am Stück gelöscht. Leider bringt das nicht den gewünschten Erfolg, der Overhead ist so hoch, dass selbst mit einem Testrange von zehn IDs unerträglich lange organisiert wird, bis es zu der eigentlichen Löschung kommt, und selbst diese minimale Löschung generierte wieder Resourcenprobleme.

Und dann soll es noch Bugs geben: LINK Problembeschreibung in Metalink (purge alter snapids in SYSAUX erfolgt nicht)

Daher haben wir eine resourcenschonendere Variante entwickelt, die im Prinzip in handlichen Portionen mit passend platzierten Commits die Objekte einzeln bereinigt,  um dann zum Schluss die physische Konsistenz durch Reorgs wieder herbeizuführen.

Hier holen wir uns die Parameter, die wir bei der resourcenschonenden Löschung weiterverwenden.

LINK zu 08_SHOW_SPECIALS_RANGEPARM.SQL (deaktiviert)

Ausgabe von 08_SHOW_SPECIALS_RANGEPARM.SQL:

Hier sehen wir einen recht großen Range von verwaisten Snapids.
In der ersten markierten Zeile lesen wir ab, dass Ids vorliegen von 8717 bis 33907. Diese geben wir gleich in unseren AWR-Aufräumer.
Man kann abschätzen, wie lange es dauerte, dass diese entstehen, da wir stündlich einen Snapshot angefordert haben, (33907-8717)/24 -> 1049 Tage Laufzeit ohne Aufräumen!

Daher gibt es den Clippingparameter ( DOCLIPPING > 0)

BEFORE : Groesse AWR Objekte in SYSAUX is 9798.8125 MB
Vorhandene snaps ->PARTITION NAME SNAP_ID DBID
————————— ——- ———-
Loopcnt  1 Partition_name WRH$_ACTIVE_3384418391_0

1  dbid 3384418391 : WRH$_ACTIVE_3384418391_0 MinInPartition 1 MaxInPartition 26827
—
START : Anzahl verwaister Records ist 3105645 MinOrphaned is 1 MaxOrphaned is 26825
—
Executing: DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE  ( low_snap_id ->1; high_snap_id ->26825; dbid ->3384418391);

***CLIPPING*** max auf min+MAXRANGE 1001

Aber für solche großen Ranges könnte das DROP_SNAPSHOT_RANGE nicht ausgelegt sein, daher ergeben sich sicherlich die oben beschriebenen Resourcenprobleme.

Die Lösung ist die Prozedur, die resourcenschonend in kleinen Häppchen einfach nur löscht:

LINK 92_DELETE_WRH_INTERACTIVE.SQL (AWR-Aufräumer) will follow later

Das Script kommt auch mit mehreren DBIds zurecht, eine geklonte DB könnte SnapIds einer älteren DBid enthalten.

Beispiel solch einer Installation, Protokoll:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

Database = DB4711 mit ID 3582112766
–
DBID in DBA_HIST_WR_CONTROL 3582112766
DBID in DBA_HIST_WR_CONTROL 3518517643
–
Hier stehen die inkonsistenten Daten für das manuelle Delete mit 92 ==>
DBID in WRH$_EVENT_HISTOGRAM 3518517643 MIN SNAPID 17289 MAX SNAPID 17300
DBID in WRH$_EVENT_HISTOGRAM 3582112766 MIN SNAPID 12873 MAX SNAPID 35793
–
===0> Und hier stehen die aktuellen Daten für den zeitnahen Bereich ==>
DBID in DBA_HIST_SNAPSHOT 3582112766 MIN SNAPID 35794 MAX SNAPID 35999 MINT 09-OCT-17 12.00.57.977 AM MAXT 17-OCT-17
01.00.04.394 PM

Wir haben hier folgende zu löschenden Bereiche:

DBID 3518517643 Snapid von 17289 bis Snapid 17300
DBID 3582112766 Snapid von 12873 bis Snapid 35793

Erster Lauf:  (verkürzte Darstellung, „Klicke“ zum Aufklappen des Outputs)

Den vollständigen Report finden wir hier:  (Longreport)

Wir rufen jetzt nach Abschätzung des Zeitbedarfs für den aktuellen Lauf sowie des Verhältnisses der Schrittgröße zum Restbereich das Script erneut mit einem unteren Snapidrange auf, iterativ solange bis wir die Untergrenze der aktuellen Snaps erreicht haben.  Natürlich kann man das auch aus Bequemlichkeit am Stück machen, jedoch muss man dann auf die Resourcen aufpassen. Weiterhin ist in dem Script ein Limit (Selbstbeschränkung) auf die Zahl der internen Iterationen gesetzt. Schlägt dieses zu, meldet das Script, dass es wiederholt aufgerufen werden möchte!

Dann sind wir dann fast fertig und bereit,  den freien Platz wiederzugewinnen, aber nur fast.

Nach dem Löschen folgen zwingend noch Nacharbeiten, die den Platz, den die gelöschten Objekte durch Fragmentierung implizit verbrauchen, erst nutzbar machen.

Und wenn dann die verwaisten Snapids gelöscht und damit theoretisch viel Platz geschaffen wird, muss der Platz noch wirklich physisch instantiiert werden. Dies geschieht erst durch Reorganisieren der zutiefst fragmentierten Objekte.
Eine Übersicht verschaffen kann man sich mit Hilfe des Segment Advisors im EM12c.

Ausschnitt aus der Empfehlung des Segment Advisors:

Hier sehen wir Zeile für Zeile, wie wir Platz gewinnen können. Bei dem großen Beispiel waren das gerne mal  bis zu 500 MB pro Objekt.

Am krassesten ist das Verhältnis nach der ersten großen Bereinigung, siehe Schaubild (draufklicken für Vergrößerung)

Für diesen Task haben wir Scripte erstellt, die die grobe Fleißarbeit übernehmen. Grade bei den partitionierten Indizes ist es manuell etwas mühsam, da die Partitionen für den entsprechenden Befehl im SQL Plus mit selektiert und angegeben werden müssen. Das passiert, wenn man es „am Stück“ probiert:

Einigermaßen bequem ist es noch mit dem SQL Developer, da kann man auch ein größeres Objekt interaktiv reorganisieren, indem man die aufklappenden Partitionen selektiert:

Um diesen ebenfalls mühsamen Teil zu automatisieren, haben wir wieder Scripts geschaffen, die diesen Teil übernehmen.

Hier ein Link auf die Reorg-Scripte für Table- und Index (-Partitionen)

Nachdem wir diese Scripte ausgeführt haben, sind wir fast fertig. Nachdem die Tabellen reorganisiert sind, sind die Indizes teilweise UNUSABLE.

Warum ist das von Relevanz? Nun, es hat nicht nur die üblichen bekannten Auswirkungen (eventuell Zugriff auf eine Tabelle langsam).

Nein – es hat beachtliche globale Auswirkungen!

Da die Indizes „unusable“ sind, läuft der ADDM-Run im Scheduler auf Fehler und erzeugt keine Snapshots (Statistiken) mehr. Und das kann tiefgreifende Auswirkungen nach Datenänderungen in vollkommen unbeteiligten Tabellen haben, die Statistiken können nicht mehr erneuert werden!

Daher müssen wir unbedingt noch den nächsten Task durchführen:

Identifizieren der Indizes im Status Unusable gefolgt vom Rebuild der betroffenen Indizes.

Wir können diese jetzt anlisten und anfangen, sie wieder herzustellen.
Es gibt im SQL-Developer eine schöne View für die nicht brauchbaren Indizes.

Wir finden Sie im Navigations-Seitenbereich unter den fertigen Berichten:

So sieht es dann aus:

Nach Ausführen unseres Scriptes leert sich diese Sicht dann (Aktualisieren Button)

Wir können das auch in der Console ermitteln:

Leider ist die Thematik so komplex, dass dieses Vorgehen nicht ausreicht.

Aufgrund eines Seiteneffektes wirkt es so, als würden sich die Indizes gegenseitig beeinflussen, was du vorne richtest, fällt hinten wieder um, sozusagen. Nachlesen kann man dieses seltsame Verhalten unter
Rebuilding Many Indexes on Sysaux Causes ORA-1502 Error on ‚SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST‘ (Doc ID 2294203.1)

Auszug:
After executing „alter table WRI$_OPTSTAT_IND_HISTORY move“, the indexes on WRI$_OPTSTAT_IND_HISTORY e.g. I_WRI$_OPTSTAT_IND_OBJ#_ST
have became UNUSABLE. In this case, through executing „alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild“, it will return to valid status.
But if indexes on WRI$_OPTSTAT_IND_HISTORY are not rebuilt first and other indexes rebuild due to index rebuilding while gathering index statistics and
insert old index statistics into WRI$_OPTSTAT_IND_HISTORY automatically, the recursive SQL of insert into WRI$_OPTSTAT_IND_HISTORY will fail with ORA-01502.
This error is a expected behavior, and this problem is caused by the incorrect index rebuilding order.

Dies betrifft im Wesentlichen zwei Indizes und kann mit diesen beiden expliziten Befehlen im Startbereich des Scriptes fixiert werden:

ALTER INDEX SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST REBUILD TABLESPACE SYSAUX
ALTER INDEX SYS.I_WRI$_OPTSTAT_IND_ST REBUILD TABLESPACE SYSAUX

Um die hohen Anzahl von Datenbanken automatisiert bearbeiten zu können, wurden auch dazu Scripte erstellt.

Jetzt können wir schauen, wie viel Platz wir gewonnen haben.

Die Datendatei(en) zu SYSAUX kann/können dann auf die entsprechende Minimalgröße „geshrinkt“ werden.
Folgendes Script analysiert das und gibt gleich Handlungsempfehlungen (auf Seite ganz unten)  05_RESIZE_COMMAND.SQL 

FileSize SYSAUX 13713
BEFORE : Allocierter Platz (GESAMT) in SYSAUX is 4326,25 MB
DB : Freespace in SYSAUX is 9386,75 MB
BEFORE : Groesse AWR Objekte in SYSAUX is 288,6875 MB
DB : Freespace in SYSAUX is 9384,75 MB

File /u01/oradata/DB/datafile/o1_mf_sysaux_8xtqyfp2_.dbf smallest 6857 currsize 6966 max. Ersparnis 109
File /u01/oradata/DB/datafile/o1_mf_sysaux_8xtrp6s7_.dbf smallest 6728 currsize 6747 max. Ersparnis 19

Optimale empfohlene Mindestgrösse von 5624 MB

ALTER CMDS TO GAIN DISK SPACE:

alter database datafile ‚/u01/oradata/DB/datafile/o1_mf_sysaux_8xtqyfp2_.dbf‘ resize 6857m;
alter database datafile ‚/u01/oradata/DB/datafile/o1_mf_sysaux_8xtrp6s7_.dbf‘ resize 6728m;

Nach dem Ausführen des „Alter Befehls“ in SQLPLUS haben wir den nicht fragmentierten Platz in SYSAUX gewonnen. Bei einigen Datenbanken ging SYSAUX von 12GB auf 3GB, bei anderen hatten wir danach nur 10GB freien Platz, aber die Datei ließ sich nicht verkleinern. Stichwörter: Highwatermark, Fragmentierung

Eine kurze Hilfe zur Untersuchung warum wir nicht mehr Platz freibekommen, betrachten wir unter anderem auch hier.

Bitte senden Sie eine email, wenn Sie an nicht veröffentlichten Scripten interessiert sind.