NIMSOFT Tabellen RN_QOS_DATA_nnnn laufen voll, Archivierung versagte

Die Archivierung der NIMSOFT Daten auf dem zentralen Server stand. Der Server sammelt alle Daten aller Maschinen.

Allein eine Schleife mit einem count(*)  über die Datentabellen lief den ganzen Vormittag.   Zunächst die Ermittlung des Istzustandes.

Ich startete mit :

1
2
QUERYL := 'select COUNT(1) LINES FROM ' || USERNAM || '.' || NTAB_NAME
|| ' WHERE TABLE_ID >= 0 AND ' || TOKEN || ' < TRUNC(SYSDATE) - ' || DELTA;

Verglichen wird gegen trunc(SYSDATE), nicht gegen SYSDATE, damit der evaluierte Ausdruck auch bei Wiederholung konstant bleibt, was bei dem puren SYSDATE nicht der Fall ist (Uhrzeitanteil würde sich ändern).  Könnte Cache-Benefit liefern. Theoretisch könnte man es auch als Bind-Variable hinschreiben.

Aber man kann die Performance noch erheblich steigern. Ähnlich wie bei einem „WHERE EXISTS“, wenn man nur wissen will ob etwas da ist, hilft hier statt dem Count auf die volle Ergebnismenge der Count auf die „limitierte Mindestmenge“. Da wir in erster Näherung resourcenschonend in 200000-er Blöcken Löschen wollten, konnte ich die Laufzeiten etwas verbessern mit

1
2
3
4
-- nicht direkt zählen nur "ist mindestens der zu löschende Block da"
QUERYL := 'SELECT COUNT(1) FROM (SELECT 1 FROM ' || USERNAM || '.' || NTAB_NAME
|| ' WHERE TABLE_ID >= 0 AND ' || TOKEN || ' < TRUNC(SYSDATE) - ' || DELTA
|| ' AND ROWNUM <= ' || MAXROWNUM || ')';

So konnte man schon mal die Laufzeit der Ermittlung von „ganzem Vormittag“ auf einige Minuten drücken. 

Letzte Hürde:

Ungefähr drei Tabellen wehrten sich noch, mit Laufzeiten von bis zu sechs Minuten für eine de  Facto leere Tabelle ohne Daten. Woran kann das liegen?

BN_QOS_DATA_0041 NumRows: 0 TODELETE 0
29.11.2017 11:46:56
DN_QOS_DATA_0041 NumRows: 0 TODELETE 0
29.11.2017 11:46:56
HN_QOS_DATA_0041 NumRows: 0 TODELETE 0
29.11.2017 11:46:56
RN_QOS_DATA_0041 NumRows: 0 TODELETE 0
29.11.2017 11:53:01
BN_QOS_DATA_0042 NumRows: 0 TODELETE 0
29.11.2017 11:53:01

Struktur der Tabelle validiert. Tabelle reorganisiert (MOVE). Keine Auswirkung.

Index geprüft. Er ist nicht „unusable“.

Trotzdem war die Lösung ein „Alter Index REBUILD“.
Das neu Erzeugen des Index eliminierte das Performanceproblem.
Nachdem das Script noch um die Ausgabe der Deltatime pro Tabellenanalyse ergänzt wurden, fielen noch 2-3 weitere Kandidaten auf, deren Probleme genauso gelöst werden konnten. Eventuell hätte auch eine Suche nach  Indizes mit hohem Fragmentierungsgrad zum Ziel geführt, das könnte die Ursache gewesen sein. Habe daher auch schon bei Kunden für solche Fälle einen automatisierten Indexrebuild in der Wartung implementiert.

Ein Script löst dann die Probleme mit den aus dem Ruder gelaufenen Datenmengen ( > 900 Tage angefallen).

Folgende Zeile

RN_QOS_DATA_0001 NumRows: 36871127 TODELETE 200000

bedeutet, das in der Tabelle RN_QOS_DATA_0001 mindestens 200000 Zeilen zu löschen sind, die älter als 900 Tage sind.
In der nächsten Zeile haben wir ein Beispiel, wo die Tabelle nach der ersten Iteration schon clean ist, da nur 8815 Rows anfallen.

RN_QOS_DATA_0024 NumRows: 8815 TODELETE 8815

Kompletter Report:

Database = DB DELTA 900 -> DATUMSSCHWELLE 13-06-2015 READONLY1
BN_QOS_DATA_0001 NumRows: 0 TODELETE 0
29.11.2017 15:29:36 delta t sec 0
DN_QOS_DATA_0001 NumRows: 0 TODELETE 0
29.11.2017 15:29:36 delta t sec 0
HN_QOS_DATA_0001 NumRows: 0 TODELETE 0
29.11.2017 15:29:36 delta t sec 0
RN_QOS_DATA_0001 NumRows: 36871127 TODELETE 200000
<===============
29.11.2017 15:29:37 delta t sec ,72
BN_QOS_DATA_0002 NumRows: 0 TODELETE 0
29.11.2017 15:29:37 delta t sec 0
DN_QOS_DATA_0002 NumRows: 0 TODELETE 0
29.11.2017 15:29:37 delta t sec 0

RN_QOS_DATA_0462 NumRows: 11992679 TODELETE 0
29.11.2017 15:29:54 delta t sec 0
CLONE_RN_QOS_DATA_0041 NumRows: TODELETE 0
29.11.2017 15:29:54 delta t sec 0
585 TABELLEN betrachtet
================> LÖSCHUNG IST ERFORDERLICH
Ein Löschbefehl:
DELETE FROM NMUSER.CLONE_RN_QOS_DATA_0041 WHERE TABLE_ID >= 0 AND SAMPLETIME < TRUNC(SYSDATE) – 900 AND ROWNUM <= 200000

Das Script das die Löschungen resourcenschonend durchführt:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
DECLARE
/* Programmparameter = Konstanten zur Steuerung des Laufzeitverhaltens */
/* Eine Iteration dauert so knapp über 30 min , 4 -> ~2h */
MAXITER CONSTANT NUMBER := 4; zum Limitieren der Laufzeit nutzen
MAXROWNUM CONSTANT NUMBER := 200000; -- adaptieren ; jetzt ca 30 Minuten/iter
DELTA CONSTANT NUMBER := 900; -- DELTA Tage zurück!
USERNAM CONSTANT VARCHAR2(6) := 'NMUSER'; -- der Tableowner

CURSOR NIM_TABLES IS
SELECT table_name, num_rows
FROM dba_tables
WHERE OWNER = USERNAM
AND table_name LIKE '%N_QOS_DATA_%'
ORDER BY SUBSTR(table_name,11),SUBSTR(table_name,1,1);

QUERYL VARCHAR2(500);
D_DML VARCHAR2(500);
TOKEN VARCHAR(32);
LINES NUMBER;
TODELETE NUMBER; -- Löschung erforderlich
GLOBTODELETE NUMBER; -- GLOBALES TODELETE
DELDONE NUMBER; -- hat gelöscht?
NITER NUMBER; -- Anzahl Iterationen
TBD NUMBER; -- noch was zu tun?
RERUN_HINT NUMBER;
EXAMINE_ONLY NUMBER; -- READONLY J/N
POSSIBLE_LIMIT NUMBER;
NLOOP NUMBER;
BTABLE BOOLEAN;
V_DBNAME VARCHAR2(132);
V_DBID NUMBER;
runtime DATE;
runseconds NUMBER;
timesnap DATE;

N_ROWS DBA_TABLES.NUM_ROWS%TYPE;
NTAB_NAME DBA_TABLES.TABLE_NAME%TYPE;

DLIMIT DATE;

 

BEGIN
EXAMINE_ONLY := &&NODELETE;
--EXAMINE_ONLY := 1;
/* 0 ==> FALSE; 1 ==> TRUE */
DELDONE := 0; TBD := 0; RERUN_HINT := 0; POSSIBLE_LIMIT := 1;
DLIMIT := SYSDATE-DELTA;
GLOBTODELETE := 0;
SELECT NAME,DBID INTO V_DBNAME,V_DBID FROM V$DATABASE;

DBMS_OUTPUT.PUT_LINE('Database = '||V_DBNAME || ' DELTA ' || DELTA ||
' -> DATUMSSCHWELLE ' || TO_CHAR(DLIMIT, 'DD-MM-YYYY ') || ' READONLY' || EXAMINE_ONLY );

NLOOP := 0;
-- Hauptschleife über die NIMTABLES
OPEN NIM_TABLES;
LOOP
FETCH NIM_TABLES INTO
NTAB_NAME, N_ROWS;
EXIT WHEN NIM_TABLES%NOTFOUND;

NLOOP := NLOOP + 1;
LINES := 0;

BTABLE := SUBSTR(NTAB_NAME,1,1) = 'B';

IF NOT BTABLE THEN
TOKEN := 'SAMPLETIME';
ELSE
TOKEN := 'STOPTIME';
END IF;

timesnap := SYSDATE;

-- nicht zählen nur "ist mindestens der zu löschende Block da"
QUERYL := 'SELECT COUNT(1) FROM (SELECT 1 FROM ' || USERNAM || '.' || NTAB_NAME
|| ' WHERE TABLE_ID >= 0 AND ' || TOKEN || ' < TRUNC(SYSDATE) - ' || DELTA
|| ' AND ROWNUM <= ' || MAXROWNUM || ')'; -- DBMS_OUTPUT.PUT_LINE(QUERYL); EXECUTE IMMEDIATE QUERYL INTO LINES; DBMS_OUTPUT.PUT_LINE(NTAB_NAME || ' NumRows: ' || N_ROWS || ' TODELETE '||LINES); IF LINES > 0 THEN
TODELETE := 1;
GLOBTODELETE := 1;
DBMS_OUTPUT.PUT_LINE(' <=============== '); ELSE TODELETE := 0; END IF; IF LINES >= MAXROWNUM THEN
RERUN_HINT := 1;
END IF;

DBMS_OUTPUT.PUT( TO_CHAR(SYSDATE, 'DD.MM.YYYY hh24:mi:ss') );
runseconds := 24 * 2600 * ( SYSDATE - timesnap);
DBMS_OUTPUT.put( ' delta t sec '|| TO_CHAR( runseconds , '9999.9') );
IF runseconds > 1 THEN
DBMS_OUTPUT.PUT_LINE ( ' MUCH PERFORMED ***** ');
ELSE
DBMS_OUTPUT.PUT_LINE ( ' ');
END IF;
timesnap := SYSDATE;

IF TODELETE > 0 THEN
-- Löschbefehl aufbereiten
-- Loop über Löschblöcke Designentscheidung. Inner Loop Block oder Table?
D_DML := 'DELETE FROM ' || USERNAM || '.' || NTAB_NAME
|| ' WHERE TABLE_ID >= 0 AND ' || TOKEN || ' < TRUNC(SYSDATE) - ' || DELTA
|| ' AND ROWNUM <= ' || MAXROWNUM;

IF EXAMINE_ONLY <> 0 THEN
DBMS_OUTPUT.PUT_LINE(' READONLY ');
--NULL;
ELSE

NITER := 0;
-- INNER LOOP - ITERATE Nx MAXROWNUM
WHILE TODELETE > 0 AND NITER < MAXITER LOOP IF NITER = 0 THEN DBMS_OUTPUT.PUT(' EXEC: ' ); DBMS_OUTPUT.PUT_LINE(' SHOULD DELETE CMD: ' || D_DML); END IF; EXECUTE IMMEDIATE D_DML; COMMIT; DELDONE := 1; DBMS_OUTPUT.PUT_LINE('Iteration ' || NITER || ' Es wurden '|| LINES || ' Zeilen gelöscht '); EXECUTE IMMEDIATE QUERYL INTO LINES; IF LINES > 0 THEN
TODELETE := 1;
ELSE
TODELETE := 0;
END IF;

IF LINES >= MAXROWNUM THEN
RERUN_HINT := 1;
ELSE
RERUN_HINT := 0;
END IF;

NITER := NITER +1;

END LOOP;
NULL;
END IF;

END IF; -- TODELETE

-- LIMIT FÜR TESTLAUF
--EXIT WHEN NLOOP >= 5;

END LOOP; -- NUM_TABLES;
CLOSE NIM_TABLES;

DBMS_OUTPUT.PUT_LINE(NLOOP || ' TABELLEN betrachtet ');

IF GLOBTODELETE > 0 THEN

IF DELDONE > 0 THEN
DBMS_OUTPUT.PUT_LINE(' Es wurde gelöscht ');
IF RERUN_HINT > 0 THEN
DBMS_OUTPUT.PUT_LINE(' Bitte wiederhole Aufruf, limitierter Lauf wegen Resourcenlimit ');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(' ================> LÖSCHUNG IST ERFORDERLICH ');
DBMS_OUTPUT.PUT_LINE('Letzter exemplarischer Befehl: ');
DBMS_OUTPUT.PUT_LINE(D_DML);
END IF;

ELSE
DBMS_OUTPUT.PUT_LINE('Es war keine Löschung erforderlich ');
END IF;

EXCEPTION WHEN OTHERS THEN
IF SQLCODE = -12801 THEN
DBMS_OUTPUT.PUT_LINE (' TEMP Segment zu klein? Empfohlen ist mind. 600MB');
END IF;
DBMS_OUTPUT.PUT_LINE ('Abbruch -> Errcode ' || SQLCODE);
END;

Es folgt dann noch ein wesentlicher Schritt. Wie auch bei der SYSAUX-Bereinigung ist ein entscheidender Schritt der Reorg der indizes, die über die Jahre einen Fragmentierungsgrad von bis zu 85% erreicht haben.
Dies verbraucht viel Platz sowie Performance, daher ist nach dem Löschen der Rebuild der Indizes nötig. Glücklicherweise gibt es in der Enterprise Version einen Qualifier REBUILD ONLINE, damit ist im Rebuild die Tabelle nicht gelockt, kann also während des Betriebs erfolgen. Der Rebuild der ersten drei Indizes bracht schon 7GB Freespace.

produktives Script Online Rebuild :

(wird folgen) 

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.