Die Kurzfassung des Problems stellte sich so dar:
Nach einem Neustart der Datenbank hatte man zunächst schlechte Antwortzeiten, nach circa 30 Minuten waren die Antwortzeiten hervorragend. Im Laufe des Tages wurden diese Antwortzeiten kontinuierlich schlechter. Dass die Antwortzeiten nach dem Neustart schlecht, und danach gut sind, ist erklärbar (Warm-Up der Buffer). Aber warum wurden die Antwortzeiten im Laufe der Zeit immer schlechter?
Da die Datenbank nicht permanent neu gestartet werden konnte, ist der Kunde auf die Idee gekommen den Shared-Pool jede zwei Stunden zu löschen. Dadurch hatte man alle zwei Stunden wieder schlechte, aber circa ein bis zwei Stunden wieder gute Antwortzeiten. Den Shared-Pool alle zwei Stunden zu löschen ist keine gute Idee. Ich habe meinem Kunden erklärt, dass dies eine "Künstlich herbeigeführte Demenz" für die Datenbank ist. Dies sollte auf jeden Fall in produktiven Umgebungen vermieden werden.
Der Automatic Workload Repository (AWR)
Oracle hat viele Möglichkeiten Problemfälle zu analysieren. Der AWR ist ein guter Beginn dem Problem auf die Spur zu kommen. Die betroffenen SQL-Statements waren selbst nicht auffällig. Im AWR gibt es den Report "SQL ordered by Version Count", der hilft bei der Lösungssuche. Die MOS-Note (Doc ID 296377.1) gibt hierzu nähere Informationen.
In dieser Liste werden alle SQL-Statements mit einem Version-Count > 20 protokolliert. Ist der Version-Count hoch (hunderte oder tausende) kann dies katastrophal für die Anwendung sein. Genau das war hier der Fall. Im Laufe des Tages wurden die Version-Counts von verschiedenen Statements immer höher. Von einigen hundert bis zu mehreren tausend Versionen pro SQL-Statement.
Zu viele Child-Cursor
Oracle muss bei jedem SQL zunächst prüfen, ob der Parent Cursor vorhanden ist. Danach wird geprüft, ob es einen passenden Child gibt. Dies kann aber nur sequenziell erfolgen. Nach der Prüfung wird entweder ein bereits vorhandener Child genommen (Soft-Parse), oder es wird ein neues Child (Hard-Parse) angelegt. Es gibt viele verschiede Gründe warum viele Child-Cursor entstehen. Es ist häufig ein Applikationsproblem. Die MOS-Note (Doc ID 120655.1) hilft bei der Analyse.
Nach der Analyse müssen die daraus resultierenden Maßnahmen umgesetzt werden. Bei Produktiven Systemen kann das mehrere Monate dauern.
SQL-Statements löschen
Was kann in der Zwischenzeit unternommen werden, um das Problem einigermaßen in dem Griff zu bekommen?
Ich empfehle hier die in Frage kommenden Statements zu identifizieren und gegebenenfalls einzeln aus dem Shared-Pool zu löschen. Hierzu bietet es sich an, einen Job zu erstellen, der dies jede Minute prüft und die betroffenen Statements löscht. Aus meiner bisherigen Erfahrung sollten alle SQL-Statements mit einem Version-Count > 150 gelöscht werden.
Einen entsprechenden Job kann ich Ihnen gerne zu senden (E-Mail: Bruno@~@Cirone.de), da dies hier zu weit führen würde.
Nach der Lösung dieses Problems wurde ich wieder begrüßt mit: "Hallo Bruno schön, dass du da bist, willst du einen Kaffee?" Eine Begrüßung, die mir am liebsten ist!
Bruno Cirone
Mehr zum Thema auf der DOAG 2024 Konferenz + Ausstellung vom 19. bis 22. November im Nürnberg Convention Center.
