Real-time Monitoring in der Oracle Datenbank
Monitoring der Datenbank ist mittlerweile für Datenbankadministratoren wie auch für Datenbankentwickler ein wichtiger Bestandteil der (täglichen) Aufgaben. Bei der Implementierung von neuen Anwendungen, neuen Techniken oder Features oder generell bei Testen wird man häufig mit der Herausforderung konfrontiert, schnell und unkompliziert einen Überblick über die Langläufer oder Performance Ausreißer zu erhalten. Nehmen wir beispielsweise an, eine neue Applikation soll in der Datenbank genutzt werden. Bei der ersten Durchführung wird allerdings festgestellt, dass der gesamte Ablauf sehr träge und langsam ist. Jetzt wäre es wichtig herauszufinden, wo das Problem liegen könnte. Liegt es an gewissen Statements, der Parallelisierung, den Ausführungsplänen, oder vielleicht an Ressourcen, die zu knapp bemessen sind?
Hilfestellung zur Beantwortung dieser Fragen liefert dabei die Datenbank selbst. Sie stellt ein eigenes Framework zur Verfügung, um ohne Verwendung von externen Werkzeugen und ohne zusätzliche Installation ein genaues Monitoring zu erlauben. Das „Datenbank Framework“ umfasst seit jeher spezielle Datenbank Views, PL/SQL-Packages und ein spezielles Workload Repository, um nur einige Beispiele zu nennen. Dabei wächst in jedem neuen Datenbankrelease der Funktionsumfang dieses Frameworks – so beispielsweise auch in Oracle Database 11g mit Real-time Monitoring (auch SQL Monitoring genannt) und den Erweiterungen dazu in Oracle Database 12c. Um den Rahmen des Beitrags nicht zu sprengen, wird der Hauptfokus auf das SQL Monitoring gelegt. Zuvor allerdings wird ein Überblick über das Monitoring von langlaufenden Operationen gegeben.
Monitoring von langlaufenden Operationen mit V$SESSION_LONGOPS
Schon seit langer Zeit stehen in der Datenbank spezielle Views zur Verfügung um langlaufende Operationen zu überwachen. Es handelt sich dabei um V$SESSION_LONGOPS bzw. GV$SESSION_LONGOPS. Wie der Name schon andeutet, handelt es sich hierbei um langlaufende Oracle- oder Benutzer- definierte Operationen wie z.B. RMAN-, Data Pump- oder „Statistik Gathering“- Operationen und um bestimmte Abfrage-Operationen. Diese werden automatisch nach Ablauf von 6 Sekunden als langlaufende Operationen in den Views angezeigt. Dauert eine Datenbank-Operation zu lange an, lohnt sich in jedem Fall nicht nur ein Blick in die Alert-Datei, sondern auch in diese V$Views. Sogar Operationen wie beispielsweise vom User ausgeführte ROLLBACK-Kommandos nach einer DML-Operation werden in V$SESSION_LONGOPS mitgeschrieben. Welche Informationen können nun über die V$SESSION_LONGOPS-Views gelistet werden? Folgendes einfache Beispiel im Listing gibt einen Eindruck über die Informationen, die abgefragt werden können. Der Name der Operation, der User, die Start- und Endpunkte der Operation und die verbleibende Zeitdauer gehören dabei zu den Haupteinträgen.
SQL> SELECT opname, username, to_char(start_time,'DD-MON-YYYY HH24:MI:SS'), (sofar/totalwork)*100 prozent, time_remaining FROM v$session_longops WHERE time_remaining>0; OPNAME USERNAME TO_CHAR(START_TIME,'DD-MON-YY ----------------------------------- -------- ----------------------------- PROZENT TIME_REMAINING ---------- -------------- Gather Schema Statistics SYS 09-JAN-2015 12:35:24 66.6666667 1 Gather Table's Index Statistics SYS 09-JAN-2015 12:35:29 80 2
Das Ergebnis zeigt, dass gerade zwei DBMS_STATS-Operationen aktiv sind und in ungefähr 1 bzw. 2 Sekunden (siehe Spalte TIME_REMAINING) beendet sind. Möchte man gezielt nach bestimmten Operationen suchen, eignet sich der Filter auf den entsprechenden Namen wie zum Beispiel %RMAN% für RMAN-Operationen oder %EXPORT% für Data Pump Export-Operationen. Bei Operationen, die Teil eines SQL-Statements sind, wie zum Beispiel Table Scans, sollte natürlich zusätzlich SQL_ID bzw. SQL_HASH_VALUE ausgegeben werden, um den zugehörigen SQL-Text ermitteln zu können.
Die Werte in V$SESSION_LONGOPS werden übrigens aus den Informationen des Cost Based Optimizers abgeleitet, daher sollte der Parameter TIMED_STATISTICS den Wert TRUE beinhalten – das ist auch der Standardwert. V$SESSION_LONGOPS steht in allen Editionen auch in Oracle Database 12c zur Verfügung; keinerlei zusätzliche Lizenzierung zur Nutzung ist erforderlich. Übrigens existiert keine vollständig dokumentierte Liste aller Operationen oder SQL-Abfragen, die mit V$SESSION_LONGOPS überwacht werden können. Man muss sich mit der Aussage begnügen, dass bestimmte User- und Oracle-Operationen protokolliert werden. Damit kann leider nicht sichergestellt werden, dass wirklich alle zeitintensiven Operationen nach Ablauf von 6 Sekunden aufgeführt werden.
Das SQL Monitoring
Wie kann man nun alle langlaufenden SQL-Statements mit hohen Antwortzeiten finden? Welche (Teil-)Operationen dieser SQL-Abfragen sind zeitintensiv? Welche SQL-Statements laufen parallel? Diese Fragen gehen über die Funktionsweise von V$SESSION_LONGOPS hinaus. Hier eignet sich der Einsatz von SQL-Monitoring, das mit Oracle Database 11g eingeführt worden ist. Schnell und ohne Aufwand erhält man damit einen aktuellen und detaillierten Überblick über globale SQL- und PL/SQL-Statistiken von Operationen, die gerade aktiv sind, sich in einer Warteschlange befinden oder gar abgebrochen worden sind. Dazu gehören, wie der Name schon andeutet, in erster Linie SQL-Statements und ihre zugehörigen Abfrage-Operationen. Zusätzlich werden auch langlaufende Operationen wie Data Pump und DBMS_STATS-Operationen oder selbst definierte PL/SQL-Aufrufe überwacht. Im Unterschied zu den AWR bzw. STATSPACK-Reports zeigt das SQL-Monitoring dabei auch Operationen und SQL-Statements an, die im Moment gerade aktiv sind oder sich in einer Warteschleife befinden. Auch SQL-Statements, die nicht unbedingt zu den "Langläufern" gehören, werden automatisch gelistet, falls sie bestimmte Eigenschaften (s. u.) erfüllen. Die Verwendung ist im Linemode über bestimmte Views bzw. Packages möglich. Empfehlenswert ist allerdings die Nutzung der graphischen Oberfläche im Enterprise Manager. Auch hier ist wie bei V$SESSION_LONGOPS keine Installation oder weitere Konfiguration erforderlich. Voraussetzung ist allerdings die Lizenzierung des Tuning Packs. Die Kontrolle über den Einsatz der Management Packs wie Tuning und Diagnostics kann man dabei seit 11g mit dem Initialisierungsparameter CONTROL_MANAGEMENT_PACK_ACCESS sicher stellen. Der Wert DIAGNOSTIC+TUNING ermöglicht dabei den Einsatz der Tuning Pack-Funktionen. Zusätzlich muss der Parameter STATISTICS_LEVEL den Wert TYPICAL (das ist der Defaultwert) haben.
SQL> show parameter pack NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_management_pack_access string DIAGNOSTIC+TUNING SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ statistics_level string TYPICAL
Die Funktionsweise ist dann schnell erklärt: Automatisch – meist ohne zusätzlichen weiteren Eingriff durch den Anwender oder DBA – werden SQL-Statements, Data Pump-Operationen beziehungsweise PL/SQL-Programme, die bestimmten Anforderungen genügen, aufgezeichnet. Das SQL-Monitoring wird automatisch gestartet, falls die Operationen eine der folgenden Voraussetzungen erfüllen:
- Parallele Ausführung
- Verbrauch von mehr als 5 Sekunden CPU- bzw. I/O-Zeit
- Verwendung des MONITOR-Hints.
Kein umständliches Einschalten beispielsweise durchsetzen eines speziellen Trace Events ist zur Nutzung erforderlich.
Das SQL-Monitoring ist über die Views wie V$SQL_MONITOR und V$SQL_PLAN_MONITOR möglich. Die graphische Implementierung ist die einfachste Art der Verwendung und wird über Grid Control 11g bzw. Cloud Control 12c ermöglicht. In Cloud Control 12c beispielsweise ist SQL-Monitoring im Bereich "Performance => SQL-Monitoring" zu finden. Die Spaltenüberschriften geben dabei Auskunft über den Status der Operationen, die Dauer, die Art (wie zum Beispiel SQL, PL/SQL, zusammengesetzte Operation (neu in 12c)), den Parallelitätsgrad, die Datenbankzeit, den I/O-Anforderungen, den Anfangs- und Endzeitpunkt und über die SQL-Texte bzw. PL/SQL-Aufrufe. So ist es beispielsweise möglich, auf den ersten Blick die aufwändigsten Statements (gemessen an Datenbankzeit oder I/O-Anforderungen) zu finden.
Durch einen Mausklick auf die ID gelangt man zu einer erweiterten Detail-Ansicht. Handelt es sich bei der Operation um ein SQL-Statement, werden zusätzlich Ausführungspläne, Planstatistiken oder auch Informationen zu den einzelnen parallelen Prozessen, soweit vorhanden, angezeigt. So kann sehr schnell die langlaufende Operation identifiziert und beispielsweise ein eventueller Ressourcen-Engpass bei einer bestimmten Ausführung ermittelt werden. Bei PL/SQL-Ausführungen hingegen werden die einzelnen SQL-Statements (soweit vorhanden) aufgelistet. Die SQL-Statements sind dann für die weitere Analyse auf die Top Activity-Seite verlinkt. Unabhängig davon wird in jedem Fall der Gesamtverbrauch der PL/SQL-Ausführung an CPU, IO und PGA aufgezeigt. Diese Funktionsweise sollte allerdings nicht mit dem PL/SQL-Profiler in Verbindung gebracht werden, da hierbei keine zeilenweise Analyse des PL/SQL-Codes erfolgt.
Als zusätzliches wichtiges Hilfsmittel können diese Ansichten im Enterprise Manager ganz einfach auch mit Kollegen oder Fachleuten geteilt werden. Diese Funktion nennt sich Active Report-Funktionalität und ist in allen Enterprise Manager-Versionen ab Grid Control 11g enthalten. Active Reports sind interaktive Berichte, die es ermöglichen, die Daten als HTML-Datei zu speichern. So wird ein „Offline“ Viewing oder das E-mailen an Kollegen leicht möglich. Da die Active Reports auf der Flash Player-Funktionalität von Adobe basieren, ist nur die Installation des Plug-ins von Adobe erforderlich – und keine Installation des Enterprise Managers. Die Implementierung von Active Reports im Enterprise Manager ist an unterschiedlichen Stellen zu finden, wie z. B. beim SQL-Monitoring und dem SQL-Performance-Analyzer. Speichern lässt sich der HTML-Report mit dem Button "Save“.
Natürlich lassen sich die Funktionen des SQL Monitorings auch über Abfragen im Data Dictionary nutzen. Dazu ist es notwendig, die entsprechenden Views wie V$SQL_MONITOR und für die Ausführungspläne V$SQL_PLAN_MONITOR bzw. die entsprechenden GV$-Views zu selektieren. Diese Views listen dabei die Statistiken pro SQL-Statement (SQL_ID) auf. Im Unterschied zu anderen V$Views werden die Statement-Informationen einzeln pro Ausführung und nicht wie zum Beispiel in V$SQL kumuliert gelistet. Folgendes Beispiel zeigt ein Ausschnitt aus der V$SQL_MONITOR View.
SQL> SELECT key, sid, username, sql_id, sql_plan_hash_value plan_hash, elapsed_time, cpu_time, buffer_gets, disk_reads, substr(sql_text,1,50) sql_text FROM v$sql_monitor WHERE sql_text LIKE '%sales%'; KEY SID USERNAME SQL_ID PLAN_HASH ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS SQL_TEXT --------------- ----- ------------ ------------- ---------- ------------ ---------- ----------- ---------- -------------------------------------------------- 30064778449 77 SH 8ts0v8vm6ut55 3620391911 14417240 6679985 98 4469 select count(*) from sales a, sales s1 73014451423 77 SH 9xupuyjz6c335 1550251865 3097233 1029832 194 27 select /*+ monitor */ * from sales 77309418720 77 SH 3108004 975869 194 0 select /*+ monitor */ * from sales 81604386012 77 SH dw5pusvf8yq4x 3060979429 2919910 772882 372 43 select /*+parallel */ * from sales
Um die Darstellung übersichtlicher und lesbarer zu gestalten, gibt es zusätzlich die Möglichkeit, mit Hilfe der Funktion REPORT_SQL_MONITOR des Package DBMS_SQL_MONITOR einen Bericht in unterschiedlichen Formaten wie zum Beispiel Text (Defaultwert) oder HTML zu generieren.
Neuigkeiten in 12c
Wie mittlerweile allgemein bekannt sein dürfte, besitzt jede Installation einer Oracle Database 12c eine neue schlanke Verwaltungsoberfläche mit dem Namen Oracle Enterprise Manager Database Express. Database Express löst dabei die bis Oracle Database 11g verfügbare Variante Enterprise Manager Database Control als Verwaltungsoberfläche für eine einzelne Datenbank ab. Schnell aktiviert über die Freigabe eines HTTP bzw. HTTPS Ports bietet Database Express ebenfalls eine graphische Implementierung des SQL-Monitorings an. Schon auf der Homepage werden die langlaufenden Operationen im Bereich SQL-Monitoring angezeigt. Nutzt man den Menüpunkt „Performance=> Performance Hub“ gelangt man auf eine spezielle Performance Homepage.
Die „Performance Hub”-Ansicht ist dabei im Master-Detail-Format angeordnet, wobei die Zeitauswahl über einen sogenannten “Timepicker” den Inhalt der Seite bestimmt. Die Real Time-Ansicht zeigt die durchschnitllicher Active Session-Informationen im Verlauf der letzten Stunde an. Werden Spitzenwerte angezeigt, kann man diese Zeitspanne auswählen und erhält automatisch über den Menüpunkt „Monitored SQL“ die zugehörigen Real-Time-Operationen in der gleichen Ansicht wie vorher im Cloud Control. Möchte man auch hier die Active Reports verwenden, kann man den Button „PerfHub Report“ verwenden. Monitored SQL ist hier übrigens nicht nur als real-time-Variante sondern auch im historischen Modus nutzbar.
Bisher in Oracle Database 11g ließen sich nur einzelne SQL-Statements oder PL/SQL-Programme im SQL-Monitor überwachen. Mit Oracle Database 12c ist es nun möglich, komplexe Operationen in den Monitoring-Prozess einzubeziehen. Die Idee dabei ist, in einer Session den Anfangs- und Endpunkt der zu überwachenden Aktivitäten zu markieren; dies erfolgt über die neuen Programme BEGIN_OPERATION und END_OPERATION des Package DBMS_SQL_MONITOR. Die SQL- und PL/SQL-Operationen, die zwischen den beiden Punkten stattfinden, gehören dann zu einer zusammengesetzten Database-Operation. Typische Anwendungsbeispiele sind die Überwachung von SQL*Plus Jobs, Standard ETL-Prozessen oder von Batch Jobs, die beispielsweise beim Quartalsende anfallen. Die zusammengesetzten Database Operationen sind dann unter einem benutzerdefinierten Namen gelistet und lassen sich ebenfalls sehr gut im Enterprise Manager Cloud Control monitoren.
Verwendet man Oracle Database Resource Manager, um SQL-Langläufer zu überwachen bzw. zu unterbinden, wird man ab Oracle Database 12c auch eine Integration in das SQL-Monitoring finden. Im Ressource Manager ist es möglich, eine Grenze (Threshold) für eine langlaufende Query anzugeben - man spricht hier auch von "Runaway Queries" – und nach Erreichung dieser Grenze wird eine Aktion durchgeführt. Dauert beispielsweise eine Query oder ein PL/SQL-Aufruf mehr als 30 Sekunden (in CPU), dann kann man die Abfrage beenden, einen Wechsel zu einer anderen Resource Consumer Group vollziehen oder sogar die ganze Session beenden. Eine genaues Protokollieren dieser Vorgänge im Resource Manager ist allerdings nicht möglich. Auch hier kann das SQL-Monitoring aushelfen. Zusätzliche Spalten in V$SQL_MONITOR dokumentieren ab Oracle Database 12c, wer diese Runaway Queries ausgeführt hat, welcher Code (SQL oder PL/SQL) verwendet wurde und was für eine Aktion durchgeführt wurde. Möchte man diese Runaway Queries nur überwachen und keine zusätzliche Aktion durchführen, steht im Database Resource Manager die neue Aktion“ LOG_ONLY“ zur Verfügung. Damit erfolgt kein Wechsel zu einer anderen Consumer Gruppe oder gar Abbruch des Statements, sondern nur eine Auflistung in V$SQL-Monitor. So wird es dem DBA leicht gemacht, die entsprechende Resource Consumer Gruppe und die entsprechenden langlaufenden Operationen ausfindig zu machen, ohne dass die Aktionen dieser Gruppe beeinflusst werden.
Fazit
SQL-Monitoring ist mittlerweile eines der wichtigsten Werkzeuge im Datenbank-Monitoring und Tuning-Umfeld. Hat man ein Performance-Problem, sollte man unbedingt zuerst das SQL-Monitoring über die graphische Schnittstelle im Enterprise Manager verwenden, bevor man die Analyse mit AWRs startet oder ein aufwändiges SQL-Tracing beginnt. Die Erweiterungen in Oracle Database 12c zeigen außerdem, dass die Funktionenvielfalt noch nicht erschöpft ist. In Zukunft wird es sicher noch weitere interessante Neuigkeiten dazu geben.
Erwin Spanner
am 03.02.2021