Real-Time Monitoring und Database Developer in 19c

Datenbankentwickler sollten nicht nur SQL Statements schreiben, sondern auch die Möglichkeit haben, ihre eigenen SQL Abfragen zu monitoren und gegebenenfalls zu optimieren. Liegt es an gewissen Statements, der Parallelisierung, den Ausführungsplänen, oder vielleicht an Ressourcen, die zu knapp bemessen sind, falls Datenbankabfragen zu langsam laufen? 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. Dieses Datenbank Framework besteht dabei aus speziellen Datenbank Views, PL/SQL-Packages und ein spezielles Workload Repository, um nur einige Beispiele zu nennen. Eine einfache Methode besteht beispielsweise darin die View V$SESSION_LONGOPS abzufragen. 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 einen Blick auf diese V$View zu werfen. Allerdings kann man damit nicht sichergehen, dass wirklich alle zeitintensiven Operationen nach Ablauf von 6 Sekunden aufgeführt werden.

Das Real-Time Monitoring hingegen, das schon seit Oracle 11g in der Oracle Datenbank zur Verfügung steht, ist ein geeignetes Hilfsmittel um zuverlässig einen detaillierten Überblick über globale SQL- und PL/SQL-Statistiken von Operationen zu erhalten. Sowohl Cursor-Statistiken (z.B. CPU-Zeiten und IO-Zeiten) als auch Ausführungsplan-Statistiken (z.B. Anzahl der Zeilen, Speicher und belegter Temp Space) werden während der Ausführung der Anweisung nahezu in Echtzeit aktualisiert. Dabei werden nicht nur die gerade aktive Operationen monitort, sondern auch Abfragen, die sich in einer Warteschlange befinden oder gar abgebrochen worden sind. Grundlage um die relevanten Statistiken anzuzeigen sind die Views V$SQL_MONITOR und V$SQL_PLAN_MONITOR. Darüber hinaus stellt DBMS_SQLTUNE die Funktionen REPORT_SQL_MONITOR und REPORT_SQL_MONITOR_LIST bereit, um übersichtliche Reports zu erstellen.

Ein Hinderungsgrund für Database Developer diese Funktion zu verwenden, war bisher immer, dass Datenbankadministrationsrechte wie SELECT_ANY_CATALOG Rolle erforderlich waren. Mit 19c ist diese Einschränkung nun aufgehoben worden! Somit können Datenbank Developer mit weniger Privilegien das Real-Time Monitoring für ihre eigenen Statements verwenden.

In den folgenden Abschnitten wird diese Möglichkeit an einem einfachen Beispiel demonstriert. Für das Beispiel verwenden wir einen User mit den Rollen CONNECT und RESOURCE und SELECT Rechten auf die entsprechenden Tabellen.

SQL> create user dev1 identified by passwort;
User created.

SQL> grant connect, resource to dev1;
Grant succeeded.

SQL>  grant select on dev.tab1  to dev1;
Grant succeeded.

SQL>  grant select on dev.tab2 to dev1;

Zur Erinnerung: Das SQL-Monitoring wird immer 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 durch Setzen eines speziellen Trace Events ist zur Nutzung erforderlich.

Voraussetzung bei On-Premises Anwendungen ist 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. In Cloud Umgebungen sind Tuning und Diagnostics Pack schon in der Enterprise Edition inkludiert. Zusätzlich muss der Parameter STATISTICS_LEVEL den Wert TYPICAL (das ist der Defaultwert) haben.

Ab Oracle Database 19c gibt es eine Reihe von V$-Views, die es einem Datenbankbenutzer ohne SELECT_CATALOG_ROLE ermöglichen, die Pläne und Statistiken für einzelne SQL- und PL/SQL-Anweisungen zu monitoren. Folgende Liste gibt einen Überblick über diese Views.

  • V$ALL_SQL_MONITOR listet die Ausführungsstatitistiken (vergleichbar mit V$SQL_MONITOR)
  • V$ALL_SQL_PLAN_MONITOR listet die Informationen zu Ausführungsplänen (vergleichbar mit V$SQL_PLAN_MONITOR)
  • V$ALL_ACTIVE_SESSION_HISTORY vergleichbar mit V$ACTIVE_SESSION_HISTORY
  • V$ALL_SQL_PLAN vergleichbar mit v$SQL_PLAN

Der User DEV führt dann folgende langlaufende Abfrage aus.

select /*+ monitoring */ count(*)
     from dev.tab1 a, dev.tab2 b
     where a.c1=b.c1 order by 1;

Um einen ersten Überblick über die Abfragen zu bekommen, verwenden wir nun die Abfrage auf V$ALL_SQL_MONITOR. Die View besteht dabei aus über 80 Spalten. Wir wählen im folgenden nur ein paar charakteristische Merkmale aus.

SQL> set linesize window
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$all_sql_monitor where sql_text is not null;

       KEY        SID
---------- ----------
USERNAME
--------------------------------------------------------------------------------------------------------
SQL_ID                                   PLAN_HASH ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS
--------------------------------------- ---------- ------------ ---------- ----------- ----------
SQL_TEXT
--------------------------------------------------------------------------------------------------------
3.9428E+12      12956
DEV1
4f48nwrxfd0m4                           2530687813    611140232  610215515       24467        208
select /*+ monitoring */ count(*)
     from dev.ta

1.4169E+13       2186
DEV1
2p0qszv745kwx                           2646901065      1007081    1000467         504          3
SELECT 'FUNCTION' type, owner, object_name/*||'.'|

Die gleiche Abfrage ausgeführt mit dem User SYS zeigt übrigens die Ausführung von allen Usern mit langlaufenden Abfragen.

Um die Darstellung übersichtlicher und lesbarer zu gestalten, gibt es zusätzlich die Möglichkeit, mit den Funktionen REPORT_SQL_MONITOR_LIST und REPORT_SQL_MONITOR des Package DBMS_SQLTUNE zu arbeiten. Damit ist eine Darstellung in unterschiedlichen Formaten möglich wie z.B. TEXT (das Defaultformat), HTML oder auch ACTIVE für interaktive Reports, die auf der Flash Player-Funktionalität von Adobe basieren.

Folgendes Beispiel erzeugt eine Liste der langlaufenden Statements.

SQL> SET LONG 1000000
SQL> SET LONGCHUNKSIZE 1000000
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 0
SQL> SET TRIM ON
SQL> SET TRIMSPOOL ON
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF

SQL> spool monitor_list_sql_system.html rep

SQL> SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'html', report_level => 'ALL') AS report FROM dual;

SQL> spool off

Der zugehörige Report sieht dann folgendermaßen aus. Nur die beiden Ausführungen des Users DEV sind zu gelistet.

Eine genauere Detailansicht gibt es über folgenden Aufruf. Hier wird die letzte Operation im Detail angezeigt. Möchte man ein bestimmtes Statement anzeigen, kann man dies mit der SQL_ID im Aufruf mitangeben.

SQL> SET LONG 1000000
SQL> SET LONGCHUNKSIZE 1000000
SQL> SET LINESIZE 1000
SQL> SET PAGESIZE 0
SQL> SET TRIM ON
SQL> SET TRIMSPOOL ON
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> spool monitor_sql_dev1.html rep

SQL> select dbms_sqltune.report_sql_monitor(type=>'html') from dual;

SQL> spool off

Der zughörige HTML Report sieht dann wie folgt aus.

Weitere Informationen zum Thema findet man in den Handbüchern oder auch in der Oracle Support Note 2480461.1.

Weitere Informationen

 

Zurück zur Community-Seite
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services