STATSPACK - Datenmenge einschränken

05.
August
2017
Veröffentlicht von: Richard Meinhardt

STATSPACK ist ein solides Tool zur Auswertung der Performance, das kostenlos mit jeder Oracle Datenbank mitgeliefert wird.

Leider kann es durchaus passieren, dass STATSPACK Teil eines Problems oder sogar selbst zu einem Problem wird. Dies geschieht meist in großen bzw. stark belasteten Datenbanken.

STATSPACK ist ein solides Tool zur Auswertung der Performance, das kostenlos mit jeder Oracle Datenbank mitgeliefert wird.

Leider kann es durchaus passieren, dass STATSPACK Teil eines Problems oder sogar selbst zu einem Problem wird. Dies geschieht meist in großen bzw. stark belasteten Datenbanken.

Problemstellung

Wie kann es nun zu Problemen mit STATSPACK kommen?

Generell gibt es zwei Probleme, die in entsprechenden Systemen mit STATSPACK schnell auftreten können:

  1. Die STATSPACK-Datenmenge belegt sehr viel Festplattenplatz.
  2. Einen STATSPACK-Snapshot zu erstellen ist so aufwendig für die Datenbank, dass dadurch die Performance negativ beeinflusst wird.

Die Lösung für beide Probleme ist dieselbe. Leider gibt es für die genaue Umsetzung dieser keine Faustregel und man muss sich je nach Wichtigkeit des Systems herantasten.

Anmerkung: 

Für das Problem, dass viel Festplattenplatz verbraucht wird, gibt es natürlich auch die einfache Lösung, weniger Snapshots vorzuhalten. Dies wird unter anderem im oben erwähnten Grundlagen-Monatstipp beschrieben.

Lösung

Um das Problem nun anzugehen, muss man STATSPACK dazu veranlassen, weniger Daten zu sammeln. Dies natürlich in einem Rahmen, indem keine wichtigen Daten verloren gehen.

Dafür gibt es 3 Bereiche, in denen man STATSPACK "tunen" kann.

  1. Snapshot Intervall
  2. Snapshot Level
  3. Snapshot Thresholds

Snapshot Intervall

Die einfachste Methode ist, das Intervall für die Snapshots, zu vergrößern. Das schränkt das Volumen der Daten und die Performance-Last ein. Dazu muss einfach nur der Datenbank-Job, der den Snapshot-Befehl ausführt, angepasst werden und mit dem neuen Intervall versehen werden.

Dabei ist natürlich zu bedenken, dass keine so präzise Auswertung mehr möglich ist, wenn das Intervall vergrößert wird. Dies gilt allerdings nur für historische Auswertungen. Bei wiederkehrenden Problem können Sie das Intervall wieder kurzzeitig verringern, um eine präzisere Auswertung möglich zu machen.

Snapshot Level

Das Snapshot Level bestimmt grundlegend, welche Informationen von STATSPACK gesammelt werden.

Es gibt 5 Level, welche immer aufeinander aufbauen. Also enthält das höchste Level auch alle Informationen der darunterligenden Level.

  • Level 0 - Sammelt generelle Performance-Statistiken
  • Level 5 - Sammelt Informationen zu SQL-Statements
  • Level 6 - Sammelt zu obigen SQL-Statements Ausführungspläne
  • Level 7 - Sammelt zusätzlich "Segment Level Statistics"
  • Level 10 - Sammelt Parent und Child Latches

Sowohl Level 0 als auch Level 10 sind sehr selten in Verwendung, da bei Level 0 die wichtigen SQL-Statement-Daten fehlen und Level 10 einfach sehr Performance-aufwendig ist. Auch von Oracle selber wird geraten, diese beiden Level nur auf Anweisung des Oracle Supports zu benutzen.

Level 5, 6 und 7 sind die üblichen Stufen, die Verwendung finden. Hier gilt es abzuwägen, welche Informationen gebraucht werden.

Es gibt 3 Wege das Level eines Snapshots festzulegen. Der Default wird in der Tabelle STATS$STATSPACK_PARAMETER gespeichert.

Beim Ausführen von STATSPACK.SNAP ohne Änderung des Defaults:

SQL> execute statspack.snap(i_snap_level=>0);

PL/SQL procedure successfully completed.

-- Letzter Snapshot
SQL> select snap_id, snap_time, snap_level from STATS$SNAPSHOT order by snap_time desc

   SNAP_ID SNAP_TIME            SNAP_LEVEL
---------- -------------------- ----------
      5564 10-JUL-2017 13:37:01          0
.....

-- Derzeitiger Default
SQL> select DBID, INSTANCE_NUMBER, SNAP_LEVEL from stats$statspack_parameter;

      DBID INSTANCE_NUMBER SNAP_LEVEL
---------- --------------- ----------
2203873876               1          7

Beim Ausführen von STATSPACK.SNAP mit Änderung des Defaults:

SQL> execute statspack.snap(i_snap_level=>5, i_modify_parameter=>'true');

PL/SQL procedure successfully completed.

-- Letzter Snapshot
SQL> select snap_id, snap_time, snap_level from STATS$SNAPSHOT order by snap_time desc;

   SNAP_ID SNAP_TIME            SNAP_LEVEL
---------- -------------------- ----------
      5565 10-JUL-2017 13:41:07          5
....

-- Derzeitiger Default
SQL> select DBID, INSTANCE_NUMBER, SNAP_LEVEL from stats$statspack_parameter;

      DBID INSTANCE_NUMBER SNAP_LEVEL
---------- --------------- ----------
2203873876               1          5

Änderung des Defaults ohne Ausführen von STATSPACK.SNAP:

SQL> execute statspack.modify_statspack_parameter(i_snap_level=>7);

PL/SQL procedure successfully completed.

-- Letzter Snapshot
SQL> select snap_id, snap_time, snap_level from STATS$SNAPSHOT order by snap_time desc;

   SNAP_ID SNAP_TIME            SNAP_LEVEL
---------- -------------------- ----------
      5565 10-JUL-2017 13:41:07          5
...

-- Derzeitiger Default
SQL> select DBID, INSTANCE_NUMBER, SNAP_LEVEL from stats$statspack_parameter;

      DBID INSTANCE_NUMBER SNAP_LEVEL
---------- --------------- ----------
2203873876               1          7

Snapshot Thresholds

Die dritte Möglichkeit ist das Anpassen der Threshold-Werte. Die derzeit eingestellten Default-Werte sind in der Tabelle STATS$STATSPACK_PARAMETER einzusehen. Auf Level 0 gibt es noch keine Thresholds. Ab Level 5 gibt es Thresholds, welche die Sammlung von SQL-Statements einschränken. Level 6 hat keine „eigenen“ Thresholds (Die Thresholds von Level 5, gelten natürlich auch hier.):

SQL> select dbid, instance_number, snap_level, EXECUTIONS_TH, DISK_READS_TH,
PARSE_CALLS_TH, BUFFER_GETS_TH, SHARABLE_MEM_TH, VERSION_COUNT_TH from
stats$statspack_parameter;

      DBID INSTANCE_NUMBER SNAP_LEVEL EXECUTIONS_TH DISK_READS_TH...
---------- --------------- ---------- ------------- -------------...
2203873876               1          6           100          1000...
...PARSE_CALLS_TH BUFFER_GETS_TH SHARABLE_MEM_TH VERSION_COUNT_TH
...-------------- -------------- --------------- ----------------
...          1000          10000         1048576               20

Ab Level 7 gibt es Threshholds, welche die Sammlung der "Segment Level Statisitcs" einschränken:

SQL> select dbid, instance_number, snap_level, SEG_PHY_READS_TH, SEG_LOG_READS_TH, SEG_BUFF_BUSY_TH, SEG_ROWLOCK_W_TH, SEG_ITL_WAITS_TH, SEG_CR_BKS_RC_TH, SEG_CU_BKS_RC_TH from stats$statspack_parameter;

      DBID INSTANCE_NUMBER SNAP_LEVEL SEG_PHY_READS_TH SEG_LOG_READS_TH...
---------- --------------- ---------- ---------------- ----------------...
2203873876               1          7             1000            10000...
... SEG_BUFF_BUSY_TH SEG_ROWLOCK_W_TH SEG_ITL_WAITS_TH SEG_CR_BKS_RC_TH...
... ---------------- ---------------- ---------------- ----------------...
...              100              100              100             1000...
...SEG_CU_BKS_RC_TH
...----------------
...            1000

Die Spalten der Tabelle haben entsprechende Namen und sollten selbsterklärend sein.

Die Thresholds können genauso gesetzt werden wie auch schon das Snap Level:

-- Variante 1
SQL> execute statspack.snap(i_snap_level=>5,i_executions_th=>5);

PL/SQL procedure successfully completed.

-- Variante 2
SQL> execute statspack.snap(i_snap_level=>5,i_executions_th=>5,i_modify_parameter=>'true');

PL/SQL procedure successfully completed.

-- Variante 3
SQL> execute statspack.modify_statspack_parameter(i_snap_level=>5,i_executions_th=>10);

PL/SQL procedure successfully completed.

-- Derzeitiger Default
SQL> select dbid, instance_number, snap_level, EXECUTIONS_TH, DISK_READS_TH,
PARSE_CALLS_TH, BUFFER_GETS_TH, SHARABLE_MEM_TH, VERSION_COUNT_TH from stats$statspack_parameter;

      DBID INSTANCE_NUMBER SNAP_LEVEL EXECUTIONS_TH DISK_READS_TH...
---------- --------------- ---------- ------------- -------------...
2203873876               1          5            10          1000...
...PARSE_CALLS_TH BUFFER_GETS_TH SHARABLE_MEM_TH VERSION_COUNT_TH
...-------------- -------------- --------------- ----------------
...          1000          10000         1048576               20

-- Letzte Snapshots
SQL> select snap_id, snap_time, snap_level from STATS$SNAPSHOT order by snap_time desc;

   SNAP_ID SNAP_TIME            SNAP_LEVEL
---------- -------------------- ----------
      5567 10-JUL-2017 14:25:29          5
      5566 10-JUL-2017 14:24:13          5
...

Welche Werte hier nun sinnvoll sind, muss für jedes System individuell analysiert werden, denn es gibt keine Faustregel um diese festzulegen.

Fazit

Abschließend ist zu sagen, dass die eingangs erwähnten Probleme mit STATSPACK sich durchaus beheben lassen. Aber man sollte Kompromisse eingehen, z. B. nur bestimmte Daten zu sammeln. Teilweise sind umfangreiche Analysen notwendig.

Sollten Sie Probleme mit der Performance haben, Opens window for sending emailhelfen wir gerne weiter.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.