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 0RN_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)