Heute habe ich mal wieder was über Oracle gelernt, deshalb hab ich gleich den folgenden Spickzettel erstellt, vielleicht hilft es ja auch dem Einen oder Anderen von euch...
Zur Zeit arbeiten wir in einem Projekt mit Oracle 10gR2. Mittlerweile hat Oracle (ich glaube seit Version 9) den Enterprise Manager (EM), eine grafische Oberfläche, mit der man allerhand Statistiken und Metriken überwachen kann.
Wenn man den EM nicht zur Verfügung hat, ist es ganz praktisch, wenn man ein paar SQL-Statements kennt, mit deren Hilfe man sich in einer SQL-Konsole einen Überblick über die ausgeführten SQL's machen kann.
Hier findet ihr solche kleinen Statements, die mir als Spickzettel gegen meine Vergesslichkeit helfen sollen...
Oracle Views
Oracle bietet ein paar Views, die in den folgenden Statements verwendet werden:
- V$SQLAREA: Oracle hat einen Speicher, in dem es SQL-Statements für spätere Verwendungen aufbewahrt. Mit Hilfe dieses Views findet man diese Statements und bestimmte Statistiken dazu
- V$SQLTEXT: Dieser View kann mit den kompletten SQL-Statements dienen, die im V$SQLAREA-View nach 1000 Zeichen abgebrochen werden
- V$SESSION: Dieser View enthält Informationen über die aktuellen Datenbank-Sessions und die aktuelle während der Session ausgeführten Statements. Diese Statements bekommen einen Hash-Wert und eine Adresse, die sich ggf. in V$SQLAREA wiederfinden. So kann man feststellen, welche Statements einer aktiven Session im "Cache" der Datenbank sind.
- V$SESS_IO: Ein View, mit dessen Hilfe man feststellen kann, ob ein bestimmtes SQL-Statement aktiv arbeitet oder nicht. Man kann sich hier die Blöcke anzeigen lassen, an denen das Statement gerade "arbeitet"
- V$SQL_BIND_CAPTURE: Dieser View kann dazu benutzt werden, BIND-Variablen eines SQL-Statements anzuzeigen. In den bisher erwähnten Views findet man die SQL-Statements nur mit Bind-Variablen, aber nicht mit den tatsächlich enthaltenen Werten. Diese Zuordnung findet man hier.
- DBMS_XPLAN: Dies ist ein Oracle-Objekt, mit dessen Hilfe man einen Ausführungsplan für ein bestimmtes SQL-Statement bekommen kann
- V$SESSION_WAIT_CLASS: Mit Hilfe dieses Views kann man sehen, wieviel Sessions bei den jeweiligen Wait-Klassen warten müssen
Verwendung der Views
Wenn man die Views kennt, kann man einige interessante und hilfreiche Abfragen ausführen:
Wie erkennt man, welche Statements im SharedPool sind, wie oft diese ausgeführt und geparst werden ?
SELECT EXECUTIONS, PARSE_CALLS, CPU_TIME, ELAPSED_TIME, SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS <= PARSE_CALLS
ORDER BY PARSE_CALLS DESC, SQL_TEXT
Damit bekommt man eine Übersicht über alle Statements, die mindestens so oft geparst wurden, wie sie ausgeführt wurden.
Ausserdem sieht man die Ausführungs- und CPU-Dauer.
Wie findet man aktive Benutzer bzw. Sessions ?
SELECT SID, TO_CHAR(LOGON_TIME,'DD.MM.YYYY HH24:MI') LOGON_TIME, USERNAME, TYPE, STATUS, PROCESS, SQL_ADDRESS, SQL_HASH_VALUE
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND USERNAME != 'SYS'
Damit bekommt man eine Übersicht über aktive Sessions. Wenn eine Session aktiv ist, steht die Spalte 'STATUS' auf 'ACTIVE', andernfalls aus 'INACTIVE'. Die beiden letzten Spalten 'SQL_ADDRESS' und 'SQL_HASH_VALUE' kann man dazu verwenden, die SQL-Statements zu finden, die von diesem Benutzer / dieser Session ausgeführt werden. Die gleichen Werte findet man dann im View V$SQLAREA als Spalten 'ADDRESS' und 'HASH_VALUE'.
Mit Hilfe des folgenden Statements kann man die beiden Views verknüpfen:
SELECT SESSIONS.SID, SESSIONS.USERNAME, OPTIMIZER_MODE, HASH_VALUE, ADDRESS, CPU_TIME, PARSE_CALLS, ELAPSED_TIME, SQL_TEXT
FROM V$SQLAREA SQLAREA, V$SESSION SESSIONS
WHERE SESSIONS.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
AND SESSIONS.SQL_ADDRESS = SQLAREA.ADDRESS
AND SESSIONS.USERNAME IS NOT NULL
AND SESSIONS.USERNAME != 'SYS'
ORDER BY ELAPSED_TIME DESC
Wie findet man heraus, ob ein Statement noch aktiv ist oder still steht ?
SELECT IO.SID, IO.BLOCK_GETS, IO.CONSISTENT_GETS, IO.PHYSICAL_READS, IO.BLOCK_CHANGES, IO.CONSISTENT_CHANGES
FROM V$SESS_IO IO, V$SESSION SESSIONS
WHERE SESSIONS.SID = IO.SID
AND SESSIONS.USERNAME IS NOT NULL
AND SESSIONS.USERNAME != 'SYS'
Durch dieses Statement erhält man eine Übersicht über die Blöcke, die ein Statement bearbeitet. Wenn es aktiv ist, sollte sich die Anzahl der Blöcke verändern. Bleibt die Anzahl gleich, so ist das Statement nicht mehr aktiv oder "steckt fest".
Wie kann man sich aktive Statements mit der dazugehörigen Session / Benutzer-ID anzeigen lassen ?
SELECT SESSIONS.SID, SESSIONS.SERIAL#, SESSIONS.USERNAME, SESSIONS.SQL_ID, PARSE_CALLS, EXECUTIONS, SESSIONS.SQL_CHILD_NUMBER, OPTIMIZER_MODE, HASH_VALUE, ADDRESS, SQL_TEXT
FROM V$SQLAREA SQLAREA, V$SESSION SESSIONS
WHERE SESSIONS.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
AND SESSIONS.SQL_ADDRESS = SQLAREA.ADDRESS
AND SESSIONS.USERNAME IS NOT NULL
AND SESSIONS.USERNAME != 'SYS'
ORDER BY SQL_TEXT DESC
Dieses Statement liefert eine Übersicht über aktive SQL-Statements und den dazugehörigen Sessions.
Wie bekommt man eine Information darüber, welche Werte das SQL-Statement benutzt ?
Wenn man sog. BIND-Variablen in seinen SQL-Statements verwendet (was zu empfehlen ist), dann findet man in den Views anstelle der Werte nur die Bind-Variablen ( ... WHERE id=:1 ... )
Um die tatsächlichen Werte zu ermitteln, kann man das folgende Statement verwenden und ggf. erweitern:
SELECT SESSIONS.SID, SESSIONS.USERNAME, SESSIONS.SQL_ID, SESSIONS.SQL_CHILD_NUMBER, BIND.NAME, BIND.VALUE_STRING
FROM V$SQL_BIND_CAPTURE BIND, V$SESSION SESSIONS
WHERE SESSIONS.SQL_HASH_VALUE = BIND.HASH_VALUE
AND SESSIONS.USERNAME != 'SYS'
AND SESSIONS.SQL_ADDRESS = BIND.ADDRESS
AND SESSIONS.USERNAME IS NOT NULL
Wie bekomme ich einen Ausführungsplan eines Statements ?
Durch die bereits genannten Views kann man die SQL-ID eines Statements bekommen.
Das Oracle Objekt 'DBMS_XPLAN' ermöglicht es, auf den Ausführungsplan zuzugreifen. Dabei muss man dem Objekt die SQL-ID und die sog. CHILD_NUMBER übergeben:
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(('5032g02wrwu5d'),0))
In Oracle 10g gibt es verschiedene Wait-Klassen, zu denen die auftretenden Wait-Events gehören.
Diese Wait-Klassen sind:
- Administrative
- Application
- Cluster
- Commit
- Concurrency
- Configuration
- Idle
- Network
- Other
- Scheduler
- System I/O
- User I/O
Wie bekommt man Informationen darüber, welche Wait-Klasse am häufigsten auftritt ?
SELECT WAIT_CLASS, TOTAL_WAITS, ROUND(100 * (TOTAL_WAITS / SUM_WAITS),2) PCT_WAITS, TIME_WAITED, ROUND(100 * (TIME_WAITED / SUM_TIME),2) PCT_TIME
FROM (
SELECT WAIT_CLASS, TOTAL_WAITS, TIME_WAITED
FROM V$SYSTEM_WAIT_CLASS
WHERE WAIT_CLASS != 'Idle'
),(
SELECT SUM(TOTAL_WAITS) SUM_WAITS, SUM(TIME_WAITED) SUM_TIME
FROM V$SYSTEM_WAIT_CLASS
WHERE WAIT_CLASS != 'Idle'
)
ORDER BY PCT_TIME DESC
Das Ergebnis zeigt eine Liste der Wait-Klassen, sortiert nach der Häufigkeit der auftretenden Wait-Events.
Wie finde ich heraus, warum meine Sessions langsam sind und auf was die Sessions warten ?
SELECT WAIT_CLASS.SID, SESSIONS.USERNAME, WAIT_CLASS.WAIT_CLASS, WAIT_CLASS.TOTAL_WAITS, WAIT_CLASS.TIME_WAITED
FROM V$SESSION_WAIT_CLASS WAIT_CLASS, V$SESSION SESSIONS
WHERE SESSIONS.SID = WAIT_CLASS.SID
AND WAIT_CLASS.WAIT_CLASS != 'Idle'
AND SESSIONS.USERNAME IS NOT NULL
ORDER BY WAIT_CLASS.SID, WAIT_CLASS.TIME_WAITED DESC
Man bekommt eine Liste über die aktiven Sessions, verknüpft mit den jeweiligen Wait-Klassen.
So kann man erkennen, welche Wait-Klasse in einer Session am häufigsten auftritt.
Quellen: