SQL Monitoring (dbms_sql_monitor) - Stand der Dinge in 12c

DBA Community - Januar 2014 (ergänzt Februar 2014)

von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

SQL Monitoring - auch bekannt unter dem Namen Real Time Monitoring - ist mittlerweile eines der wichtigsten Werkzeuge im Bereich Datenbank Monitoring und Tuning. Schnell und ohne Aufwand erhält man einen aktuellen und schnellen Überblick über bestimmte - meist langlaufende - Operationen. Im Unterschied zu AWR bzw. STATSPACK Reports zeigt der SQL Monitor dabei auch Statements an, die im Moment gerade aktiv sind oder sich in einer Warteschleife befinden. Auch Statements, die nicht unbedingt zu den "Langläufern" gehören, werden automatisch gelistet, falls sie bestimmte Eigenschaften (siehe unten) erfüllen.

Als die Funktion mit 11g zum ersten Mal vorgestellt wurde, haben wir dazu einen Artikel verfasst, der hier nachzulesen ist. Mit 11g Release 2 und nun auch mit 12c sind neue Features hinzugefügt worden. Wir nehmen dies zum Anlass einen Überblick über das Thema zu geben und den aktuellen Stand aufzuzeigen.

SQL Monitoring - ein Einstieg

SQL Monitoring steht, wie oben schon erwähnt, seit Oracle Database 11g zur Verfügung und ist in jedem Release erweitert worden. Bevorzugt wird die graphische Verwendung, allerdings besteht auch die Möglichkeit über; das Package DBMS_SQL_MONITOR zu arbeiten. In jedem Fall ist die Lizenzierung des Tuning Packs erforderlich. Die Kontrolle über den Einsatz des Packs kann dabei seit 11g mit dem Initialisierungparameter CONTROL_MANAGEMENT_PACK_ACCESS erfolgen. Dieser muss den Wert DIAGNOSTIC+TUNING enthalten. Zusätzlich muss der Parameter STATISTICS_LEVEL den Wert TYPICAL (das ist der Default) haben.

SQL> show parameter pack
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

Die Funktionsweise ist 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, im Monitor angezeigt. Es ist kein Einschalten beispielsweise durch Setzen eines speziellen Trace Events erforderlich. Die graphische Implementierung und somit die einfachste Überwachung ist durch Grid Control 11g bzw. Cloud Control 12c sicher gestellt.

Nun stellt sich die Frage, welche Eigenschaften müssen die Statements/Operationen haben müssen, um angezeigt zu werden? Für eine einzelne Ausführung muss mindestens eine der folgenden Bedingungen erfüllt sein:

parallele Durchführung
ein Ressourcen Verbrauch von mindestens 5 Sekunden I/O oder CPU Zeit
Nutzung des speziellen Hints MONITOR

Soll die Überwachung nicht nur durch den DBA erfolgen, sind SELECT Privilegien auf folgende Views erforderlich:

GV_$SQL_MONITOR bzw. GV$SQL
GV_$SQL_PLAN_MONITOR
GV_$ACTIVE_SESSION_HISTORY
GV_$SESSION_LONGOPS

Graphische Verwendung

Die einfachste und häufigste Art der Nutzung ist über Cloud Control 12c oder Grid Control 11g. 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), Parallelitätsgrad, Datenbankzeit, I/O Anforderungen, Anfangs- und Endezeit und über die SQL Texte. 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 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. Dies kann beispielsweise dabei hilffreich sein, schnell einen aktuellen Ressourcen Engpass (zum Beispiel im temporären Memory Umfeld) bei einer bestimmten Ausführung zu ermitteln.

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 Page 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 Code erfolgen kann.

Linemode Verwendung

Die Statistiken, die der graphischen Darstellung zugrunde liegen, sind auch über die Views V$SQL_MONITOR bzw.V$SQL_PLAN_MONITOR abzufragen. Die Views listen dabei die Statistiken pro SQL_ID auf. Im Unterschied zu anderen V$Views werden dabei die Statement Informationen einzeln pro Ausführung und nicht wie zum Beispiel in V$SQL kumuliert gelistet. Um die Darstellung übersichtlicher zu gestalten, steht zusätzlich als weiteres Hilfsmittel die Funktion REPORT_SQL_MONITOR des Package DBMS_SQL_MONITOR zur Verfügung. Sie ermöglicht eine Berichtsausgabe in unterschiedlichen Formaten wie zum Beispiel Text (Default) oder HTML. Folgender Screenshot zeigt einen Ausschnitt aus einem HTML Report.

Verwendet wurde dabei folgende Abfrage.

SQL> set heading off
SQl> set pagesize 0
SQL> set long 100000
SQL> select dbms_sql_monitor.report_sql_monitor(sql_id=>'fxzag1r4yvhd4',type=>'HTML') 
     FROM dual;

Die SQL_ID gibt dabei an, um welche Operation es sich handelt, der Parameter TYPE steht für das Format. Verwendet man übrigens keine Übergabeparameter, wird der aktuelle Bericht im Textformat ausgegeben.

Neuigkeiten in 12c

Zusammengesetzte Operationen

Die Erweiterung in 12c ermöglicht nun mehr als eine Operation in den Monitoring Prozess einzubeziehen. Die Idee dabei ist, in einer Session den Anfangs- und Endpunkt der zu überwachenden Aktivitäten zu markieren. Die SQL und PL/SQL Operationen, die zwischen den beiden Punkten stattfinden, gehören dann zu einer zusammengesetzten Database Operation. Typische Anwendungbeispiele sind das Monitoring von SQL*Plus Jobs, Batch Jobs oder ETL Prozessen.

Die zusammengesetzte Database Operation ist dann unter einem benutzerdefinierten Namen gelistet (im Beispiel Job1) und lässt sich sehr gut im Enterprise Manager Cloud Control monitoren.

... und die einzelnen Operationen

Wie funktioniert das Ganze? Um eine zusammengesetzte Database Operation zu definieren, wird der Start- und der Endpunkt der Operation über die neuen Programme BEGIN_OPERATION und END_OPERATION des Package DBMS_SQL_MONITOR markiert. Da das Resultat der Funktion BEGIN_OPERATION an die Prozedur END_OPERATION übergeben werden muss, kann entweder eine SQL*Plus Variable oder eine Package Variable verwendet werden. Folgendes Beispiel zeigt eine einfache Verwendung.

variable z number;
execute :z:=DBMS_SQL_MONITOR.BEGIN_OPERATION(DBOP_NAME => 'job3');
-- Operationen
execute DBMS_SQL_MONITOR.END_OPERATION(DBOP_NAME => 'job3', DBOP_EID => :z);

In V$SQL_MONITOR werden dann die einzelnen Ausführungen pro Operation angezeigt.

SQL> SELECT SUBSTR(DBOP_NAME, 1, 10), DBOP_EXEC_ID,
     SUBSTR(STATUS, 1, 10),  cpu_time
     FROM  V$SQL_MONITOR
     WHERE DBOP_NAME ='job3'
     ORDER BY DBOP_EXEC_ID;

SUBSTR(DBOP_NAME,1,10)                   DBOP_EXEC_ID
---------------------------------------- ------------
SUBSTR(STATUS,1,10)                        CPU_TIME
---------------------------------------- ----------
job3                                                1
DONE                                       42109598

job3                                                2
DONE                                       46258967

Runaway Queries, Database Resource Manager und SQL Monitoring

Vor 12c war es schon möglich im Database Ressource Manager eine Grenze (Threshold) für eine Query anzugeben - man spricht hier auch von "Runaway Queries". Nach Erreichnung dieser Grenze wurde eine Aktion durchgeführt. Dauert beispielsweise eine Query oder ein PL/SQL Aufruf mehr als 30 Sekunden (in CPU), dann kann man die Query beenden, einen Switch zu einer anderen Consumer Group vollziehen oder sogar die ganze Session beenden.

Wichtig zu wissen wäre nun, wer diese Queries ausgeführt hat, welcher Code (SQL oder PL/SQL) verwendet wurde und was für eine Aktion durchgeführt wurde. Dies kann nun über folgende neue Spalten in V$SQL_MONITOR eingesehen werden.

RM_CONSUMER_GROUP: die aktuelle Consumer Group für das überwachte SQL
RM_LAST_ACTION: entsprechende Aktion, nach Identifikation einer Runaway Query
RM_LAST_ACTION_REASON: Art der Grenze: Elapsed Time, CPU Time oder Anzahl IOs?
RM_LAST_ACTION_TIME: Zeitpunkt der Aktion

Folgendes Beispiel zeigt die neuen Informationen.

SQL> SELECT USERNAME, ELAPSED_TIME, PLSQL_EXEC_TIME, SQL_TEXT, CPU_TIME, 
     RM_LAST_ACTION, RM_LAST_ACTION_REASON, RM_LAST_ACTION_TIME, RM_CONSUMER_GROUP 
     FROM v$sql_monitor WHERE username is not null;

USERNAME                       ELAPSED_TIME PLSQL_EXEC_TIME
------------------------------ ------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
  CPU_TIME RM_LAST_ACTION
---------- ------------------------------------------------
RM_LAST_ACTION_REASON          RM_LAST_A RM_CONSUMER_GROUP
------------------------------ --------- ------------------------------
SH                                   378358               0
select /*+ use_nl(c) parallel ordered*/ count(*) from sh.sales s,sh.customers c
where c.cust_id=s.cust_id and cust_first_name='Dina'
     10998 SWITCH TO OTHER_GROUPS
SWITCH_CPU_TIME                19-FEB-14 OTHER_GROUPS

Hier wurde nach Erreichung einer Grenze der Switch von der GRUPPE_LOW_CPU zur OTHER_GROUPS initiiert.

Einige Kunden wollten allerdings nur die Runaway Queries überwachen und keine zusätzlichen Aktionen durchführen. Auch dies ist im Database Resource Manager ab 12c eine neue Option. Die neue "LOG_ONLY" Aktion ermöglicht ein Monitoring ohne eine weitere Aktion durchzuführen. So können verschiedene Grenzen für unterschiedliche Consumer Groups definiert und überwacht werden. Das erweiterte Beispiel sieht dann folgendermassen aus.

SQL> select USERNAME, ELAPSED_TIME, PLSQL_EXEC_TIME, SQL_TEXT, CPU_TIME, 
     RM_LAST_ACTION,  RM_LAST_ACTION_REASON, RM_LAST_ACTION_TIME, 
     SQL_EXEC_START, RM_CONSUMER_GROUP 
     FROM v$sql_monitor WHERE username is not null;

USERNAME                       ELAPSED_TIME PLSQL_EXEC_TIME
------------------------------ ------------ ---------------
SQL_TEXT
--------------------------------------------------------------------------------
  CPU_TIME RM_LAST_ACTION
---------- ------------------------------------------------
RM_LAST_ACTION_REASON          RM_LAST_ACTION_T SQL_EXEC_START
------------------------------ ---------------- ----------------
RM_CONSUMER_GROUP
------------------------------
SH                                   101635               0
select /*+ use_nl(c) parallel ordered*/ count(*) from sh.sales s,sh.customers c
where c.cust_id=s.cust_id and cust_first_name='Dina'
      7000 SWITCH TO OTHER_GROUPS
SWITCH_CPU_TIME                19.02.2014 16:51 19.02.2014 16:51
OTHER_GROUPS

SCOTT                             140668120       140658478
BEGIN last1.sortiere(32767); END;
 116168340
                                                19.02.2014 16:44
GRUPPE_HIGH_CPU

Der User SCOTT führte offensichtlich um 16:44 eine langlaufende Prozedur aus und wurde in V$SQL_MONITOR aufgelistet.

Die Direktiven können übrigens mit der Prozedur CREATE_PLAN_DIRECTIVE oder über UPDATE_PLAN_DIRECTIVE eingestellt werden.Im folgenden soll ein Beispiel die Einstellungen dokumentieren.

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
                   plan             => 'TEST_RUNAWAY',
                   group_or_subplan =>'GRUPPE_HIGH_CPU',
                   new_switch_group => 'LOG_ONLY', 
                   new_switch_time  => 200);
dbms_resource_manager.update_plan_directive(
                    plan             => 'TEST_RUNAWAY',
                    group_or_subplan => 'GRUPPE_LOW_CPU',                     
                    new_switch_group => 'OTHER_GROUPS', 
                    new_switch_time  => 30);
dbms_resource_manager.update_plan_directive(
                   plan             => 'TEST_RUNAWAY',
                   group_or_subplan => 'OTHER_GROUPS',                   
                   new_switch_group => 'CANCEL_SQL', 
                   new_switch_time  => 100);
dbms_resource_manager.submit_pending_area();
END;
/

Lizenzhinweis

Für die Verwendung dieser Funktionen ist die Lizenzierung des Diagnostics und Tuning Packs erforderlich.

Weitere Informationen

Zurück zur Community-Seite