Oracle: Cache hit ratio

[ToFe]

Hallo

wir haben hier mehrere grosse Datenbanken, aus denen überwiegend mittels Web-Anwendungen gelesen wird (das sind so Tomcat-Anwendungen).

Diese Tomcat-Anwendungen bleiben gern mal hängen, auch wenn man ihnen 64 GB RAM gibt und 256MB für die GarbageCollection. Der Anwendungshersteller hat da inzwischen nachgessert, aber "richtig doll" ist das immer noch nicht.

Wie kriegen wir raus, ob die Datenbank der Engpass ist?

Wir haben hier _keinen_ Vollzeit-DBA bzw. hat der zum 30.3. gekündigt, vsl. weil man ihm die versprochene Beförderung doch nicht gegeben hat.

Bis jetzt haben wir da eine Abfrage eingerichtet: " SELECT ROUND(((SUM(PINS) - SUM(RELOADS)) / SUM(PINS) * 100), 2) FROM SYS.V_$LIBRARYCACHE "

Nun meine Fragen:

- Was genau erfasst die Abfrage?

- Wie kann man das aktuelle "Wohlbefinden" der Datenbank besser erfassen. Das ist eine recht zentrale Datenbank, die in die NUR monatlich neue Daten (zusätzliche Reihen) geschrieben werden, und einmal im Jahr EVENTUELL neue Spalten oder Tabellen hinzukommen. Leserate sollte also 99 Prozent++ sein.

Ciao

Bearbeitet 1. März 2012 von ToFe

[carstenj]

Hi,

also es gibt die sogenannten AWR bzw. Statspack Reports, die allerdings eine Menge Informationen liefern und etwas Erfahrung beim Interpretieren der Werte benötigen:

ORACLE-BASE - Automatic Workload Repository (AWR) in Oracle Database 10g

Tuning mit Statspack: Oracle Tool zur detaillierten Analyse von Datenbanken mit Vorteilen Gegenüber dem Tuning Pack des Enterprise Managers, dem BMC SQL-Explorer oder den Quest Werkzeugen

Die Cache hit ratio wird hier erklärt:

Oracle Library Cache Hit Ratio

Statements müssen geparst werden, d.h. es muss geschaut werden, ob der Benutzer die richtigen Rechte auf die Tabellen hat, welcher Ausführungsplan genutzt wird und so weiter. Das kostet Zeit. Daher versucht Oracle, diese Dinge zu speichern (Cachen), damit er diese Statements nicht jedes Mal neu parsen muss.

Jetzt kann es sein, dass der Speicher zu klein dafür ist, und nur eine bestimmte Anzahl von Statements im Speicher gehalten werden kann. Dann muss man, sofern möglich, den Speicher erhöhen. In diesem Fall ist es der shared_pool.

[carstenj]

Hi,

noch ein Nachtrag:

Euer Statement liefert einen Wert. Je höher dieser Wert ist, desto besser. Mögliche Ursachen für zu niedrige Werte könnten sein:

- shared_pool zu klein

- Es werden keine Bind Variablen genutzt

Wenn die Applikation nicht von euch kommt, habt ihr auf letzteres sowieso keinen Einfluss.

[ToFe]

Dass die SQL-Statements alle kompiliert sind: schön und gut. Die ändern sich auch kaum.

Aber das sagt noch nix über die aktuelle Geschwindigkeit der Dutzende Tabellen aus (teilweise 30 Jahre gesammelte Daten) aus.

Da kann "ein Berserker im Internet" eine "kräftige" Abfrage zusammenklicken UND zeitgleich irgendein Entwickler tagsüber ein paar Hundert Tests anstossen, dann wirds der Datenbank "sicherlich" nicht so "gut gehen" als wenn hier in Baden-Württemberg Feiertags ist (ca. 5% Zugriffe aus anderen Bundesländern)

Wie bekomme ich da einen "Durchschnittswert über alle Tabellen", damit ich sehen kann, ob die Java-Tomcat-Anwendung "Mucken macht" (eines Tages muss ich mal ein Buch schreiben "Rolling Releases und was man dabei alles wiederholt falsch machen kann" oder eben die Datenbank (dann ruf ich dort den Abteilungsleiter an, und "schlage vor" einen anderen Testplan zu verwenden)

[carstenj]

Hi,

1. Auch eine kleine Änderung des SQL-Statements kann dramatische Auswirkungen haben. Wenn keine Bind Variablen benutzt werden, kann es vorkommen, dass die Abfrage immer neu neu geparst wird, auch wenn sich nur eine Kleinigkeit geändert hat. Wenn die 100.000 Mal pro Minute ausgeführt wird, kann das schnell das System in die Knie zwingen

2. Tabellen können nicht schnell sein, sondern nur die Abfragen.

Was ist denn jetzt genau deine Frage? Zuerst fragst du, was die Cache Hit Ratio ist. Ich habe dir dann die Statspack und AWR Reports genannt. Hast du dich damit mal befasst?

Ansonsten müsst ihr prüfen, ob Indizes erstellt/genutzt werden, ob die Statistiken aktuell sind, Ausführungspläne angucken. Man muss sich damit schon etwas beschäftigen, so "mal eben" kann man einen Engpass nicht ermitteln.

Handelt es sich bei der Datenbank eher um ein "Data Warehouse" (bei 30 Jahre alten Daten gehe ich mal davon aus) oder eine Transaktionsdatenbank? Auch das ist ausschlaggeben für die Konfiguration.

[streffin]

Datenbank Performance ist eine kleine Wissenschaft für sich.

Wenn ihr in der Richtung momentan Probleme habt ist es vermutlich nicht sooo dolle den Datenbank Menschen gehen zu lassen.

Was die Cache Hits angeht, ich bin eher ein MSSQL Mensch, aber ein paar Dinge sind scheinbar halbwegs übertragbar :

Using SQL Plan Management , im speziellen 15.1.1.2 Manual Plan Loading

Das grundsätzliche Problem :

Die Datenbank cached die Execution Pläne, damit die nicht jedesmal neu errechnet werden müssen (kostet CPU). Wenn die Datenbank erkennt :

"aha, der Filter in der Where Bedingung, ist eine Variabele, da kann ich auch wenn sich der ändert den selben gecacheten Plan benutzen"

Dann ist das gut, und du hast mehr hits auf deinem Cache. Um da zu begünstigen, schreibt man (sollte man schreiben) Querys in einer parametisierten Form, und verwendet Stored Procedures, so dass das erkannt werden kann.

Wenn das jetzt eine 3rd Party Application ist, dann kannst du natürlich keinen Einfluss darauf nehmen, wie da Querys erstellt werden, und die das SQL aussieht.

Ich sprech jetzt ein wenig MSSQL Syntax, aber soweit ich die Oracle Doku überflogen hab, sollte das sich recht analog verhalten.

Wenn ein Query nicht parameterisiert ist, dann führt das bei komplexen Querys dazu, dass die DB den Plan nicht im Cache finden kann. Du kannst allerdings mit Plan Guides auf das Verhalten einwirken.

Unter TSQL gibt es dazu noch Query Hints und globale Datenbank settings, mit dehnen eine Parametisierung erzwungen werden kann, das >kann< enorm viel Performance bringen, aber mit sowas muss man äußerste Vorsicht walten lassen, und wissen was man tut.

Ansonsten ganz allgemein würd ich mir mal die Indizie der Tabellen genau ansehen, fehlen Indize, sind sie Fragmentiert etc etc.

Uralte Daten in ein Backup rausschreiben und dann vom produktiv System schmeissen, solche Dinge.

Wenn sehr wenig Write Operationen auf einer Tabelle ausgeführt werden, dann kannst du mit zusätzlichen Indexen eigentlich auch nichts falsch machen.

Ganz Allgemein würd ich an euerer Stelle aber versuchen eueren Datenbankler zu halten, das ist ein sehr komplexes und weitläufiges Thema

Gruß

Sven

Bearbeitet 1. März 2012 von streffin

[ToFe]

Nö, unserer DBA hat schon woanders unterschrieben, die haben sich richtig bemüht um ihn (sagt er).

Man hätte ihm vielleicht nix versprechen sollen, was man hinterher nicht einhält. Die Stelle ist mind. 1 Jahr gesperrt, selbst dann kommt statt jetziger unbefristeter Stelle eine Stelle mit Zweijahresvertrag - das ist bestimmt "viel interessanter" für Bewerber.

Klarstellung: Ich suche 1 bis 5 SQL-Statements (zusätzlich zu dem im Post #1), die mir sagen "wie beschäftigt" heute die DB ist (einen "Durchschnittswert über alle Tabellen"), z.B. aktuelle Anzahl Festplattenlesevorgänge, Tagesgesamtzahl Festplattenlesevorgänge, etc.

Ich bin kein DBA, und bekomme keinerlei Fortbildung in dieser Richtung - kein Budget.

Ja, so ist Öffentlicher Dienst anno 2012.

[carstenj]

Nochmal: Hast du dir AWR / Statspack mal angeguckt? Das liefert doch das, was du brauchst, und stellt es auch noch übersichtlich dar!

Es gibt keine allumfassenden Statements, wie es der Datenbank geht. Es gibt nur Statements, die Werte liefern, und die Werte muss jemand (Du?), abhängig von vielen Faktoren interpretieren. Cache Ratio ist einer davon. Und ob heute 10342348 oder 29023421 Lesevorgänge stattgefunden haben, was machst du mit dieser Information? Ansonsten schau hier: Oracle :: Oracle

Allerdings würde ich deinem Chef schon sehr deutlichen machen, dass alles was ihr tut ohne DBA ein großes Risiko ist.