Oracle Statspack und SQL zur Performanceanalyse – Kostenlos aber effektiv
Viele Möglichkeiten, welche die Oracle Datenbank zur Analyse bereitstellt, sind nicht umsonst zu erhalten und sprengen oft für kleine und mittelständische Unternehmen das Budget. In diesem Artikel wird gezeigt, wie auch mit kostenlos zur Verfügung stehenden Tools Probleme analysiert und gelöst werden können.
Was ist Performance?
Performance ist in der Datenbanktechnik gleichzusetzen mit der Leistungsfähigkeit sowie dem entsprechendem Leistungserlebnis der User. Die Bewertung von Performance hängt vom System oder der einzelnen Komponente ab. Performance ist mess- und vergleichbar.
Oracle Statspack
Um die Effizienz bzw. Verfügbarkeit einer Datenbank zu überwachen oder um bereits vorhandene Ressourcenengpässe zu lokalisieren, stellt Oracle seit der Version 8i ein kostenloses Tool zur Sammlung und Auswertung von Datenbankinformationen für die Performanceoptimierung und Problemanalyse zur Verfügung. Mit Oracle Statspack können (Performance-)Informationen über viele Bereiche einer Oracle Datenbank gesammelt werden, um diese dann zu einem späteren Zeitpunkt auszuwerten.
Seit der Oracle Database Version 10g gibt es zusätzlich zu Statspack noch ein weiteres Tool für diese Aufgaben, das Automatic Workload Repository (AWR). Der große Nachteil von AWR ist, dass es im Gegensatz zu Statspack kostenpflichtig und ausschließlich auf einer Oracle Enterprise Edition lizensierbar ist.
Statspack Installation
Bei der Installation von Oracle Statspack wird das neue Schema PERFSTAT in der Datenbank angelegt. Diesem Benutzer ist ein Default und ein temporärer Tablespace zugewiesen. Hier wird empfohlen, einen separaten Tablespace zu verwenden, da nach dem Löschen von Statspack und des PERFSTAT Schemas dieser Tablespace auch problemlos gelöscht werden kann.
Nachdem ein eigener Tablespace angelegt wurde, kann mit der Statspack Installation als SYS begonnen werden:
SQL> @?/rdbms/admin/spcreate.sql
Im Verlauf der Installation muss ein Passwort für den PERFSTAT User definiert, sowie die beiden zu verwendenden Tablespaces angeben werden. Nach der erfolgreichen Installation ist man als User PERFSTAT mit der Datenbank verbunden und es kann sofort ein erster Snapshot durchgeführt werden.
SQL> EXECUTE statspack.snap;
Oder z.B. mit einem anderen Snapshot-Level (Default: 5)
SQL> EXECUTE statspack.snap(i_snap_level => 7);
Snapshot Level
Anhand von verschiedenen Snapshot Leveln im Statspack kann der Datenbankadmin festlegen, wie granular die Datenbankinformationen gesammelt werden. Je detaillierter die Snapshots erzeugt werden, umso länger kann ein einzelner Snapshot dauern.
Level 0: Ausführungsstatistiken, grundlegende Events, SGA-Daten
Level 5: Top SQL
Level 6: SQL Pläne und Plan-Nutzungsstatistiken
Level 7: Segmentstatistiken, Locks, Buffer Busy Waits
Level 10: Erweiterte Latching Statistiken (Child Latches)
Erstellen eines automatischen Snapshot Job
Das unten aufgeführte Skript erstellt einen Datenbank Scheduler Job, welcher im Intervall von 10 Minuten einen Snapshot mit Level 5 durchführt.
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"PERFSTAT"."PERFSTAT_SNAP"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
execute immediate (''Alter session set "_cursor_plan_unparse_enabled" = false'');
statspack.snap(i_snap_level => 5,
i_modify_parameter => ''false''
);
end;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=10;',
start_date => systimestamp at time zone 'Europe/Berlin',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Perfstat snapshot job',
auto_drop => FALSE,
enabled => TRUE);
END;
/Statspack Purge Job
Um sicherzustellen, dass der neu angelegte PERFSTAT Tablespace nicht sinnlos wächst, sollten die erstellten Snapshots nur für eine bestimmte Zeit aufbewahrt werden. In unserem Beispiel werden die Snapshots im PERFSTAT Tablespace für 7 Tage aufbewahrt. Auch hierfür wird empfohlen, einen Datenbank Scheduler Job zu erstellen:
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"PERFSTAT"."PERFSTAT_CLEANUP"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
execute immediate
''delete
from stats$snapshot
where snap_time < sysdate -7'';
commit;
end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp at time zone 'Europe/Berlin',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Perfstat cleanup 5 days history',
auto_drop => FALSE,
enabled => TRUE);
END;
/
Statspack Report erzeugen
Die gesammelten Statistiken können nun anhand des von Oracle zur Verfügung gestellten Skripts ausgewertet werden:
SQL> @?/rdbms/admin/spreport.sql
Nach dem Aufruf dieses Skripts werden alle zur Verfügung stehenden Snapshots aufgelistet. Anschließend wird zur Eingabe der Snap-ID’s aufgefordert, über die ein Report erzeugt werden soll. Der Default Dateiname
sp_<begin_snap_id>_<end_snap_id>.lst
kann optional geändert werden.
In den erstellten Reports sind Informationen zu den Wait-Events, Systemstatistiken (IO, PGA, Undo,etc.), Top-SQL Statements, der Instance Aktivität sowie viele weitere Komponenten der Oracle Datenbank enthalten.
Execution Plan from Cache generieren
Im Statspack Report lassen sich die Problem SQL Statements finden. Diese Statements sind mit einem eindeutigen OLD_HASH_VALUE identifizierbar. Anhand eines Skripts lässt sich der Ausführungsplan des Statements anzeigen.
ALTER SESSION SET "_cursor_plan_unparse_enabled"=TRUE;
define OHV=
undefine CHILD_NUMBER
select SQL_ID
from v$SQLAREA
where old_hash_value=&&OHV;
select inst_id, child_number, plan_hash_value
from gv$sql_plan
where SQL_ID=(select SQL_ID
from v$SQLAREA
where old_hash_value=&&OHV)
group by inst_id, child_number, plan_hash_value
order by inst_id, child_number
;
select *
from gv$sqlarea
where sql_id=(select SQL_ID
from v$SQLAREA
where old_hash_value=&&OHV);
select inst_id, plan_hash_value, executions, buffer_gets, round(buffer_gets/nvl(executions,0),0) as BG_average
from gv$sql
where sql_id=(select SQL_ID
from v$SQLAREA
where old_hash_value=&&OHV
)
order by inst_id,plan_hash_value;
select 'exec sys.dbms_shared_pool.purge ('''||address||','||hash_value||''', ''C'');'
from gv$sql
where sql_id=(select SQL_ID
from v$SQLAREA
where old_hash_value=&&OHV);
SELECT * FROM table
(DBMS_XPLAN.DISPLAY_CURSOR(
(select SQL_ID
from v$SQLAREA
where old_hash_value=&&OHV), &&CHILD_NUMBER,'COST,IOSTATS,LAST,ADVANCED'));
Blocking Session identifizieren
Locks (Sperren) sind ein wichtiger Bestandteil der Oracle Datenbank, welche verhindern, dass mehrere Benutzer zur gleichen Zeit die gleiche Zeile einer Tabelle ändern. Möglicherweise können Locks auch zu Problemen bei der Verarbeitung von Transaktionen führen. Ein klassischer Fall ist der Blocking Lock, wobei eine Transaktion auf das Beenden (commit/rollback) einer anderen wartet, um den gewünschten Datensatz manipulieren zu können. Mit Hilfe eines Selects auf die Views V$LOCK und V$SESSION lässt sich die blockierende Session sowie die wartende Session identifizieren:
select s1.username || '@' || s1.machine || '
( SID=' || s1.sid || ' )
is blocking ' ||
s2.username || '@' || s2.machine || '
( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid
and s2.sid=l2.sid
and l1.BLOCK=1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.sid, s2.sid
;
Fazit
Mit Hilfe von kostenlos zur Verfügung stehenden Tools wie beispielsweise STATSPACK werden Datenbankinformationen gespeichert und können später über einen Report ausgewertet werden. Sie dienen somit als Stütze zur Problemlösung für Datenbankadministratoren. Auch ohne Oracle Enterprise Edition mit Diagnostic und Tuning Pack lassen sich Probleme in der Datenbank lösen. Gerade für kleine und mittelständische Unternehmen ist dies oftmals der bevorzugte Weg um hohen Lizenzkosten aus dem Weg zu gehen.
Neuen Kommentar schreiben