Das Monitoring der Datenbank ist für Datenbankadministratoren wie auch für Datenbankentwickler ein wichtiger Bestandteil ihrer Aufgaben - besonders auch in Zeiten von Ressource-Engpässen bzw. Performanceproblemen. Speziell beim Testen von neuen Anwendungen, neuen Techniken oder Features ist das Monitoring ein wichtiger Schritt im Testablauf.

Die Datenbank selbst stellt ein eigenes Framework zur Verfügung, um ohne Verwendung von externen Werkzeugen und ohne zusätzliche Installation ein genaues Monitoring zu erlauben. Das Framework umfasst seit jeher spezielle Data Dictionary Views, V$-Tabellen, PL/SQL-Packages für verschiedene Advisory-Aufgaben und ein spezielles Workload Repository. In jedem neuen Datenbankrelease wächst der Funktionsumfang dieses Frameworks - so auch in Oracle Database 11g mit Real-time Monitoring (auch SQL Monitoring ) und den Active Reports. Viele dieser Funktionen sind mittlerweile graphisch in der Enterprise Manager Database Control Konsole oder im aktuellen Grid Control implementiert, so dass die Nutzung sehr einfach und intuitiv ist.

In diesem Tipp konzentrieren wir uns auf die langlaufenden Transaktionen, das Real-time Monitorung und die Active Reports. Dabei werden folgende Techniken benutzt:

• V$SESSION_LONGOPS
• DBMS_APPLICATION_INFO
• V$SQL_MONITOR
• DBMS_SQLTUNE
• Enterprise Manager Database Control und Grid Control

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,' PROZENT TIME_REMAINING
------------------------------ -------------------- ------- --------------
Gather Schema Statistics
SYS                            16-JUN-2010 15:26:32      50              4

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,'    PROZENT TIME_REMAINING
------------------------------ -------------------- ---------- --------------
Transaction Rollback
SH                             26-AUG-2010 15:43:00 91.7590224              3

...
l_rindex:=dbms_application_info.set_session_longops_nohint;    
...
dbms_application_info.set_session_longops(
   rindex       => l_rindex, 
   slno         => l_slno, 
   op_name      => 'Lauf: '||l_seq, 
   target       => null,
   context      => null,
   sofar        => l_loopcnt,
   totalwork    => l_count,            
   target_desc  => 'Produkte', 
   units        => 'Anzahl'
   );
... 

SQL> SELECT sid, serial#, message, (sofar/totalwork)* 100 prozent
     FROM v$session_longops
     WHERE time_remaining>0;

       SID    SERIAL# MESSAGE                                            PROZENT
---------- ---------- -------------------------------------------------- -------
        26      35283 Lauf: 1: Produkte : 569247 out of 918843 Anzahl      61.95
                      done

• Wie kann man alle SQL Statements mit hohen Antwortzeiten finden?
• Welche Operationen dieser SQL Abfragen sind zeitintensiv?
• Welche Statements laufen parallel?
• Können die Statements auch in Echtzeit (real-time) überwacht werden?

• parallele Ausführung
• Verbrauch von mehr als 5 Sekunden CPU bzw. I/O Zeit
• Verwendung des MONITOR Hints

SQL> -- Überprüfung auf Verwendung des Tuning Packs
SQL> sho parameter pack

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

SQL> SELECT distinct s.sql_text, m.sql_id, m.cpu_time 
  1  FROM  v$sql_monitor m INNER JOIN v$sql s ON s.sql_id=m.sql_id
  2* WHERE username='SH' ORDER BY m.cpu_time

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID          CPU_TIME
------------- ----------
insert /*+ append */ into sales select * from sales
fcb815s02apwc   12388774


SQL> SELECT distinct s.sql_text, m.sql_id, m.cpu_time 
  1  FROM  v$sql_monitor m INNER JOIN v$sql s ON s.sql_id=m.sql_id
  2* WHERE username='SH' ORDER BY m.cpu_time

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID          CPU_TIME
------------- ----------
insert /*+ append */into sales select * from sales
fcb815s02apwc   16641040

spool monitor1.html
SELECT dbms_sqltune.report_sql_monitor(sql_id=>'fcb815s02apwc',type=>'HTML') FROM dual;
spool off

• OTN: Oracle Database 11g: SQL Details Active Report
• OTN: Oracle Database 11g: Real-Time SQL Monitoring
• Oracle Database Reference Handbuch
• Handbuch-Beschreibung von SET_SESSION_LONGOPS Procedure
• Community Tipp: Parallel Query ganz automatisch mit Oracle 11g.

Zurück zur Community-Seite