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;';

[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.