SysAux Tablespace in Pluggable Database wächst rasant
Über 5 Gigabyte Wachstum in wenigen Tagen - normal ist das nicht.
Nach der Umstellung unserer Applikationsdatenbanken von Oracle 12c auf Datenbanken in Version 19.6 bzw. 19.8, beklagten sich einige unserer Kunden plötzlich über Probleme mit einem schnell wachsenden SysAux-Tablespace in den Pluggable Databases (PDB), in denen sich die Applikationsdaten befinden. Im Standardsetup besteht bei den betroffenen Kunden ein Datenbanksystem aus einem Root Container (CDB$ROOT) sowie bis zu zwei PDBs. Eine beinhaltet die Applikationsdaten, die zweite stellt ein Repository für Artefakte, die im Weblogic verwendet werden, zur Verfügung.
Das Wachstum des SysAux-Tablespaces in der ApplikationsPDB betrug teilweise mehrere Gigabyte pro Woche, so dass eine ständige Vergrößerung des SysAux-Tablespaces notwendig war. Normal ist ein sehr begrenztes Wachstum des SysAux-Tablespaces, der nach etwa einem Monat "seine" richtige Größe gefunden hat und dann nahezu dauerhaft in dieser Größe verharrt. Aber ein ständiges Wachstum ist nicht normal.
Woher also kommt dieses Wachstum, das einfach nicht aufhört?
Schaut man sich in der betroffenen PDB z.B. mit einem
select occupant_name, space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
an, wer diesen Zuwachs verursacht, landet man beim SM/Advisor, der weit über 90% des SysAux-Tablespace Platzes für sich beansprucht.
Forscht man etwas weiter, kann man den SM/Advisor zurückverfolgen auf den AUTO_STATS_ADVISOR_TASK, der scheinbar mehrfach pro Tag oder pro Woche im Hintergrund in den Maintenance Windows ausgeführt wird.
AUTO_STATS_ADVISOR_TASK - was macht er?
Der AUTO_STATS_ADVISOR_TASK ist ein Optimizer Statistics Advisor. Er soll durch zusätzliche Informationen dem DBA die Möglichkeit geben, bessere Optimizer Statistiken zu generieren oder die Gather_Statistics Läufe zu verbessern. Somit kann er, wenn der DBA den Output des Advisors nutzt, zu einer besseren oder stabileren Performance beitragen. Wer sich etwas intensiver mit dem Optimizer Statistics Advisor und seinen Möglichkeiten beschäftigen möchte, der kann sich im Oracle Blog von Ulrike Schwinn hier genauer informieren. Ulrike Schwinn zeigt, wie man an die Daten des Advisors kommt und welche Möglichkeiten es gibt, die Empfehlungen des Advisors zu implementieren. Das ist seit 12c im Grunde genommen unverändert.
Doch zurück zum Tablespace Sizing Problem. Dieser AUTO_STATS_ADVISOR_TASK füllt die Tabelle WRI$_ADV_OBJECTS, die zusammen mit den dazugehörigen Indexen den Tablespace nahezu komplett nutzt. Das lässt sich mit folgendem Select herausfinden:
SELECT * FROM
(SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024 "SIZE(MB)",SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC
)
WHERE ROWNUM<=10;
Schaut man in die Tabelle WRI$_ADV_OJBECTS hinein , findet man folgende Einträge:
Workaround: Advisor stilllegen und Daten loswerden
Was tun, ist nun die Frage? Die meisten unserer Kunden benutzen die zusätzlich gesammelten Daten für die Reports (siehe Ulrike Schwinns Blogeintrag) überhaupt nicht, um die Statistikgenerierung zu verbessern. Deshalb ist es auch möglich, den Advisor Task einfach zu droppen. Dabei wird auch die Tabelle WRI$_ADV_OBJECTS bereinigt und die alten Daten werden gelöscht. Allerdings wird der belegte Platz im SysAux-Tablespace nicht freigegeben.
Auch Oracle bietet dieses als Lösung in seiner Support Note an (Oracle Support Document 2305512.1).
Mit einem
exec DBMS_STATS.DROP_ADVISOR_TASK('AUTO_STATS_ADVISOR_TASK');
wird man den AUTO_STATS_ADVISOR_TASK also los und die Daten werden aus der Tabelle WRI$_ADV_OBJECTS gelöscht.
Wer nicht ganz so forsch zu Werke gehen möchte, kann den AUTO_STATS_ADVISOR_TASK zuerst disablen
exec dbms_stats.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');)
und ggfs. später droppen. Erstellt ist der Task aber mit einem
exec dbms_stats.init_package();
nach dem Droppen auch schnell wieder.
Schaut man in den Root-Container der Datenbank, sieht man dieses Mengenwachstum hingegen nicht. Forscht man an dieser Stelle etwas weiter, findet man einen nicht öffentlich zugänglichen Bug (ENH-31028071 - Purge expired auto_stats_advisor_task data in pdb), genauer gesagt, handelt es sich dabei um einen Enhancement Request.
Offensichtlich ist hier, seit der Einführung der PDBs und des AUTO_STATS_ADVISOR_TASKs der Output des Advisors nicht in das Auto-Purge innerhalb von PDBs aufgenommen worden. Es nützt deshalb auch nichts, den Parameter EXECUTION_DAYS_TO_EXPIRE für den AUTO_STATS_ADVISOR_TASK auf einen kürzeren Zeitraum innerhalb der PDB zu setzen.
Was allerdings funktioniert, ist ein manueller Purge mittels
exec prvt_advisor.delete_expired_tasks;
Diesen Purge kann man z.B. in einen eigenen Job einbauen, wenn man den Advisor Job behalten möchte.
Belegten Tablespace Platz wieder freigeben
Nach dem Droppen / Purgen der überflüssigen Daten aus der Tabelle WRI$_ADV_OBJECTS muss diese noch bereinigt werden, damit der belegte Platz im SysAux-Tablespace wieder anderweitig verwendet werden kann.
Die Support Note weißt darauf hin, dass man WRI$_ADV_OBJECTS moven soll. Das allerdings funktioniert innerhalb einer PDB nicht so einfach. Sowohl ein move als auch ein move online der Tabelle ist hier nicht zulässig (ein truncate funktioniert, man verliert aber dann auch alle andere Daten, ist also keine wirkliche Lösung).
Deshalb muss ein kleiner Trick angewendet werden - man gibt sich als "Oracle Script" aus. Damit funktioniert dann auch der Table move innerhalb der PDB und das Rebuild der dazugehörigen Indexe. Da das Setzen von "_oracle_script"=true diverseste Sicherheitsmechanismen aushebelt, sollte man diese Technik allerdings nur mit erhöhter Aufmerksamkeit anwenden und die Session so schnell als möglich wieder zurücksetzen (oder besser gleich schließen).
Kommentare
|
Thanks for this thorough yet clearly organized guide. Was a great help.