Einstellungen für den Plan Cache

[dschroeder]

Hallo,
nachdem wir unser Wissen über SQL Performance durch einen interessanten Workshop bei Birgitta Hauser auf den neuesten Stand gebracht haben, wollten wir zur Tat schreiten und gleich ein paar Optimierungen vornehmen.

Dabei bin ich auf eine Merkwürdigkeit beim Plan Cache gestoßen. Bisher war der Plan Cache bei uns auf eine fixe Größe von 1024 MB eingestellt. Diese Einstellung hat wahrscheinlich mal ein anderer Performance-Berater vor einigen Jahren festgelegt und wir haben sie nie geändert.

Laut IBM wird aber empfohlen, die Default-Einstellungen zu verwenden. Die besagen, dass das System den Plan Cache selbst reguliert. Wir haben deshalb alle Werte auf die Default-Einstellungen gestellt.
Ich beobachte jetzt aber, dass der Plan Cache bei uns relativ klein bleibt (max 512 MB). Er läuft bei uns in wenigen Minuten über, ohne das die eingestellte Hit Ratio von 90% erreicht wird. Eigentlich müsste das System den Plan Cache so lange vergrößern, bis die Hit Ratio erreicht ist (denke ich jedenfalls).

Hat jemand eine Idee, woran das liegen kann?

Mein Vermutungen gehen in folgende Richtungen:

1. Vielleicht wirken einige einige Einstellungen erst nach dem nächsten IPL. Das haben wir seit dem noch nicht gemacht. Allerdings hat sich die fixe Größe des Plan Cache bereits sofort geändert (von fix 1024 MB auf "flexibel", aber 512 MB)
2. Oder ein anderes Limit (z.B. Speicher) verhindert eine Vergrößerung. Kann das sein? (Eigentlich sollte bei unserer Maschine weder Hauptspeicher noch Platte irgendein Problem darstellen.)

LG, Dieter

[camouflage]

Was ich darüber gefunden habe:

The SQL Plan Cache is cleared during each IPL. The Plan Cache is a repository that contains the access plans for queries that were optimized by SQE. The Plan Cache is interrogated each time a query is executed in order to determine if a valid access plan exists that satisfies the requirements of the query. If a valid access plan is found, it is used to implement the query. Otherwise a new access plan is created and stored in the Plan Cache for future use.

[dschroeder]

Vielen Dank,
das hatte ich allerdings auch schon gelesen. Ich denke, ich habe grob verstanden, wie die Optimierung über den Plan Cache läuft. Die Frage ist, warum er sich nicht so verhält, wie beschrieben.
Aber ich denke, da müssen wir mal IBM fragen.

LG, Dieter

[Fuerchau]

Ich denke mal, dass die Häufigkeit der Verwendung ebenso eine Rolle spielt.
So wird u.U. eine Lebensdauer verwaltet und um den Cache klein zu halten (Suchzeitenoptimierung) werden länger nicht benötige Optimierungen verworfen.
Unabhängig vom Plancache ist davon aber eine vernünftige Indexstrategie. Häufige Zugriffe sollten über einen permanenten Index verfüügen. Dies hält den Plancache automatisch klein.

Ehrlich gesagt habe ich mich darum nie gekümmert sondern meine SQL's immer so analysiert, dass Indizes verwendet werden.

[dschroeder]

Hallo Baldur,
sorry, dass ich erst jetzt auf deinen Beitrag antworte. Ja, du hast Recht. Mit einer passenden Indexstrategie treten viele Problem gar nicht erst auf und für den normalen Hausgebrauch reicht das auch. Das machen wir hier genauso.

Allerdings gibt es ja weitergehende Optimierungsmöglichkeiten, die dann eher Mikroverbesserungen sind. Wie z.B. dass man einen auf den ersten Blick sinnlos erscheinenden zusätzlichen Index erstellt, der aber dafür sorgt, dass alle Daten direkt aus dem Index gelesen werden und dann kein zusätzlicher Zugriff auf die physische Tabelle gemacht werden muss.

Wir haben bei uns sehr viele Abfragen parallel laufen, weil bei uns viel gesynct wird. Da möchte ich an der einen oder anderen Stelle mal gucken, ob eine kleinteilige Optimierung bei bestimmten Jobs etwas verbessert. Wobei wir grundsätzlich erstmal kein echtes Performanceproblem haben, aber das heißt ja nicht, dass es nicht noch besser laufen könnte.

Ich bin über manche Daten selbst überrascht, die uns das Performance Center anzeigt. Wenn ich das richtig interpretiere, sollen über 300.000 Queries parallel ausgeführt werden. Das kann ich mir kaum vorstellen, auch wenn man jeden Einzelsatzzugriff mitzählt, der ja seit einigen Releases im Hintergrund immer über die SQL-Engine verarbeitet wird.

Hier mal ein aktueller Ausschnitt aus unserem Performance Center:



Für mich sieht es so aus, als würden 332535 Queries "currently", also gerade jetzt ausgeführt. Finde ich viel.

LG, Dieter

[Fuerchau]

Das würde ja bedeuten, dass aktuell auch soviele SQL-Jobs laufen, neben den vielen anderen.
Physikalisch kannst du (Anzahl CPU's mal Anzahl Platten) tatsächliche Abfragen parallel fahren.
Andererseits können auch viele kleine Abfragen unte 0,001 Sekunden auch aus dem Cache befriedigt werden und wie der Snapshot zählt weißt du ja auch nicht.
Wenn bedenkt, dass man durchaus mehrere 1000 Inserts/Sekunde durchbekommt, mit Index fast genauso viele Updates oder Deletes.

Die Mehrzahl der Abfragen sind ja weniger Reports auf Basis von mehreren 100.000 Zeilen sondern Einzelsatz/Einzelbeleg-Abfragen. Und da reicht ja genau 1 passender Index.

Für unser DWH und Reportingsystem (Web-Dashboard) bilde ich Indizes automatisch auf Grund der Abfragebestimmungen (Where-Klausel). Allerdings ist unser DWH auch auf einer Firebird-DWH um die Quellsysteme (ins besonders SQL-Server) zu entlasten.

Bei der IBM i sind die SQL-Serverprobleme bei größeren Resultsets schlichtweg nicht vorhanden.
Such mal nach Lockescalation. Bei bestimmten Abfragen und Transaktionen (Read Committed) kann gleich die gesamte Tabelle gegen Veränderungen gesperrt werden bis die Abfragetransaktion committed hat. Da fragen sich schnell ein paar hundert User, wer denn da schon wieder Power-BI-Abfragen lädt.
Denn die anderen Transaktionen laufen da nicht auf timeout sondern werden bis zum Ende geparkt.
Aber versuche das mal als schlechtes Konzepte den Microsoft-SQL-Spezies darzustellen.

Was die Performance generell angeht hatte ich mal einen Zwangsvergleich zu einem SQL-Server.
Hier wurden Daten per ODBC in den SQL-Server gepumpt. Laut "Microsoftspezialist" liefert die IBM i die Daten zu langsam. Der SQL-Server könnte erheblich mehr.
Bei einem Beispiel mit einer Tabelle über 8 Felder konnte der SQL-Server ca. 12000 Zeilen/Sekunde einfügen. Mit einem kleinen C#-Programm konnte ich aber bis zu 280.000 Zeilen/Sekunde abrufen.
Laut "Microsoftspezialist" hätte ich da ein Fakeprogramm geschrieben. Die Quelle haben die sich gar nicht angesehen und selber ausprobiert. Selbst per SQL-Server-Bulkload kam ich dann nur auf ca. 30.000 Zeilen/Sekunde, was aber der "Microsoftspezialist" auch als Fake abgewiesen hat, da ich ja kein "Microsoftspezialist" bin.

[dschroeder]

Ja, vielleicht ist der Namensteil "Micro" in Microsoft tatsächlich aussagekräftig :-)