Dieser Tipp baut auf folgendem Szenario auf:
Des Weiteren sollten Sie unseres Monatstipps „STATSPACK – DATENMENGE EINSCHRÄNKEN“ vor diesem Tipp lesen oder das Wissen bezüglich STATSPACK Thresholds Präsenz haben.
Als erstes sollte man sich damit beschäftigen, welches Objekt am meisten Speicherplatz belegt. So erhält man eine grobe Vorstellung davon, an welchem Threshold man ansetzen muss.
Um das zu ermitteln, setzen wir folgenden SELECT ab:
COL OWNER FOR a20
COL SEGMENT_NAME FOR a30
SELECT
OWNER,
SEGMENT_NAME,
ROUND(SUM(BYTES)/1024/1024/1024) as GB
FROM
DBA_SEGMENTS
WHERE
TABLESPACE_NAME='STATSPACK'
HAVING
ROUND(SUM(BYTES)/1024/1024/1024)<>0
GROUP BY
OWNER, SEGMENT_NAME
ORDER BY
3;
OWNER SEGMENT_NAME GB
-------------------- ------------------------------ ----------
PERFSTAT STATS$SQL_PLAN_USAGE 1
PERFSTAT STATS$SQL_PLAN_USAGE_PK 1
PERFSTAT STATS$SQL_SUMMARY 1
PERFSTAT STATS$SQLTEXT_PK 4
PERFSTAT STATS$SQLTEXT 6
Anhand des Outputs kann man nun erkennen, dass der meiste Platz von der Tabelle STATS$SQLTEXT (und Anhang) verbraucht wird. Wir würden also das Größenwachstum der STATSPACK-Daten deutlich reduzieren, wenn weniger SQL-Statements erfasst würden. Somit konzentrieren wir uns hier auf die Thresholds, die die Anzahl der gesammelten SQLs beeinflussen. Schließlich beachten wir im Report später meist ohnehin nur die Topstatements.
Das heißt, wenn wir Bezug auf die Konfigurationstabelle STATS$STATSPACK_PARAMETER nehmen:
SQL> desc STATS$STATSPACK_PARAMETER
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SESSION_ID NOT NULL NUMBER
SNAP_LEVEL NOT NULL NUMBER
NUM_SQL NOT NULL NUMBER
EXECUTIONS_TH NOT NULL NUMBER
PARSE_CALLS_TH NOT NULL NUMBER
DISK_READS_TH NOT NULL NUMBER
BUFFER_GETS_TH NOT NULL NUMBER
SHARABLE_MEM_TH NOT NULL NUMBER
VERSION_COUNT_TH NOT NULL NUMBER
PIN_STATSPACK NOT NULL VARCHAR2(10 CHAR)
ALL_INIT NOT NULL VARCHAR2(5 CHAR)
LAST_MODIFIED DATE
UCOMMENT VARCHAR2(160 CHAR)
JOB NUMBER
SEG_PHY_READS_TH NOT NULL NUMBER
SEG_LOG_READS_TH NOT NULL NUMBER
SEG_BUFF_BUSY_TH NOT NULL NUMBER
SEG_ROWLOCK_W_TH NOT NULL NUMBER
SEG_ITL_WAITS_TH NOT NULL NUMBER
SEG_CR_BKS_RC_TH NOT NULL NUMBER
SEG_CU_BKS_RC_TH NOT NULL NUMBER
OLD_SQL_CAPTURE_MTH NOT NULL VARCHAR2(10 CHAR)
sind folgende Thresholds somit interessant:
EXECUTIONS_TH, PARSE_CALLS_TH, DISK_READS_TH, BUFFER_GETS_TH, SHARABLE_MEM_TH, VERSION_COUNT_TH
Als nächsten gilt es zu klären, welche Threshold überschritten wurden und in welchen Kombinationen, da es sein kann, dass ein SQL beispielhaft sowohl den Threshold für EXECUTION wie auch BUFFER_GETS überschreitet. Die entsprechenden Werte für EXECUTIONS, PARSE_CALLS, DISK_READS, BUFFER_GETS, SHARABLE_MEM und VERISON_COUNT eines SQL Statements sind in der Tabelle STATS$SQL_SUMMARY zu finden:
SQL> desc STATS$SQL_SUMMARY
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
TEXT_SUBSET NOT NULL VARCHAR2(31 CHAR)
OLD_HASH_VALUE NOT NULL NUMBER
SQL_TEXT VARCHAR2(1000 CHAR)
SQL_ID VARCHAR2(13 CHAR)
SHARABLE_MEM NUMBER
SORTS NUMBER
MODULE VARCHAR2(64 CHAR)
LOADED_VERSIONS NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
LOADS NUMBER
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
ADDRESS RAW(8)
HASH_VALUE NUMBER
VERSION_COUNT NUMBER
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
AVG_HARD_PARSE_TIME NUMBER
OUTLINE_SID NUMBER
OUTLINE_CATEGORY VARCHAR2(64 CHAR)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64 CHAR)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
Im folgenden SELECT wird anhand der Schwellwertüberschreitungen ein String erstellt, der mit einer bestimmten Kombination aus Y und N die Überschreitungen darstellt. Danach werden anhand der Gruppenfunktion COUNT() die einzelnen Kombinationsausprägungen gruppiert:
SET PAGESIZE 1000
SELECT
E||P||D||B||S||V AS EPDBSV,
COUNT(*) AS SQLCOUNT
FROM
(
SELECT
(
CASE WHEN SQ.EXECUTIONS > TH.EXECUTIONS_TH THEN 'Y'
ELSE 'N'
END
) AS E ,
(
CASE WHEN SQ.PARSE_CALLS > TH.PARSE_CALLS_TH THEN 'Y'
ELSE 'N'
END
) AS P ,
(
CASE WHEN SQ.DISK_READS > TH.DISK_READS_TH THEN 'Y'
ELSE 'N'
END
) AS D ,
(
CASE WHEN SQ.BUFFER_GETS > TH.BUFFER_GETS_TH THEN 'Y'
ELSE 'N'
END
) AS B ,
(
CASE WHEN SQ.SHARABLE_MEM > TH.SHARABLE_MEM_TH THEN 'Y'
ELSE 'N'
END
) AS S ,
(
CASE WHEN SQ.VERSION_COUNT > TH.VERSION_COUNT_TH THEN 'Y'
ELSE 'N'
END
) AS V
FROM
STATS$SQL_SUMMARY SQ,
STATS$STATSPACK_PARAMETER TH
WHERE
TH.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER
)
GROUP BY
E||P||D||B||S||V
ORDER BY
2;
EPDBSV SQLCOUNT
------ ----------
YYNYNY 1
YNYYYY 2
YNNYNY 4
NYNNNY 11
YYYNNN 11
NNNNYY 12
NNNNNY 14
NNYNYN 29
NYNNYN 36
YNYNNN 37
NNNNNN 53
NYNYYN 125
YNNNYY 434
YYNNYY 774
YNYYYN 776
YYYYYY 854
NYNYNN 974
YYNNYN 1804
NNYYYN 2601
YYNYYY 3449
YNNYYN 4900
YYYYYN 4902
NYNNNN 8633
YNNNYN 9386
YYYYNN 12877
YNYYNN 16190
YYNYYN 23592
NNNYYN 44065
YYNNNN 60730
YYNYNN 108357
NNYNNN 118737
NNYYNN 131027
NNNNYN 181159
YNNYNN 397233
YNNNNN 443695
NNNYNN 2642657
In diesem Szenario gibt es mit der Kombination NNNYNN einen relativ eindeutigen "Gewinner" und nachdem dies auch eine Kombination mit nur einem Y ist, ist nur dieser eine Threshold zu beachten. Das Y belegt die vierte Stelle, damit handelt es sich hier um den Threshold BUFFER_GETS.
Bis hier hin ist das Vorgehen noch relativ klar, ab dem nächsten Schritt ändert sich das aber leider. Um das etwas bildlicher auszudrücken: Schritt 1 und 2 waren ein gut ausgeleuchteter gepflasterter Weg und nun gehen wir in einen Wald ohne Weg bei starkem Nebel. Entweder wir bewegen uns sehr langsam und vorsichtig oder wir fallen sehr wahrscheinlich früher oder später auf die Nase.
Um das bildliche Beispiel wieder ein wenig auf die technische Ebene zu holen: Es gibt an dieser Stelle leider so viele Faktoren, welche die Entscheidung für einen Threshold betreffen, dass es nahezu unmöglich ist, alle im Auge zu behalten. Deswegen ist es am besten, wenn man die Anpassungen eher im kleinen Rahmen macht und die Änderung immer erst eine Zeit beobachtet, da es das Ziel ist, für zukünftige Performance-Analysen keine wichtigen Daten zu verlieren.
Das Ziel in unserem Beispiel ist, die BUFFER_GETS Werte zu analysieren und entsprechende Schlüsse daraus abzuleiten.
Für den ersten Blick lassen wir uns COUNT(), MIN() und MAX() anzeigen:
SET PAGESIZE 1000
SELECT
COUNT(BUFFER_GETS) AS COUNT,
MIN(BUFFER_GETS) AS MIN,
MAX(BUFFER_GETS) AS MAX
FROM
(
SELECT
SQL_ID,
BUFFER_GETS,
(
CASE WHEN SQ.EXECUTIONS > TH.EXECUTIONS_TH THEN 'Y'
ELSE 'N'
END
) AS E ,
(
CASE WHEN SQ.PARSE_CALLS > TH.PARSE_CALLS_TH THEN 'Y'
ELSE 'N'
END
) AS P ,
(
CASE WHEN SQ.DISK_READS > TH.DISK_READS_TH THEN 'Y'
ELSE 'N'
END
) AS D ,
(
CASE WHEN SQ.BUFFER_GETS > TH.BUFFER_GETS_TH THEN 'Y'
ELSE 'N'
END
) AS B ,
(
CASE WHEN SQ.SHARABLE_MEM > TH.SHARABLE_MEM_TH THEN 'Y'
ELSE 'N'
END
) AS S ,
(
CASE WHEN SQ.VERSION_COUNT > TH.VERSION_COUNT_TH THEN 'Y'
ELSE 'N'
END
) AS V
FROM
STATS$SQL_SUMMARY SQ,
STATS$STATSPACK_PARAMETER TH
WHERE
TH.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER
)
WHERE
E||P||D||B||S||V = 'NNNYNN';
COUNT MIN MAX
---------- ---------- ----------
2642657 10001 3925051005
Der COUNT() entspricht natürlich der schon im zweiten Schritt ermittelten Zahl. Das Minimum ist auch keine große Überraschung und liegt genau eins über dem default Threshold.
Sehr interessant ist das sehr hohe Maximum, welches bei ungefähr 4 Milliarden liegt.
Eine so große Spanne zwischen Minimum und Maximum lässt spontan zwei Schlüsse zu:
Ersteres war zu erwarten, sonst würde die "Pointe" des Tipps fehlen. Der zweite Punkt sollte hingegen genauer analysiert werden.
Dafür setzen wir folgenden SELECT ab:
SET PAGESIZE 1000
SELECT
PERCENT,
COUNT(PERCENT) AS ANZAHL
FROM
(
SELECT
TRUNC(ROUND(BUFFER_GETS/(MAX(BUFFER_GETS) OVER ()/100)),-1) AS PERCENT,
(
CASE WHEN SQ.EXECUTIONS > TH.EXECUTIONS_TH THEN 'Y'
ELSE 'N'
END
) AS E ,
(
CASE WHEN SQ.PARSE_CALLS > TH.PARSE_CALLS_TH THEN 'Y'
ELSE 'N'
END
) AS P ,
(
CASE WHEN SQ.DISK_READS > TH.DISK_READS_TH THEN 'Y'
ELSE 'N'
END
) AS D ,
(
CASE WHEN SQ.BUFFER_GETS > TH.BUFFER_GETS_TH THEN 'Y'
ELSE 'N'
END
) AS B ,
(
CASE WHEN SQ.SHARABLE_MEM > TH.SHARABLE_MEM_TH THEN 'Y'
ELSE 'N'
END
) AS S ,
(
CASE WHEN SQ.VERSION_COUNT > TH.VERSION_COUNT_TH THEN 'Y'
ELSE 'N'
END
) AS V
FROM
STATS$SQL_SUMMARY SQ,
STATS$STATSPACK_PARAMETER TH
WHERE
TH.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER
)
WHERE
E||P||D||B||S||V = 'NNNYNN'
GROUP BY
PERCENT
ORDER BY
PERCENT;
PERCENT ANZAHL
---------- ----------
0 2642642
10 1
20 4
30 3
50 1
60 2
80 1
90 3
Nun sehen wir hier, dass die Werte nicht ansatzweise gleich verteilt sind. Die meisten Werte liegen im Bereich 0% - 10% des Maximums. Dies führt in seiner Konsequenz dazu, dass ein Average hier eine ungünstige Wahl wäre und eher auf einen Median zurückgegriffen werden sollte.
Um das zu veranschaulichen, wurde folgender SELECT ausgeführt:
set linesize 500
SELECT DISTINCT
MIN(BUFFER_GETS) AS MIN,
MAX(BUFFER_GETS) AS MAX,
ROUND(AVG(BUFFER_GETS)) AS AVG,
MEDIAN(BUFFER_GETS) AS MEDIAN,
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY BUFFER_GETS ASC) AS MEDIAN40,
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY BUFFER_GETS ASC) AS MEDIAN30,
PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY BUFFER_GETS ASC) AS MEDIAN20,
PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY BUFFER_GETS ASC) AS MEDIAN10
FROM
(SELECT
SQ.BUFFER_GETS,
(
CASE WHEN SQ.EXECUTIONS > TH.EXECUTIONS_TH THEN 'Y'
ELSE 'N'
END
) AS E ,
(
CASE WHEN SQ.PARSE_CALLS > TH.PARSE_CALLS_TH THEN 'Y'
ELSE 'N'
END
) AS P ,
(
CASE WHEN SQ.DISK_READS > TH.DISK_READS_TH THEN 'Y'
ELSE 'N'
END
) AS D ,
(
CASE WHEN SQ.BUFFER_GETS > TH.BUFFER_GETS_TH THEN 'Y'
ELSE 'N'
END
) AS B ,
(
CASE WHEN SQ.SHARABLE_MEM > TH.SHARABLE_MEM_TH THEN 'Y'
ELSE 'N'
END
) AS S ,
(
CASE WHEN SQ.VERSION_COUNT > TH.VERSION_COUNT_TH THEN 'Y'
ELSE 'N'
END
) AS V
FROM
STATS$SQL_SUMMARY SQ,
STATS$STATSPACK_PARAMETER TH
WHERE
TH.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER
)
WHERE
E||P||D||B||S||V = 'NNNYNN';
MIN MAX AVG MEDIAN MEDIAN40 MEDIAN30 MEDIAN20 MEDIAN10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10001 3925051005 479985 78471 49196 32823 21434 14485
Im Ergebnis des SELECT kann man sehr deutlich den Unterschied zwischen dem Average und dem Median erkennen.
Der Median definiert in einer sortierten Werteliste einen Wert, der anzeigt, dass 50% der Listenwerte über dem Median und 50% unter dem Median liegen. Mit der analytischen Funktion PERCENTILE_CONT() wurden in diesem Fall auch noch für die Kombinationen 40%/60%, 30%/70%, 20%/80% und 10%/90% die Werte ausgeben. Das dient dazu, die Werte besser einordnen zu können.
Nun beginnt das Herantasten. Dazu ermitteln wir uns die Werte, wie viele der Einträge denn herausfallen würden.
Bezüglich der Medianwerte brauchen wir nur die Gesamtzahl
set linesize 500
SELECT
count(buffer_gets)
FROM
(SELECT
SQ.BUFFER_GETS,
(
CASE WHEN SQ.EXECUTIONS > TH.EXECUTIONS_TH THEN 'Y'
ELSE 'N'
END
) AS E ,
(
CASE WHEN SQ.PARSE_CALLS > TH.PARSE_CALLS_TH THEN 'Y'
ELSE 'N'
END
) AS P ,
(
CASE WHEN SQ.DISK_READS > TH.DISK_READS_TH THEN 'Y'
ELSE 'N'
END
) AS D ,
(
CASE WHEN SQ.BUFFER_GETS > TH.BUFFER_GETS_TH THEN 'Y'
ELSE 'N'
END
) AS B ,
(
CASE WHEN SQ.SHARABLE_MEM > TH.SHARABLE_MEM_TH THEN 'Y'
ELSE 'N'
END
) AS S ,
(
CASE WHEN SQ.VERSION_COUNT > TH.VERSION_COUNT_TH THEN 'Y'
ELSE 'N'
END
) AS V
FROM
STATS$SQL_SUMMARY SQ,
STATS$STATSPACK_PARAMETER TH
WHERE
TH.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER
)
WHERE
E||P||D||B||S||V = 'NNNYNN';
COUNT(BUFFER_GETS)
------------------
2642657
und müssen nur entsprechend multiplizieren:
Das wären die Werte, die anzeigen, wie viele Statements nicht mehr aufgezeichnet werden.
Und nochmal zur Kontrolle mit dem Average Wert:
set linesize 500
SELECT
count(buffer_gets)
FROM
(SELECT
SQ.BUFFER_GETS,
(
CASE WHEN SQ.EXECUTIONS > TH.EXECUTIONS_TH THEN 'Y'
ELSE 'N'
END
) AS E ,
(
CASE WHEN SQ.PARSE_CALLS > TH.PARSE_CALLS_TH THEN 'Y'
ELSE 'N'
END
) AS P ,
(
CASE WHEN SQ.DISK_READS > TH.DISK_READS_TH THEN 'Y'
ELSE 'N'
END
) AS D ,
(
CASE WHEN SQ.BUFFER_GETS > TH.BUFFER_GETS_TH THEN 'Y'
ELSE 'N'
END
) AS B ,
(
CASE WHEN SQ.SHARABLE_MEM > TH.SHARABLE_MEM_TH THEN 'Y'
ELSE 'N'
END
) AS S ,
(
CASE WHEN SQ.VERSION_COUNT > TH.VERSION_COUNT_TH THEN 'Y'
ELSE 'N'
END
) AS V
FROM
STATS$SQL_SUMMARY SQ,
STATS$STATSPACK_PARAMETER TH
WHERE
TH.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER
)
WHERE
E||P||D||B||S||V = 'NNNYNN' and buffer_gets<472761;
COUNT(BUFFER_GETS)
------------------
2050410
Hier kann man nun deutlich erkennen, dass über den Average-Wert sehr viele Statements herausgefallen wären. Spätestens an diesem Punkt muss man anfangen, sich auf sein Gefühl zu verlassen und versuchen, die ermittelten Werte mit vielen anderen Faktoren in Bezug zu setzen (würde den Rahmen des Monatstipps sprengen). Im Fall dieses Szenarios wurde sich für den Wert 30.000 entschieden und das ganze über einen temporären Zeitraum beobachtet. Dies führte zu einem messbaren Ergebnis, welches aber nicht das Ziel erreicht und es wurde durch die gleiche Methode erneut nachjustiert.
Zwei sehr grobe Beispiele für die anderen Faktoren wären Datenstruktur und Applikationsdesign, aber allein diese zwei Punkte haben schon so viel Inhalte, dass man Bücher darüber verfassen könnte. Im Optimalfall haben Sie für Ihre Umgebung Ansprechpartner, die sich in diesen Bereichen auskennen. Wichtig an dieser Stelle ist die Dokumentation der Änderungen, um die Übersicht nicht zu verlieren und später sinnvoll nachjustieren oder auch wieder zurückjustieren zu können.
Wie schon am Anfang erwähnt, gibt es hier kein konkretes Vorgehen und es ist ratsam, Vorsicht vor Nachsicht walten zu lassen. Möchten Sie sich weiter mit diesem oder ähnlichen Themen beschäftigen und brauchen Unterstützung oder Diskussionspartner, zögern Sie nicht und kontaktieren Sie uns.
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.
