Discussion :

[tibal]

Bonjour,
Je suis en train de mettre en place un script de rebuild d'indexes (voir ci-dessous) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME          => 'SCOTT', -
                                       OPTIONS          => 'GATHER AUTO', -
                                       ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, -
                                       METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO', -
                                       DEGREE           => 35, -
                                       GRANULARITY      => 'AUTO', -
                                       CASCADE          => TRUE, -
                                       FORCE            => TRUE);
 
 
 
SET serveroutput ON size unlimited 
SET pagesize  0 
SET linesize  132 
SET feedback  ON 
SET echo      ON 
SET trimspool ON 
SET verify    ON 
 
DECLARE
  v_Owner     all_indexes.owner%TYPE;
  v_IdxName   all_indexes.index_name%TYPE;
  v_NumRows   INTEGER;
  v_Height    index_stats.height%TYPE;
  v_LfRows    index_stats.lf_rows%TYPE;
  v_DLfRows   index_stats.del_lf_rows%TYPE;
  v_DLfPerc   NUMBER;
  v_MaxHeight NUMBER;
  v_MaxDel    NUMBER;
 
  CURSOR cur_GetIdx IS SELECT owner,
                              index_name,
                              num_rows
                         FROM all_indexes
                        WHERE OWNER NOT LIKE 'SYS%'
                        AND num_rows IS NOT NULL
                        ORDER BY num_rows desc ;
BEGIN
  v_MaxHeight := 1;
  v_MaxDel    := 2;
 
  FOR CUR IN cur_GetIdx LOOP
     v_Owner := CUR.owner ;
     v_IdxName := CUR.index_name ;
     v_NumRows := CUR.num_rows ;
     DBMS_OUTPUT.PUT_LINE(v_Owner || ' ' || v_IdxName || ' ' || v_NumRows);
 
     SELECT height,
	   lf_rows,
	   del_lf_rows INTO v_Height,v_LfRows,v_DLfRows
       FROM INDEX_STATS ;
 
     IF v_DLfRows = 0 THEN
        v_DLfPerc := 0;
     ELSE
        v_DLfPerc := (v_DLfRows / v_LfRows) * 100;
     END IF;
     IF (v_Height > v_MaxHeight) OR (v_DLfPerc > v_MaxDel) THEN 
        DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || v_Owner || '.' || v_IdxName || ' REBUILD;');
     END IF;
 
  END LOOP;
 
END;
/

J'obtiens l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SH CUSTOMERS_PK 55500
DECLARE
*
ERREUR Ó la ligne 1 :
ORA-01403: no data found
ORA-06512: at line 31

J'ai réussi à isoler cette erreur qui provient de la partie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
     SELECT height,
	   lf_rows,
	   del_lf_rows INTO v_Height,v_LfRows,v_DLfRows
       FROM INDEX_STATS ;

Mais je ne comprends pas pourquoi cette sort ?
Quelqu'un a t'il une petite idée ?
Merci par avance.

[orafrance]

si il n'y a pas de stats sur l'index INDEX_STAT ne renvoie rien non ?

[mydb_server]

La table INDEX_STATS n'est publiée que si on exécute la commande :
"ANALYZE INDEX .... VALIDATE STRUCTURE;" autrement elle est toujours vide.
En plus, il n'y a que la session qui a exécuté l'ordre ci-dessus qui voit les lignes de cette table.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SQL> conn scott/tiger
Connected.
SQL> 
SQL> select count(*) from index_stats;
 
  COUNT(*)
----------
         0
 
SQL> 
SQL> select index_name from user_indexes;
 
INDEX_NAME
------------------------------
PK_DEPT
PK_EMP
 
SQL> 
SQL> alter index pk_dept rebuild;  
 
Index altered.
SQL> 
SQL> select count(*) from index_stats;
 
  COUNT(*)
----------
         0
 
SQL> exec dbms_stats.gather_index_stats(user, 'PK_DEPT');
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from index_stats;
 
  COUNT(*)
----------
         0
 
SQL> analyze index PK_DEPT validate structure;
 
Index analyzed.
 
SQL> 
SQL> select count(*) from index_stats;
 
  COUNT(*)
----------
         1
SQL> connect scott/tiger -- Nouvelle session
 
SQL> select count(*) from index_stats;
 
  COUNT(*)
----------
         0

Les statistiques sur les indexes sont stockées dans la table DBA_INDEXES

Rachid A.

[pachot]

Bonjour,

Juste une mise en garde: est-ce que vous connaissez
- le coût de l'analyze index
- les conséquences du rebuild
- le fait que vous allez rebuilder à chaque fois tous les index qui ont plus d'un niveau, donc la pluspart des indexes sur les tables consequentes
- le fait que vous allez rebuilder les indexes sur lesquels il y a eu 2% d'entrées supprimées (suite à delete ou update) même si cet espace est réutilisable
- le fait que en fonction du pctfree de l'index, vous risquez de vous retrouver avec encore plus de vide après qu'avant ?

Cordialement,
Franck

[tibal]

Merci pour toutes vos réponses.

Franck,

le coût de l'analyze index

Non, je ne connais pas le coût d'analyse d'un indexe.

les conséquences du rebuild

Il me semblait qu'un rebuild, en fonction des critères amenant à la décision de l'effectuer, permet d'obtenir de meilleurs performances.

le fait que vous allez rebuilder à chaque fois tous les index qui ont plus d'un niveau, donc la pluspart des indexes sur les tables consequentes

J'ai positionné cette valeur volontairement basse juste pour mes tests, mais lors du passage en production, je la positionnerai à 3.

le fait que vous allez rebuilder les indexes sur lesquels il y a eu 2% d'entrées supprimées (suite à delete ou update) même si cet espace est réutilisable

Idem que pour la réponse précédente, je la positionnerai à 20%.

le fait que en fonction du pctfree de l'index, vous risquez de vous retrouver avec encore plus de vide après qu'avant ?

Etant en 10g, le pctfree est géré par Oracle, donc je supose (peut être à tort) qu'il le fait correctement.

Orafrance & Farid,

J'ai suivi vos conseils et ai modifié mon script comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME          => 'SCOTT', -
                                       OPTIONS          => 'GATHER AUTO', -
                                       ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, -
                                       METHOD_OPT       => 'FOR ALL COLUMNS SIZE AUTO', -
                                       DEGREE           => 35, -
                                       GRANULARITY      => 'AUTO', -
                                       CASCADE          => TRUE, -
                                       FORCE            => TRUE);
 
 
 
SET serveroutput ON size unlimited 
SET pagesize  0 
SET linesize  132 
SET feedback  ON 
SET echo      ON 
SET trimspool ON 
SET verify    ON 
 
DECLARE
  v_Owner     all_indexes.owner%TYPE;
  v_IdxName   all_indexes.index_name%TYPE;
  v_NumRows   INTEGER;
  v_Height    index_stats.height%TYPE;
  v_LfRows    index_stats.lf_rows%TYPE;
  v_DLfRows   index_stats.del_lf_rows%TYPE;
  v_DLfPerc   NUMBER;
  v_MaxHeight NUMBER;
  v_MaxDel    NUMBER;
  v_Analyze   varchar(4000);
  v_Rebuild   varchar(4000);
 
  CURSOR cur_GetIdx IS SELECT owner,
                              index_name,
                              num_rows
                         FROM all_indexes
                        WHERE OWNER = 'SCOTT'
                          AND num_rows IS NOT NULL
                     ORDER BY num_rows DESC ;
BEGIN
  v_MaxHeight := 0;
  v_MaxDel    := 2;
 
  FOR CUR IN cur_GetIdx LOOP
     v_Owner := CUR.owner ;
     v_IdxName := CUR.index_name ;
     v_NumRows := CUR.num_rows ;
 
	 v_Analyze := 'ANALYZE INDEX ' || v_Owner || '.' || v_IdxName || ' VALIDATE STRUCTURE';
	 EXECUTE IMMEDIATE v_Analyze;
 
     SELECT height,
	   lf_rows,
	   del_lf_rows INTO v_Height,v_LfRows,v_DLfRows
       FROM INDEX_STATS ;
 
     IF v_DLfRows = 0 THEN
        v_DLfPerc := 0;
     ELSE
        v_DLfPerc := (v_DLfRows / v_LfRows) * 100;
     END IF;
     IF (v_Height > v_MaxHeight) OR (v_DLfPerc > v_MaxDel) THEN 
		 v_Rebuild := 'ALTER INDEX ' || v_Owner || '.' || v_IdxName || ' REBUILD;';
		 EXECUTE IMMEDIATE v_Rebuild;
     END IF;
 
  END LOOP;
 
END;
/

Par contre, j'ai une erreur sur l'EXECUTE IMMEDIATE v_Rebuild et ne vois pas d'où elle peut venir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
ALTER INDEX SCOTT.PK_EMP REBUILD;
DECLARE
*
ERREUR Ó la ligne 1 :
ORA-00911: caractÞre non valide
ORA-06512: Ó ligne 47

Merci encore à tous les trois.

[tibal]

J'ai trouvé mon erreur, j'avais un ; de trop. Je ne suis pas tout à fait réveillé.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

v_Rebuild := 'ALTER INDEX ' || v_Owner || '.' || v_IdxName || ' REBUILD;';

[pachot]

Bonjour Tibal,
Il y a beaucoup d'incompréhensions dans ce que tu dis.
- Un gros index peut avoir 3 niveaux, même après le rebuild, et tu risques donc de le rebuilder à chaque fois.
- del_lf_rows n'a vraiment rien a voir avec les performances d'un index. c'est tout à fait normal d'avoir des entrées d'index supprimées, l'espace vie dest très vite réutilisé.
- rebuilder un index peut entrainer des performances bien pires: il va probablement vite retrouver sa taille, et pour celà beaucoup de leaf block split, redo et undo généré...
- PCTFREE géré tout seul par Oracle ? oh non ! absolument pas ! Et c'est toujours un paramètre très important pour les tables et les indexes.
Imagine: si tu mets un pctfree trop grand, ton rebuild ne va pas diminuer la taille de l'index. Si tu mets pctfree trop bas, alors l'index n'aura plus asses de place pour son activité, les blocs vont devoir être splittés pour faire de la place, et ca veut dire qu'ils passent à 50% d'espace libre.
Résultat: beaucoup d'activité pour ces splits, pour finalement retrouver au bout de quelques jours des index pires qu'avant. Et là donc à nouveau un rebuild... On tourne en rond en consommant des ressources !

Il y a quelque chose qui est géré tout seul par Oracle, et qui marche dans la pluspart des cas, c'est ma mise à jour des indexes. Là tu peux lui faire confiance, la pluspart des indexes atteignent leur taile idéale très vite, et la gardent.

Il y a tellement de gens qui bouffent les ressources de la machine en faisant des rebuilds qui ne servent à rien... Le seul critère qui fe ferait penser à revoir un index, serait le fait que ses niveaux augmentent.

Cordialement,
Franck.

[tibal]

Franck, merci pour ces infos.

Un gros index peut avoir 3 niveaux, même après le rebuild, et tu risques donc de le rebuilder à chaque fois.

N'y a t'il pas une profondeur au-delà de laquelle on peut estimer que l'indexe doit être rebuilder ?

del_lf_rows n'a vraiment rien a voir avec les performances d'un index. c'est tout à fait normal d'avoir des entrées d'index supprimées, l'espace vie est très vite réutilisé.

Oui mais si le pourcentage est élevé, ça signifie qu'on va parcourir des blocs partiellements vides. Cela n'a t'il acun impact sur les performances ?

rebuilder un index peut entrainer des performances bien pires: il va probablement vite retrouver sa taille, et pour celà beaucoup de leaf block split, redo et undo généré

Je suis d'accord avec toi sur le fait que certains index risquent de rapidement retrouver leur taille. Par contre, je ne savais pas que les leaf block split généraient des Redo/Undo. Peux-tu m'expliquer comment cela fonctionne ?

PCTFREE géré tout seul par Oracle ? oh non ! absolument pas ! Et c'est toujours un paramètre très important pour les tables et les indexes.

Je suis en Oracle 10g et il me semblait, sauf erreur de ma part, que la clause EXTENT MANAGEMENT LOCAL AUTOALLOCATE de création des tablespaces était justement là pour tout gérer ?

Merci encore pour tes infos.

[apersonnat]

Bonjour,

Juste pour ajouter que Tom Kytes (bien connu des DBAs) recommande fortement de ne (quasiment) jamais faire de rebuild d'index et encore moins de rebuild automatique.

Pour ma part j'ai tout de même constaté dans des cas précis une augmentation significative des performances après avoir reconstruit un index.

Le débat est toujours ouvert ..

Bonne journée,
Alain

[pachot]

N'y a t'il pas une profondeur au-delà de laquelle on peut estimer que l'indexe doit être rebuilder ?

Si en le rebuildant, la profondeur diminue, et celà durablement, alors oui. Mais dans la pluspart des cas, tu va retrouver la même profondeur, immédiatement ou au bout de quelques jours.

Oui mais si le pourcentage est élevé, ça signifie qu'on va parcourir des blocs partiellements vides. Cela n'a t'il acun impact sur les performances ?

L'espace vide n'est gênant qu'en range scan, lorsque ton index ramène beaucoup de lignes. Ce n'est pas un cas général.

Et puis del_lf_rows n'est pas représentatif de cet espace vide.

un exemple: je crée une table, supprime les 72000 lignes et je n'ai que 21000 del_lf_rows. Je recrée seulement une ligne sur 10, donc 7000 lignes et del_lf_rows est maintenant à zéro. Alors qu'il reste 90% d'espace libre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SQL> create table test as select * from dba_objects;
Table created.
SQL> create index testi on test(object_id);
Index created.
SQL> delete from test;
72130 rows deleted.
 
SQL> analyze index testi validate structure;
Index analyzed.
SQL> select del_lf_rows from index_stats;
DEL_LF_ROWS
-----------
      21234
 
SQL> insert into test select * from dba_objects where mod(object_id,10)=0;
7238 rows created.
 
SQL> analyze index testi validate structure;
Index analyzed.
SQL> select del_lf_rows from index_stats;
DEL_LF_ROWS
-----------
	  0

Mais de toute facon, tout cet espace libre est réutilisables pour les nouveux inserts dans les mêmes tranches de valeurs. Et au pire, un coalece est beaucoup moins couteux qu'un rebuild.

Je suis d'accord avec toi sur le fait que certains index risquent de rapidement retrouver leur taille. Par contre, je ne savais pas que les leaf block split généraient des Redo/Undo. Peux-tu m'expliquer comment cela fonctionne ?

Un peu compliqué, mais lorqu'un bloc est plein, il faut le couper en deux, et mettre à jour la branche au dessus. Donc au moins 4 fois plus de blocs modifiées, partiellement ou totalement => de l'undo et du redo.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Je suis en Oracle 10g et il me semblait, sauf erreur de ma part, que la clause EXTENT MANAGEMENT LOCAL AUTOALLOCATE de création des tablespaces était justement là pour tout gérer ?

Non, rien a voir avec le PCTFREE. Tu confonds peut-être avec PCTUSED qui, en 'EXTENT MANAGEMENT LOCAL' (appelé LMT), est inutile si on utilise en plus 'SEGMENT SPACE MANAGEMENT AUTO' (appelé ASSM, possible en LMT, mais pas obligatoire non plus). Ce n'est pas tout à fait pareil !


En fait, ce que fait un rebuild d'index, c'est de reconstruire l'index de telle sorte que chaque bloc ait PCTFREE d'espace libre. C'est la seule chose qu'il fait. Son seul but. C'est pour celà que je dis que si tu ne connais pas le PCTFREE, celà n'a aucun sens.
Pour faire une comparaison idiote, ce serait comme changer de boulot sans connaître ni ton salaire actuel, ni le salaire futur, et s'imaginer que de toute facon tu gagnera plus. Y'a des fois où ça marchera, mais la pluspart du temps non.