SQL-Anweisungen überwachen: Real-Time Monitoring mit Oracle11g

Dass eine SQL-Abfrage oder eine DML-Anweisung (INSERT, UPDATE, DELETE) mal etwas länger zur Ausführung braucht, hat nahezu jeder Entwickler schonmal erlebt. Und wenn man als Entwickler oder Administrator einer Application Express-Anwendung auf den vollständigen Aufbau der Berichtsseite wartet, fragt man sich schon, was die Datenbank gerade tut bzw. mit welchem Schritt des Ausführungsplans sie gerade beschäftigt ist ...

Überwachung einer gerade laufenden SQL Anweisung
Abb. 1: Überwachung einer gerade laufenden SQL Anweisung

Die gute Nachricht ist, dass Oracle11g ein Real-Time Monitoring anbietet: Mit einem einfachen PL/SQL-Aufruf bekommt man die Information, was die Datenbank - just in diesem Moment - zur Ausführung besagten SQLs tut!

Obwohl Sie die grafische Oberfläche des Enterprise Managers (Database Control bzw. Grid Control) nicht nutzen, erfordert die Nutzung der hier vorgestellten Funktionalität separate Lizenzen. Sie müssen sowohl das "Enterprise Manager Diagnostic Pack" als auch "Enterprise Manager Tuning Pack" lizensieren.

Vorbereitungen

Zunächst: Die Funktionalität ist erst ab Oracle11g verfügbar.

Damit die Funktion nicht "versehentlich" genutzt wird, muss der Datenbankparameter CONTROL_MANAGEMENT_PACK_ACCESS auf den Wert DIAGNOSTIC+TUNING eingestellt sein. Daneben muss der Parameter STATISTICS_LEVEL auf TYPICAL oder ALL stehen, damit ausreichend Statistiken gesammelt werden.

Außerdem benötigt Ihr Workspace-User noch einige Privilegien, da die Überwachung der Ausführung von SQL-Anweisungen in den meisten Datenbankinstallationen vom DBA gemacht wird. Stellen Sie sicher, dass Sie SELECT-Privilegien auf diesen V$-Views besitzen:

  • GV_$SQL_PLAN_MONITOR
  • GV_$SQL_PLAN
  • GV_$SQL_MONITOR
  • GV_$ACTIVE_SESSION_HISTORY
  • GV_$SQL
  • GV_$SESSION_LONGOPS
  • GV_$ASH_INFO
  • GV_$INSTANCE
  • GV_$DATABASE
  • GV_$TIMER
  • GV_$SQL_OPTIMIZER_ENV
  • GV_$SYS_OPTIMIZER_ENV
  • V_$SQL_PLAN_MONITOR
  • V_$SQL_PLAN
  • V_$SQL_MONITOR
  • V_$ACTIVE_SESSION_HISTORY
  • V_$SQL
  • V_$SESSION_LONGOPS
  • V_$ASH_INFO
  • V_$INSTANCE
  • V_$DATABASE
  • V_$TIMER
  • V_$SQL_OPTIMIZER_ENV
  • V_$SYS_OPTIMIZER_ENV
  • DBA_PROCEDURES

Welches SQL kann überwacht werden?

Nicht jede SQL-Anweisung in der Datenbank steht für das Real Time Monitoring bereit - das wäre auch gar nicht sinnvoll. Die Datenbank stellt diese Möglichkeit nur für langlaufende SQL-Anweisungen oder für solche mit dem Optimizer-Hint /*+ MONITOR*/ bereit. Eine Anweisung ist langlaufend, wenn sie wenigstens fünf Sekunden CPU- oder I/O-Zeit verbraucht.

Erstellen Sie nun also einen Application Express-Bericht, der die zu überwachenden SQL-Anweisungen anzeigen soll. Verwenden Sie dazu folgende Abfrage: 

select
  sql_id,
  sm.status,
  sm.sql_exec_start,
  case
    when length(s.sql_text) < 75 then s.sql_text
    else substr(s.sql_text, 1, 75) || ' ...' 
  end as sql_text
from v$sql_monitor sm join v$sql s using (sql_id)
order by sql_exec_start desc

Dieser Bericht sollte dann wie in Abbildung 2 aussehen. Mit dem Hint /*+ MONITOR*/ können Sie das Monitoring für Ihre Anweisung erzwingen, auch wenn sie nicht langlaufend ist:

SQL Anweisungen mit Monitoring Informationen
Abb. 2: SQL Anweisungen mit Monitoring Informationen

Monitoring-Informationen anzeigen ...

Fügen Sie der Seite nun ein verstecktes Element (Hidden) namens PX_SQLID und einen zweiten Bericht hinzu. Verwenden Sie für den Bericht diese SQL-Abfrage: 

select dbms_sqltune.report_sql_monitor(
  type   => 'html',
  sql_id => :PX_SQLID
) as sql_monitor from dual

Deaktivieren Sie bei diesem Bericht alle Layout-Details wie Spaltenüberschriften, oder Einstellungen zum Blättern ( Pagination). Es ist auch Sinnvoll, eine Bedingung an den Bericht zu knüpfen - er soll nur dargestellt werden, wenn :PX_SQLID nicht NULL ist. Verknüpfen Sie die beiden Berichte nun mit einem Link in der Spalte SQL_ID des ersten Berichts (Abbildung 3).

Verknüpfung der beiden Berichte per Link
Abb. 3: Verknüpfung der beiden Berichte per Link

Starten Sie die Seite nun neu und klicken Sie auf die SQL ID einer der SQL-Anweisungen. Daraufhin sollte die grafische aufbereitete Monitoring-Information darunter erscheinen. Dies ist nun nicht nur für abgeschlossene, sondern auch für gerade laufende SQL-Anweisungen möglich. Testen Sie es, indem Sie ein langlaufendes SQL-Kommando in Ihrer Datenbank starten. Wenn Sie die Oracle-Beispielschemata installiert haben, ist folgende Abfrage ein guter Kandidat: 

select count(*) from sh.sales, sh.sales

Starten Sie diese Abfrage (als SH oder als SYS) von SQL*Plus aus und laden Sie einige Sekunden später Ihre Anwendungsseite neu - im oberen Bericht sollte dann ein Eintrag für das langlaufende SQL erscheinen - klicken Sie wiederum auf die SQL ID und Sie sollten in etwa folgendes Bild sehen (Abbildung 4):

Monitoring eines gerade laufenden SQL-Kommandos
Abb. 4: Monitoring eines gerade laufenden SQL-Kommandos

LIVE-Monitoring mit automatischer Aktualisierung

Sie können nun mit <F5> oder dem Reload-Button Ihres Browsers die Seite immer wieder auffrischen und so den aktuellen Stand der Ausfühung betrachten. Schöner ist es natürlich, wenn das automatisch geht - und auch das ist mit ein wenig JavaScript ganz einfach. Hinterlegen Sie dazu bei den Seitenattributen im Bereich Header und Footer folgenden JavaScript-Code im Seiten-Header: 

<script type="text/javascript">
var g_intervall;

function enableRefresh(p_zeitInSekunden) {
  g_intervall = window.setInterval("location.reload()", p_zeitInSekunden * 1000);
}

function disableRefresh() {
  window.clearInterval(g_intervall);
}
</script>

Navigieren Sie nun zum Footer der Region mit dem ersten Bericht (der Übersicht über die vorhandenen SQL-Anweisungen) und hinterlegen Sie dort den folgenden HTML-Code: 

Automatische Aktualisierung: 
<a href="javascript:enableRefresh(5);">Ein</a>
<a href="javascript:disableRefresh();">Aus</a>

Starten Sie nun die Seite wiederum neu und klicken Sie auf den neuen Link Automatische Aktualisierung: Ein. Die Seite sollte sich nun alle 5 Sekunden neu aufbauen. Wenn Sie dann die SQL ID Ihres langlaufenden Kommandos anklicken, können Sie "sich zurücklehnen" und der Datenbank quasi direkt bei der Ausführung zusehen.

Weitere Überlegungen

Beim Erstellen dieser Anwendungsseite haben Sie sicherlich bemerkt, dass das gesamte Layout direkt aus der Funktion DBMS_SQLTUNE.REPORT_SQL_MONITOR kommt. Neben der HTML-Ausgabe kann diese Funktion die Daten auch als einfachen ASCII-Text zurückgeben. Eine weitere Funktion DBMS_SQLTUNE.REPORT_SQL_MONITOR_XML liefert die Informationen im XML-Format zurück.

Ausgabe der Monitoring-Informationen als XML
Abb. 5: Ausgabe der Monitoring-Informationen als XML

Insbesondere die letzte Variante hat viel Charme: Wenn Sie sich mit XSLT-Stylesheets auskennen, können Sie damit auch ein komplett eigenes Layout für die Informationen erstellen. Eine andere Variante wäre, mit SQL-Funktionen wie EXTRACTVALUE oder EXTRACT die Informationen von Interesse aus dem XML-Dokument "herauszuschneiden" und in einem Application Express Standard-Bericht darzustellen.

Die Details, die beim Monitoring angezeigt werden, lassen sich durch entsprechende Parameter konfigurieren - zur Illustration dieser Möglichkeiten können Sie sich eine Beispielanwendung herunterladen; diese ist eine Erweiterung der bis hierhin erstellten und enthält Features wie die Darstellung als XML oder als TEXT, eine verbesserte automatische Aktualisierung und einiges mehr.

Beachten Sie jedoch, dass Sie, um die hier vorgestellte Funktionalität (und damit auch die Beispielanwendung) nutzen zu können, das Enterprise Manager Diagnostic Pack und das Tuning Pack lizensieren müssen.

Zurück zur Community-Seite