INDEXREORG Archive - Datenbankexperten

Auf dem Monitoringserver trat ein Problem auf, durch die hohe Changerate der Daten (ständig kommen neue hinzu, und im Idealfall werden alte gelöscht) ist nach einer gewissen Zeit die Indexfragmentierungsrate recht hoch ist. Die ist zwar nur ein theoretischer Wert, jedoch führt das in der Praxis tatsächlich zu höheren Laufzeiten bei einfachen Abfragen.

Im Extremfall dauerte die Abfrage (count(*)) gegen eine leere Tabelle Minuten. Da kann zwar auch ein korrupter Block im Spiel gewesen sein, jedoch ist das dann nur ein weiterer Grund für Heilung durch einen Index Rebuild, welcher dann letztlich das Problem löst.

Aus dieser Notwendigkeit sind mehrere Jobs entstanden.

Zunächst ein Job, der den akuten Befund „Abfrage auf Tabelle dauert viel zu lange“ ermittelt und einen Index Rebuild durchführt.

Dann entstand ein Job, der als Kriterium für den Reorg nicht eine vielleicht durch Seiteneffekte beeinflusste Laufzeit, sondern direkt die in die INDEX_STATS ermittelte Fragmentierung als Kriterium heranzieht.

Die übliche empfohlene Schwelle für einen Rebuild ist ein Fragmentierungsgrad > 20%. Wir fanden teilweise 85% Fragmentierung vor.

( siehe MyOracleSupport How Btree Indexes Are Maintained (Doc ID 30405.1) )

File 2 -> INDEXWARTUNG_MITANALYZE.SQL

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

SET SERVEROUTPUT ON SIZE 1000000

/*
Analysiert für einzugebendes SCHEMA die Fragmentierunge der Indizes und wirft einen INDEX REBUILD ONLINE an. Dies hat sich als relativ sinnfrei erwiesen, da der ANALYZE schon die Tabelle lockt - und länger braucht als der Rebuild. So machen wir lieber einen Wartungsjob ohne Analyze mit nur Rebuild online! (s.u)

Hier eine kleine Feinheit:
Der Index wird in zwei Schritten analysiert, erst ONLINE, soll den Cache füllen,
dann ohne ONLINE, erst dann wird INDEX_STATS gefüllt, was dann schneller erfolgt
Leider sind die Objektgrößen hier im Monitor Schema jenseits gut & böse...,
führt leider auch zu Locks, trotz gefülltem Cache
*/

DECLARE

USERNAM VARCHAR2(30);
INPUTSCHEMA VARCHAR2(30);

NIND_NAME DBA_INDEXES.INDEX_NAME%TYPE; -- VARCHAR(30);
NTAB_NAME DBA_INDEXES.TABLE_NAME%TYPE; --VARCHAR(30);
FRAG VARCHAR(10);
CMD VARCHAR2(500);
CMD_ONLINE VARCHAR2(512);

EXAMINE_ONLY NUMBER;
REPORT_STAT NUMBER;
NLOOP NUMBER;
LINES NUMBER;
FragRate NUMBER;
Iname VARCHAR(30);
DelRows INDEX_STATS.lf_rows%TYPE; --NUMBER
TabRows INDEX_STATS.lf_rows%TYPE; --NUMBER

BEGIN
DBMS_OUTPUT.PUT_LINE('-- INPUTSCHEMA MUSS!! GEQUOTED EINGEGEBEN WERDEN ');
USERNAM := &&INPUTSCHEMA;

EXAMINE_ONLY := &&NODELETE;

/* 0 ==> FALSE; 1 ==> TRUE */
REPORT_STAT := 1;
DBMS_OUTPUT.PUT_LINE('-- Reportmode ist '|| REPORT_STAT);
NLOOP := 0;

-- Hauptschleife über die INDIZES
FOR NIM_IND IN ( SELECT index_name, table_name FROM dba_indexes
WHERE OWNER = USERNAM AND Index_type = 'NORMAL')
LOOP

NIND_NAME := NIM_IND.index_name;
NTAB_NAME := NIM_IND.table_name;

NLOOP := NLOOP + 1;
DBMS_OUTPUT.PUT_LINE( NLOOP || ': INDEX ' || NIND_NAME);

-- nicht zählen nur "ist mindestens eine Zeile da"
CMD := 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || USERNAM || '.' || NTAB_NAME || ' WHERE ROWNUM <= 1)';

EXECUTE IMMEDIATE CMD INTO LINES;

DBMS_OUTPUT.PUT_LINE(NTAB_NAME || ' ZEILEN : '||LINES);

IF LINES > 0 THEN

--- Something to do?

CMD := 'ANALYZE INDEX ' || USERNAM || '.' || NIND_NAME || ' VALIDATE STRUCTURE';
CMD_ONLINE := CMD || ' ONLINE';

DBMS_OUTPUT.PUT_LINE(CMD);

-- Beschleunigung des Locking Statements durch Füllen des Caches
EXECUTE IMMEDIATE CMD_ONLINE;
EXECUTE IMMEDIATE CMD;

SELECT name, lf_rows , del_lf_rows
INTO Iname, TabRows , DelRows
FROM index_stats
WHERE name = NIND_NAME;

IF DELRows > 0 THEN
FragRate := 100*DelRows/Tabrows;
Frag := TO_CHAR((100*DelRows)/TabRows,'999.9');

IF REPORT_STAT <> 0 THEN
DBMS_OUTPUT.PUT_LINE(Iname || ' Rows: ' || TabRows || ' DelRows: '|| DelRows
|| ' Fragmented: ' || Frag);
END IF;

IF FragRate > 20 THEN

CMD := 'ALTER INDEX "' || USERNAM || '"."' || NIND_NAME || '" rebuild ONLINE';

IF REPORT_STAT <> 0 THEN
DBMS_OUTPUT.PUT_LINE(' ****** Fragmentierungsgrad ' || Frag || ' zu hoch, Index Reorg ist erforderlich ');
END IF;

IF EXAMINE_ONLY = 0 THEN
DBMS_OUTPUT.PUT_LINE(' REORG CMD: ' || CMD);
EXECUTE IMMEDIATE CMD;
COMMIT;
END IF;

END IF; -- FragRate

ELSE --DELROWS
DBMS_OUTPUT.PUT_LINE(Iname || ' 0 gelöschte Zeilen -> keine Fragmentierung ');
FragRate := 0;
END IF; -- DELROWS

END IF; -- LINES

END LOOP; -- NUM_TABLES;

END;
/
EXIT

Leider ist das Ermitteln der validen Indexstruktur maßgeblich teurer als meist der einfach Reorg.

(einleuchtende Erklärung https://blog.pythian.com/analyze-index-validate-structure-dark-side/)

Als Zwischenlösung gibt es einen einfachen Trick der im obigen Script angewandt wurde.

Zunächst ein

analyze index ix validate structure online;

der die Tabelle nicht lockt, jedoch auch INDEX_STATS nicht füllt, gefolgt von einem

analyze index ix validate structure;

DIe Hoffnung ist, jetzt sind die Daten im Cache und der die Tabelle lockende ANALYZE dauert nicht so lange.

Da dies jedoch mit den überaus großen Objekten des Monitoringsschemas unter Produktionsbedingungen nicht funktioniert hat, letztlich ein Job der einfach alle Indizes eines Schemas neu aufbaut.

Dies kann interaktiv erfolgen, oder je nach Bedarf in einem Zyklus. Ein Wartungsfenster ist nicht erforderlich, da der Job die Objekte nicht lockt. Davon würde Job2 profitieren, in einem Wartungsfenster ohne produktiven Betrieb wäre ein VALIDATE STRUCTUR als Kriterium des Rebuilds hilfreich, und dieser könnte ohne Lock durchgeführt werden.

Doch jetzt der Bulk-Job:

Script 3 -> INDEXWARTUNG_BULK.SQL

SET SERVEROUTPUT ON SIZE 1000000

/*
Wartungsjob: für einzugebendes SCHEMA wird ein INDEX REBUILD ONLINE angeworfen.
Dies ist bei Objekten deren Analyse teuer ist, die effektiveste Variante.

*/

DECLARE

USERNAM VARCHAR2(30);
INPUTSCHEMA VARCHAR2(30);

NIND_NAME DBA_INDEXES.INDEX_NAME%TYPE; -- VARCHAR(30);
NTAB_NAME DBA_INDEXES.TABLE_NAME%TYPE; --VARCHAR(30);
FRAG VARCHAR(10);
CMD VARCHAR2(500);
CMD_ONLINE VARCHAR2(512);

EXAMINE_ONLY NUMBER;
NLOOP NUMBER;
LINES NUMBER;
FragRate NUMBER;
Iname VARCHAR(30);
DelRows INDEX_STATS.lf_rows%TYPE; --NUMBER
TabRows INDEX_STATS.lf_rows%TYPE; --NUMBER

BEGIN
DBMS_OUTPUT.PUT_LINE('-- INPUTSCHEMA MUSS!! GEQUOTED EINGEGEBEN WERDEN ');
USERNAM := &&INPUTSCHEMA;

EXAMINE_ONLY := 1;
EXAMINE_ONLY := &&NOWRITE;

/* 0 ==> FALSE; 1 ==> TRUE */

NLOOP := 0;

-- Hauptschleife über die INDIZES
FOR NIM_IND IN ( SELECT index_name, table_name FROM dba_indexes
WHERE OWNER = USERNAM AND Index_type = 'NORMAL'
AND TABLE_NAME IS NOT NULL
ORDER BY INDEX_NAME
)
LOOP
BEGIN -- cause excepzion

NIND_NAME := NIM_IND.index_name;
NTAB_NAME := NIM_IND.table_name;

NLOOP := NLOOP + 1;
DBMS_OUTPUT.PUT_LINE( NLOOP || ': INDEX ' || NIND_NAME);

IF 0=1 THEN -- DOCH NICHT COUNT ausführen
-- nicht zählen nur "ist mindestens eine Zeile da"
CMD := 'SELECT COUNT(*) FROM (SELECT 1 FROM ' || USERNAM || '.' || NTAB_NAME || ' WHERE ROWNUM <= 1)';

EXECUTE IMMEDIATE CMD INTO LINES;

DBMS_OUTPUT.PUT_LINE(NTAB_NAME || ' ZEILEN : '||LINES);
ELSE
LINES := -1;
END IF;

IF LINES <> 0 THEN -- In Sonderfällen kann es nötig sein, trotzdem zu reorganiseren. Dann manuell!

--- Something to do?

CMD := 'ALTER INDEX "' || USERNAM || '"."' || NIND_NAME || '" rebuild ONLINE Nologging PARALLEL 4 ';

DBMS_OUTPUT.PUT_LINE(' REORG CMD: ' || CMD);
IF EXAMINE_ONLY = 0 THEN
DBMS_OUTPUT.PUT_LINE(' EXECUTING CMD ');
EXECUTE IMMEDIATE CMD;
COMMIT;
END IF;

END IF; -- LINES

EXCEPTION WHEN OTHERS THEN
BEGIN
IF SQLCODE = -942 THEN
-- http://www.dba-oracle.com/sf_ora_00942_table_or_view_does_not_exist.htm
DBMS_OUTPUT.PUT_LINE (' Misc.Problem Tabellenobjekt ' || NTAB_NAME );
END IF;
DBMS_OUTPUT.PUT_LINE ('Abgefangener Fehler : Errcode ' || SQLCODE);
GOTO END_LOOP;
END; -- EXCP

END;
<<END_LOOP>>
NULL;
END LOOP; -- NUM_TABLES;

END;
/
EXIT

Kategorie: INDEXREORG

Index Rebuild (Wartungsjob oder interaktiv)

File 2 -> INDEXWARTUNG_MITANALYZE.SQL

Script 3 -> INDEXWARTUNG_BULK.SQL