©2005

Monitoring und Analyse der Performance

von Oracle Datenbanken (Teil I)

Autoren: Thomas Fritsch und Dr. Rudolf Caspary, Realtech system consulting GmbH

DOAGNews Q4_2005

Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere

die der Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen und

Tabellen, der Funksendung, der Mikroverfilmung oder der Vervielfältigung auf anderen

Wegen und der Speicherung in Datenverarbeitungsanlagen, bleiben, bei auch nur auszugs-

weiser Verwertung, vorbehalten. Eine Vervielfältigung dieses Werkes oder von Teilen dieses

Werkes ist auch im Einzelfall nur in den Grenzen der gesetzlichen Bestimmungen des

Urheberrechtes der Bundesrepublik Deutschland vom 9. September 1965 in der jeweils

geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig. Zuwiderhandlungen

unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.

 ©2005

Oracle Datenbanken stellen die Grundlage vieler geschäftskritischerApplikationen dar. Die Performance dieser Anwendungen hängt direkt von derLeistungsfähigkeit der zugrunde liegenden Datenbank ab. Die Art und Weise,wie diese Datenbank und die durch sie genutzten Ressourcen konfiguriertsind und wie diese Ressourcen genutzt werden, ist dabei von entscheidenderBedeutung.

Nur durch die laufende Messung der entscheidenden Performance-Wertekönnen detaillierte Informationen über den dauerhaften Betrieb derentsprechenden Lösung gewonnen werden. Informationen, die notwendigsind, um gezielte Tuning-Maßnahmen erfolgreich umsetzen und Investitionenin neue Hardware bedarfsgerecht ausrichten zu können. WelcheInformationen bei einer derartigen Analyse gewonnen werden können undwelche Rückschlüsse daraus zu ziehen sind, ist Grundlage dieses Artikels.

Als Basis für die folgenden Ausführungen wurde unter Laborbedingungen einSAP-System mit simulierten SAP-HR- und SAP-FI-Transaktionen analysiert.Ziel war es, unter diesen Lastbedingungen die Performance der Oracle-Umgebung detailliert zu messen. Die kritischen Performance-Werte wurdendem “Oracle Performance Tuning Guide” (Peter Corrigan, Mark Gurry,O’Reilly,  ISBN 1-56592-048-1) entnommen. Ziel war, nicht wie üblich durchSpot-Analysen von SQL-Statements oder Tracing die Applikation zuuntersuchen, sondern durch permanente Messung der entscheidendenGrößen einen Gesamteindruck über den Dauerbetrieb zu erhalten. Zu diesemZweck mussten Daten vom Betriebssystem, der Datenbank und den SAP-Applikationen gesammelt und analysiert werden. Zur Vereinfachung undAutomatisierung der daraus resultierenden Aufgaben wurde die Software-Lösung theGuard! ApplicationManager eingesetzt.

Teil eins des Artikels beschäftigt sich mit dem prinzipiellen Verfahren derAnalyse und den Ergebnissen einer ersten Analyse über 24 Stunden.Der zweite Teil geht näher auf spezifische Resultate und einzelnePerformance-Werte ein und vergleicht diese mit der ersten Analyse.

 ©2005

Rahmenbedingungen im Detail

•  Windows Servero Intel, 1 Prozessor, 647 MHz, 770MBRAM

o Windows 2000•  Datenbank: Oracle 8.1.7.3.0

o Größe: 26 GBo Anzahl Tabellen: 30.000o DB-Blocksize: 8ko Archive Mode

•  SAP: Rel. 4.6C auf der gleichen Maschineo Transaktions-Mix von HR- und FI-Transaktionen

o Simulation von bis zu etwa 50Benutzern mit Dialog-Aktivität – alle zehnMinuten werden entsprechende SAP-Jobsgestartet

o Aktivitäts-Schwerpunkte zwischen 8:00 und11:00 Uhr vormittags bzw. 13:00 und 16:00 Uhr nachmittags

o Transaktionsgruppe HR bestehend aus den Einzel-Transaktionen: PEPP, PRMD

o Transaktionsgruppe FI bestehend aus den Einzel-Transaktionen: FD01, FD02, FK01, FK02

Überwachung, dauerhafte Aufzeichnung und Interpretationder Daten

Der Dauerbetrieb wurde aufgezeichnet (s. Abb.2). Pro Applikationstyp kamendabei folgende Datenkollektoren (DC) zum Einsatz:

•  Windows DC (alle Oracle- und SAP-Prozesse)•  Oracle DC (alle Oracle Performance-Werte)•  SAP DC (alle SAP Performance-Werte)

Alle Daten wurden in der mitgelieferten relationalen Datenbank aufgezeichnetund können entweder mit dem Echtzeit-Monitor oder dem Reporting-Moduledargestellt werden.

Die Datensammlung wurde über zu konfigurierende Regelwerke realisiert(siehe Abb. 3)

Abb.1: Windows Servermit SAP und Oracle

 ©2005

1.1  Managed Object Type: Datenbank

Abb. 3: Regelwerke zur Datensammlung

Abb.2: Ermittlung aller Werte mit theGuard! ApplicationManager

Abb.3: Regelwerke zur Datensammlung

 ©2005

Folgende Aspekte waren bei der permanenten Sammlung und Auswertungder Daten besonders zu beachten.

Einfluss der Messung auf das Ergebnis

Schon Schrödingers Theorem “Jede Messung beeinflusst auch das Ergebnis”weist darauf hin, dass hier besondere Vorsicht geboten ist. Nicht jede Lösungist daher für eine solche Analyse geeignet.

Der Oracle-Datenkollektor verwendet das Oracle Call-Interface OCI zurKommunikation mit der Datenbank und verfügt über eine permanenteVerbindung mit der Oracle-Instanz. Dies bewirkt, dass der Einfluss derÜberwachungslösung auf das Messergebnis besonders gering und damit zuvernachlässigen ist. Trotzdem ist auch hier ein kleiner Einfluss zuberücksichtigen (siehe Abb. 4).

b) Normierung der Daten:

Oracle sammelt alle Performance-relevanten Daten in V$, X$ und anderenSpeicher-/Tabellenstrukturen. Oft werden diese Daten allerdings nur seit DBStartzeit gesammelt, sodass dynamische Schwankungen nur schwer zuerkennen sind. Daher kann man in ApplikationManager drei Arten von Zählernfür den gleichen  Performance-Wert definieren und analysieren:

•  Sum-Counter: Seit DB-Startzeit•  Delta-Counter: Variation seit dem letzten Messpunkt•  Scale-Counter: Delta-Counter, normiert und extrapoliert auf die letzten60 Minuten, um eine Unabhängigkeit von der Messfrequenz zuerreichen.

Abb.4: CPU Verbrauch durch periodische Messung (Skalierung x10, Taktrate: 10sec): 6% derCPU (Intel, 1 Prozessor, 647 MHz) wird durch die Messung selbst verbraucht

 ©2005

Performance-Analyse

Die folgenden Performance-Werte wurden für die drei Systeme analysiert:

für Oracle:

Kategorie Wert Bedeutung/Ursache/Aktion EmpfehlungBufferCache

HitRatio Reads ev. Buffer Cache zu klein > 0.95 (95%)

BufferCache

LogicalReadGets Verfolgung der Lese-Last aufdem Puffer

BufferCache

Buffer BusyWaits

ev. Konflikte auf den Rollback-Segmenten

VerhältnisBuffer BusyWaits /Logical readGets < 0.1(10%)

BufferCache

User Calls Verfolgung der Anfragen

BufferCache

Recursive Calls Rekursive Anfragen (durchDictionary Probleme,Fragmentierung, etc.)

VerhältnisRecusiveCalls / UserCalls < 0.05(5%)

BufferCache

Transactions Verfolgung der Transaktions-Last(commits, rollbacks)

RedologBuffer

Redo entries Verfolgung der Anfragen an denRedo-Buffer

Sorts Memory  Sorts Sortiervorgänge im SpeicherSorts Disk Sorts Sortiervorgänge auf der Platte

(Temporary Tablespace)Verhältnis(MemorySorts – DiskSorts) /MemorySorts > 0.95(95%)

DataDictionaryCache

HitRatio < 0.05 (5%)

I/OStatistics

Reads Verfolgung der Aktivität,Vergleiche zwischen Datendateien

I/O Writes Verfolgung der Aktivität,

 ©2005

Statistics Vergleiche zwischen DatendateienI/OStatistics

Read Time /Block Reads

Verfolgung der Aktivität,Vergleiche zwischen Datendateien

I/OStatistics

WriteTime/BlockWrites

Verfolgung der Aktivität,Vergleiche zwischen Datendateien

für SAP:

Kategorie Wert Bedeutung/Ursache/Aktion EmpfehlungR3 ServiceDialog

Users Verfolgung der Aktivität

R3 ServiceDialog

Dialog Steps Verfolgung der Aktivität

R3 ServiceDialog

Utilisation Verfolgung der Aktivität, ev. mehrSpeicher für Dialog-Prozesse

R3 ServiceDialog

DB RequestTime

Verfolgung der Aktivität, Analyse

FI –Performance

Response-Time (perDialog Step)

Verfolgung der Aktivität, Analyse

FI –Performance

DB Call Time(per DialogStep)

Verfolgung der Aktivität, Analyse

HR –Performance

Response-Time (perDialog Step)

Verfolgung der Aktivität, Analyse

HR –Performance

Response-Time (perDialog Step)

Verfolgung der Aktivität, Analyse

für das Windows-Betriebssystem:

Auf Windows gibt es nur einen einzige Oracle-Prozess. Auf UNIX würde essich zusätzlich lohnen, die einzelnen Oracle-Prozesse einer Oracle-Instanz<SID> aufzuzeichnen, die für die verschiedenen Aufgaben verantwortlichsind:

•  System: ora_smon_<SID>•  Process:  ora_pmon_<SID>•  Checkpoint: ora_ckpt_<SID>•  LogWriter:  ora_lgwr_<SID>•  Archiver:  ora_arc0_<SID>•  DB Writer: ora_dbw0_<SID>

Die Aufzeichnung der Verbrauchswerte einzelner Prozesse und der Vergleichmit den Performance-Werten anderer überwachter Objekte erlauben dasAufspüren von Engpässen und entsprechende Feintuning-Maßnahmen.

 ©2005

Kategorie Wert Bedeutung/Ursache/Aktion EmpfehlungOracleProzesse

 CPU Vergleiche,  Prüfung  auf  freieKapazität,  Vergleich  mit  dergesamten HW Kapazität

OracleProzesse

Memory s. oben

SAPProzesse

CPU s. oben

SAPProzesse

Memory s. oben

AlleProzessebzw.Prozessor

CPU s. oben

AlleProzessebzw.Prozessor

Memory s. oben

Ergebnisse der Performance-Messungen

SAP-Lastanalyse

Abb.5: SAP-Benutzer Abb.6: SAP Dialog-Schritte

 ©2005

In der simulierten Umgebung sind täglich bis zu 50 SAP-Benutzerangemeldet, die zusammen ca. 600 Dialogschritte ausgeführt haben.

Die Hauptlast in den zwei Aktivitätsperioden (8:00 – 10:00 und 12:30 – 15:00Uhr) ist auch hier deutlich sichtbar. Die Antwortzeiten der Datenbank liegtgegen 8.15 Uhr bei 7000ms pro Dialogschritt, was einem sehr schlechtenWert entspricht.

Oracle Lastanalyse – Generelle Performance

Die Hauptlast bei den Benutzeranfragen trat zwischen 1:00 und 2:00 Uhr auf.Wie sich durch die Analyse herausstellte, liefen drei sehr große Anfragenbzw. DB-Jobs während dieser Zeit – verursacht durch externen DB-Programme der SAP zur Erneuerung der Oracle Cost-based Optimizer (CBO)Statistiken:

Sapdba –u / -checkopt PSAP% (zwischen 1:10 und 2:02 Uhr)Sapdba –u / -analyze DBSTATCO (zwischen 3:10 und 3:36 Uhr)Sapdba –u / -next PSAP% (zwischen 5:03 und 5:05 Uhr)

Abb.7: Speicherverbrauch der SAP Dialog-Prozesse Abb.8: DB Antwortzeit pro SAP Dialog-Schritt

Abb.9: Oracle Benutzeranfragen Abb.10: Oracle Benutzer-Transaktionen

 ©2005

Durch den Vergleich mit den Transaktionen kann man sehr gut erkennen,dass die DB-Jobs weniger transaktionalen Charakter haben.

Das Buffer-Cache-Hit-Ratio liegt im Minimum bei 89% und es gibt bei Buffer-Busy-Waits ein deutliche Spitze um 8:00 Uhr. Hieraus ist abzuleiten, dass derBuffer vergrößert werden müsste.

Oracle Lastanalyse – Disk I/O

Die Schreib-Last durch auf den Tablespace PSAPSTABD (SAP-Stammdaten)wird durch die SAP-Tages-Transaktionen erzeugt.

Die Größe von 1200 Blöcken pro Stunde (x Blocksize = 8k) entspricht einerRate von 8MB/h. Dies ist um Größenordnungen kleiner als die Rate üblicherFestplatten (z.B. 320MB/sec bei UltraSCSI). Daher kann I/O prinzipiellausgeschlossen werden!

Abb.11: Oracle Buffer Cache – Hit Ratio Abb.12: Oracle Buffer Cache – Buffer Busy Waits

Abb.13: Oracle –  Schreibvorgänge auf dem Tablespace PSAPSTABD

 ©2005

Der System-Tablespace ist der mit Abstand am meisten ausgelesene (READ)-Tablespace (aufgrund der sapdba-Läufe). In einer echten Kundenumgebungkönnte man auf Basis dieses I/O-Vergleichs Tablespaces auf schnellere Plattenverlagern, wenn es prinzipiell Output-Engpässe geben würde.

Abb.14: Oracle –  Lesevorgänge pro Tag auf allen Tablespaces im Vergleich

Abb.15: Oracle –  Schreibvorgänge pro Tag auf allen Tablespaces im Vergleich

 ©2005

Bei den Schreibvorgängen erkennt man eine ganz andere Verteilung!Führend sind:

  PSAPROLL wegen „Consistent-Reads“ durch Langläufer-Jobs (u.a.Indexierung)

  SYSTEM wegen System-Rollback bzw. Katalog-Updates (sapdba–analyze)

  PSAPSTABD (SAP-Stamdaten) und PSAPBTABD (SAP-Bewegungsdaten) wegen der SAP-Transaktionen

Oracle Lastanalyse – Betriebssystem

Die CPU des Servers ist zu gewissen Zeitpunkten vollständig belastet. DieOracle-Spitzen entstammen den sapdba-Läufen, die besonders viel CPU-Lasterzeugen. Der Oracle-Prozess wird aufgrund des SAP-Tagbetriebs kaumbelastet. Der folgende Vergleich zeigt, dass die CPU-Last weder durch Oraclenoch SAP-Prozesse erzeugt wird.

Abb.16: CPU Zeit auf dem Prozessor Abb.17: CPU Zeit auf dem Prozessor durch Oracle

Abb.18: CPU-Zeit pro Tag aller Prozesse, Oracle und SAP

 ©2005

Der gesamte Speicherverbrauch liegt bei 1 GB und damit deutlich höher alsdas absolute verfügbare RAM von 770 MB. Der Server ist damit ständig am„Swappen“.

Der Verbrauch der Oracle-Prozesse liegt zwischen 100 und 210 MB bzw. imMittel bei 180 MB. Die punktuellen Lastspitzen um 8:00 und 14:00 Uhr führeneher zu einer Reduktion von Speicher, was aus der Verwaltung von Speicherauf Windows und der Prioriätensteuerung resultiert.

SAP hat einen deutlich höheren Gesamtspeicherverbrauch und entzieht zuden Lastzeitpunkten dem Oracle-System eher Speicher.

Fazit der Lastanalyse

•  Oracle ist prinzipiell gut konfiguriert•  der Puffer (Buffer Cache) müsste vergrößert werden (nach einerSpeicher-Erweiterung der Maschine)

•  Die DB-Antwortzeit von SAP ist zu Beginn der Spitzenzeit (8:15 Uhr)zu schlecht

•  Der Server benötigt mehr CPU aber vor allen Dingen mehr Speicher,um Oracle und auch SAP mehr Ressourcen zuweisen zu können.

Abb.19: Speicherverbrauch aller Prozesse Abb.20: Speicherverbrauch der Oracle Prozesse

Abb.21: Speicherverbrauch pro Tag aller Prozesse, SAP und Oracle

 ©2005

•  Die SAPDBA-Läufe zum Refresh des Oracle CBO könnte man evtl.umkonfigurieren (z.B. andere Zeiten, weniger häufig, etc.). Diese undandere SAP-Jobs beeinflussen den Puffer-Inhalt des Buffer-Caches fürdie Laborumgebung so erheblich, dass die SAP Datenbank-Antwortzeiten in Spitzenzeiten unzureichend sind.

Fazit

Die permanente Datenaufzeichnung aller beteiligten Ressourcen ist zusätzlichzu Spot-Analysen ein notwendiges Verfahren, um Datenbanken fürApplikationen effizient zu betreiben.

Kontakt:Thomas Fritschthomas.fritsch@realtech.com