Rebuild gros index

Version imprimable

27/06/2013, 23h08
zidane2012

Rebuild gros index

Salut
pour rebuilder un gros index ( 7Go) j'étais contraint d'utiliser les techniques suivantes :

1/ parallel
2/nologging

mais j'ai entendu parler d'astuces au niveau de la mémoire PGA et TEMP permettant faire l'opération plus rapidement et éviter toute erreur liée à une mémoire insuffisante lors du rebuild ? une idée ?

Sinon le rebuild est-il plus rapide qu'un drop/create ? pourquoi ?
merci par avance :ccool:
02/07/2013, 16h54
rouardg

Bonjour,

Lorsque tu crées un index sur 1 ou plusieurs colonnes C1, C2...Cn, Oracle est obligé de balayer complétement la table pour lire les différentes valeurs de cette(s) colonne(s), et de les trier afin de pouvoir construire l'index.

Le tri s'effectue normalement en mémoire, dans la PGA. Mais si celle-ci est trop petite, alors Oracle va utiliser le tablespace temporaire, afin d'y stocker temporairement les segments de tri, d'où un ralentissement.

Il faut donc vérifier que la PGA est suffisamment grande pour éviter l'utilisation du TEMP.

Sinon dropper et recréer un index est souvent plus long que le rebuilder. En effet, la recréation de l'index nécessite de balayer toute la table (Full Scan Table), alors que le rebuild de l'index nécessite de ne relire que l'index (attention, c'est un Full Scan d'index, et non pas un Fast Full Scan d'index).

Et souvent, relire l'index est plus rapide que relire la table, car il est moins volumineux. L'inconvénient, c'est qu'il faut temporairement le double de place.

NB : le seul cas où cela ne marche pas fait suite à un MOVE de la table. En effzet, si tu déplaces ta table, tous ses index deviennent UNUSABLE (à cause des changements de ROWID des lignes). Du coup, le Rebuild d'index va lire toute la table, au lieu de lire l'index.
03/07/2013, 08h48
Mohamed.Houri

Citation:

En effet, la recréation de l'index nécessite de balayer toute la table (Full Scan Table), alors que le rebuild de l'index nécessite de ne relire que l'index (attention, c'est un Full Scan d'index, et non pas un Fast Full Scan d'index).

Non, je ne pense pas que cela soit totalement correct. Il conviendrait de changer légèrement votre texte pour que cela soit exact.

En effet, la recréation de l'index nécessite de balayer toute la table (Full Scan Table), alors que le rebuild offline de l'index nécessite de ne relire que l'index (attention, c'est un Full Scan d'index, et non pas un Fast Full Scan d'index).

Citation:

NB : le seul cas où cela ne marche pas fait suite à un MOVE de la table. En effzet, si tu déplaces ta table, tous ses index deviennent UNUSABLE (à cause des changements de ROWID des lignes). Du coup, le Rebuild d'index va lire toute la table, au lieu de lire l'index.

Le rebuild, même offline, d'un index ayant un statut UNUSABLE, se fait en se basant sur la table qui lui est associée et non sur l'index lui même.
10/07/2013, 00h15
zidane2012

Merci pour vos réponses.
et le cas d'un rebuild online ...comment cela se passe ?
Sinon l'astuce d'augmenter la PGA avant de reconstruire l'index ? efficace ? comment faire et quand exactement ?
merci beaucoup :ccool:
15/07/2013, 01h49
zidane2012

bonjour ,
Je pense utiliser le paramètre caché _pga_max_size (par défault = 200 Mo).
L'objectif est de booster le rebuild d'index ...

Code:

alter session set "_pga_max_size"=2000000000;

votre avis ?merci
a+
18/07/2013, 15h28
Rei Ichido

Question subsidiaire : pourquoi faire un REBUILD de cet index ?

Est-ce qu'un COALESCE ne suffirait pas (voire, serait plus approprié) ?
19/07/2013, 23h30
pachot

Bonjour,

Citation:

Envoyé par zidane2012

ALTER session SET "_pga_max_size"=2000000000;

Plutôt que de bidouiller l'allocation automatique de la PGA, pourquoi ne pas passer en PGA manuelle avec workarea_size_policy=manual et sort_area_size=2000000000
(à supposer que votre OS aime bien manipuler des process de 2GB...)
Cordialement,
Franck.
20/07/2013, 12h21
Mohamed.Houri

Citation:

Envoyé par Rei Ichido

Question subsidiaire : pourquoi faire un REBUILD de cet index ?

Est-ce qu'un COALESCE ne suffirait pas (voire, serait plus approprié) ?

Très bonne remarque.

Rarement un rebuild d'un index conduit à un index performant. Il se peut qu’immédiatement après le rebuild les choses s’améliorent mais l'index ne tardera pas à reprendra son état d'avant le rebuild. Lors d'une discussion avec Tom Kyte en 2008 à Paris par rapport à ce sujet il m'a fait une analogie (oui marius encore une analogie sur la vie réelle) en me montrant les quelques kilos de son ventre. Il me dit alors " Voyez vous, je fais un régime pour perdre les kilos de mon ventre mais je me rends compte qu'à chaque fois que perds quelques kilos je les reprends par la suite. Rebuild des indexes c'est kif kif"

Je ne vais pas sauter à la conclusion qu'il ne faut jamais faire un rebuild d'un index. Par contre, je dirai qu'il faut vraiment être sûr que votre index bénéficiera d'un rebuild. Et pour le savoir, il faut faire un travail préalable à ce rebuild comme utiliser les scripts de jonathan Lewis

Par contre, le COALESCE (et le COMPRESS à la création de l'index) sont deux fonctionnalités très très intéressantes mais malheureusement à mon sens très peu utilisées. En tout cas moi j'utilise activement le compress, et j'ai déjà appliqué le COALESCE à plusieurs reprises.

Mais avant de faire un COALESCE j'utilise la fonction interne sys_op_lbid qui donne la répartition des leaf blocks id (lbid) par clé d'index. Si par "leaf block key" il faut visiter plusieurs blocks, alors un COALESCE s'impose.
21/07/2013, 17h08
zidane2012

Bonjour ,

Citation:

Envoyé par pachot

pourquoi ne pas passer en PGA manuelle avec workarea_size_policy=manual et sort_area_size=2000000000

Effectivement je préfère votre méthode que la mienne (utilisant un paramètre caché...un DBA de prod m'a donné cette idée ...).
Cela me permettrais donc de reconstruire rapidement mon index après je pourrais remettre les 2 paramètres à leurs valeurs initiales.Merci Franck.
21/07/2013, 17h43
zidane2012

Citation:

Envoyé par Mohamed.Houri

Par contre, le COALESCE (et le COMPRESS à la création de l'index) sont deux fonctionnalités très très intéressantes mais malheureusement à mon sens très peu utilisées. En tout cas moi j'utilise activement le compress, et j'ai déjà appliqué le COALESCE à plusieurs reprises.

La compression de clé d'index exige des conditions pour l'utiliser efficacement.
http://docs.oracle.com/cd/E11882_01/...3.htm#i1106790
Sinon le coalesce reste efficace quand la reconstruction de l'arbre n'est pas obligatoire.
Pratique aussi quand l'espace disque est limité.Merci Mohamed.

Citation:

coalesce reste efficace quand la reconstruction de l'arbre n'est pas obligatoire.

C'est quoi ça?

Vous voulez dire, que c'est soit l'un(coalesce) soit l'autre (rebuild)?

Qu'est ce que vous avez fait comme tests pour arriver à ce que la reconstruction de votre index devienne obligatoire?

Citation:

Pratique aussi quand l'espace disque est limité

Non c’est inexact ce que vous avancez ici. Le Coalesce ne fait pas gagner de place disque. Il garde le même nombre de blocs dans l’index et garde aussi la même taille de l’index. Par contre, il produit plus de blocs vides en traversant l’index, à partir des leaf blocks, bloc par bloc en se disant ''ah j’ai assez d’espace dans ce block pour y mettre les données du block adjacent’’. Ceci permet alors de libérer un bloc de données dans l’index et ainsi de suite avec les autres blocs de l'index.

Voici un exemple de ce que j’avance:

J’ai pris un index de ma base de données

Code:

1
2
3
 
select * from user_objects where object_name = ‘MY_IND’;
154813

Sur lequel j’applique la function sys_op_lb_id qui me donne la répartition des leaf block key per clé d’index

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SQL > select
  2      keys_per_leaf, count(*) blocks
  3  from (
  4        select   sys_op_lbid (144311, 'L', ptc.rowid) block_id,
  5               count (*)                            keys_per_leaf
  6        from my_table  ptc
  7         where ptc.ind_col is not null
  8        group by sys_op_lbid (144311, 'L', ptc.rowid)
  9       )
 10  group by keys_per_leaf
 11  order by keys_per_leaf
 12  ;

Ceci me donne la répartition suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
Keys per leaf     blocks
1	              37
2	              13
3	              7
4	              6
5	              5
6	              6
7	              1
8	              3
9	              1
…

Cet index, tout en n’étant pas catastrophique, montre que pour avoir une clé par leaf_block on doit accéder à 37 blocs. J’ai vu des fois des 1 pour 800.

Je vais donc faire un coalesce de cet index. Mais avant de faire cela je regarde sa taille et la répartition des données dans les blocs de cet index

Code:

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
 
SQL > begin
  2     p_check_free_space (user,’ MY_IND’, 'INDEX');
  3  end;
  4  /
Number of Blocks with 0-25% free space  = 0                -------> Total Bytes = 0
Number of Blocks with 25-50% free space  = 2964            -------> Total Bytes = 23.15625
Number of Blocks with 50-75% free space  = 0               -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0               -------> Total Bytes = 0
Number of Full Blocks with no free space = 436             -------> Total Bytes = 3.40625
Total Bolcks ______________________________
3400
Total Size MB______________________________
27.2
 
PL/SQL procedure successfully completed.
 
SQL > alter index MY_IND coalesce;
 
Index altered.
 
SQL > begin
  2     p_check_free_space (user, 'MY_IND', 'INDEX');
  3  end;
  4  /
Number of Blocks with 0-25% free space  = 0                  -------> Total Bytes = 0
Number of Blocks with 25-50% free space  = 3224            -------> Total Bytes = 25.1875
Number of Blocks with 50-75% free space  = 0                 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0                 -------> Total Bytes = 0
Number of Full Blocks with no free space = 176               -------> Total Bytes = 1.375
Total Bolcks ______________________________
3400
Total Size MB______________________________
27.2
 
PL/SQL procedure successfully completed.
 
SQL > select
  2      keys_per_leaf, count(*) blocks
  3  from (
  4        select   sys_op_lbid (144311, 'L', ptc.rowid) block_id,
  5               count (*)                            keys_per_leaf
  6        from my_table  ptc
  7         where ptc.ind_col is not null
  8        group by sys_op_lbid (144311, 'L', ptc.rowid)
  9       )
 10  group by keys_per_leaf
 11  order by keys_per_leaf
 12  ;
 
KEYS_PER_LEAF     BLOCKS
------------- ----------
            1          5
            2          7
            3          2
            5          5
            8          1
           10          1
           11          1
           12          1
           13          1
           15          2
           16          1
           18          1
           20          1
           38          1
           50          1
           68          1
          108          1
          128          1
          155          1
          172          1
          199          1
          209          2
          262          1
          320          1
          323          1
          377        119
 
26 rows selected.

Le Coalesce n’a pas réduit le nombre de blocs (3400) ni la taille de l’index (27,2 MB); par contre il a produit 260 blocs supplémentaires ayant un espace libre allant de 25-50% et a réduit le nombre de blocs ''full'' de 260 (de 436 blocs à 176).

Quand au Compress, la documentation que vous citez ne mentionne pas l’intérêt de mettre la colonne ayant le moins de valeurs distinctes en première position dans l’index(lorsque nous avons le choix de le faire) afin de profiter au maximum d’une compression efficace.

22/07/2013, 22h10
pachot

Bonjour,

Citation:

Envoyé par zidane2012

Effectivement je préfère votre méthode que la mienne (utilisant un paramètre caché...un DBA de prod m'a donné cette idée ...).
Cela me permettrais donc de reconstruire rapidement mon index après je pourrais remettre les 2 paramètres à leurs valeurs initiales

Oui. Par exemple, trier 7 GB devrait se faire en mémoire avec un sort_area_size=900M et un degré de parallélisme de 8.

Un exemple pour modifier/vérifier la taille utilisée: http://ora-demo.pachot.net/workarea_size_policy.html

Cordialement,
Franck.
21/08/2013, 16h06
ldiaz

Bonjour a tous

cette fonction: p_check_free_space
C'est uen fonction perso de Mohammed n'est ce pas?