Über unsMediaKontaktImpressum
Benedikt Nahlovsky 11. Februar 2015

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.

Autor

Benedikt Nahlovsky

Benedikt Nahlovsky ist als Administrator in der Datenbanktechnik tätig. Er konzentriert sich auf die Bereiche Systemsizing, Lizenzberatung, Single-Instance Datenbanken mit Oracle Restart, sowie die Hochverfügbarkeitslösungen RAC...
>> Weiterlesen
botMessage_toctoc_comments_9210