Probléme Performance lors de l'insertion de chargement data

**Mehdilis** · 16/02/2007, 01h55

Bonsoir,

J’ai une problématique sur oracle 9iR2, on a une base archive(contient 1 table temporaire volumineuse et 4 tables permanentes volumineuses) qui souffre des problèmes de performances ces jours-ci. Surtout au chargement des données qui dure une quinzaine d’heures au lieu de 2 heures précédemment.
En fait il y a un chargement de données dans la table temporaire (table d’entrée)qui contient des indexes.
1/Ma question quel est le meilleur a faire soit dropper les indexes, puis charger les données puis recréer les indexs (mais peut entre va mettre beaucoup de temps) , ou désactiver puis charger les données puis réactiver l’index avec monitoring/nomonitoring?

2/Une autre question, dans quel cas le rebuild d’index est conseiller apres un droppe/creation ou Desactiver/activer ?

3/Est ce que les clauses storage de la table (qui subit que des inserts et select) et de son tablespaces peuvent aussi contribuer aux problèmes de performance ?

4/Un conseil aussi à demander sur la répartition des données sur la baie de stockage.

Merci d’avance de vos suggestions et de vos efforts.

**orafrance** · 16/02/2007, 07h33

1/ ça ne se désactive pas un index

2/ aucun des deux. C'est lorsqu'il y a eu beaucoup de mise à jour des colonnes indexées ou des delete
3/ oui, si tu as beaucoup d'extents à allouer pendant l'insertion ça prendra plus de temps que si tu en as peu
4/ évite de mettre les index et les data ensemble

Pour finir, tu fais une erreur trop fréquente : tu supposes de l'origine du problème sans savoir quels sont les symptomes. Intéresses toi d'abord aux événements d'attentes et après tu verras si ça vient bien des indexes, des redos, des rollbacks ou autre chose.

**remi4444** · 16/02/2007, 12h01

Quel est le mode de chargement:
- sqlloader en mode conventionnel
- sqlloader en mode direct
- imp
- suite de requêtes d'insert unitaires

?

Dans les 15 heure est-ce que ça comprend:
- le chargement de la table uniquement
- le chargement + traitements (transfert de données de tables vers d'autres par exemple) ?

**Mehdilis** · 16/02/2007, 17h02

Merci pour fred et pour remi, en fait ce

Le mode de chargement c’est direct mais il n’ya pas le truncate et ce qui m’a étonné, car le Mode direct(sqlloader) et truncate sont deux action conjointes, la il est remplacé par Insert je pense que c’est pas terrible ?

Code :

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

1
2
3
4
5
6
7
8
9
10
OPTIONS
(
DIRECT=TRUE
)
LOAD DATA
INSERT
INTO TABLE ARCINFTMP
FIELDS TERMINATED BY X'11'
(
	Col1, col2,,,,,,,,,,,,)

2/ Les 15 heures Chargement sqlloader dans table de travail (point d’entrée)+ insert des datas de la table travail --- 3 tables permanentes, ce traitement est fait par un cursor et insertion ligne par ligne dans chacune des tables + commit pour les 3 tables dans la meme boucle cursor= pour celle la j’ai proposé d’enlever carrément le curseur et le remplacer par des requêtes genre insert into avec un select pour inserer dans chaque table, qu’en pensez-vous

Merci d’avance de votre aide ?

**Pomalaix** · 17/02/2007, 13h53

Envoyé par Fred_D

1/ ça ne se désactive pas un index

Exact pour les index ordinaires, mais on peut désactiver les index basés sur les fonctions !

**NGasparotto** · 17/02/2007, 14h19

Envoyé par Pomalaix

Exact pour les index ordinaires, mais on peut désactiver les index basés sur les fonctions !

En fait, on peut désactiver les index même ordinaires. Mais pour les réactiver cela implique des les rebuilder :

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
71
72
73
74
75
SQL> select * from conv where id = '1';

Execution Plan
----------------------------------------------------------
Plan hash value: 3089586456

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     7 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CONV_IDX |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"='1')

Note
-----
   - dynamic sampling used for this statement

SQL> alter index conv_idx unusable;

Index altered.

SQL> select * from conv where id = '1';

Execution Plan
----------------------------------------------------------
Plan hash value: 44426210

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |    18  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CONV |     1 |     7 |    18  (12)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"='1')

Note
-----
   - dynamic sampling used for this statement

SQL> alter index conv_idx rebuild;

Index altered.

SQL> select * from conv where id = '1';

Execution Plan
----------------------------------------------------------
Plan hash value: 3089586456

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     7 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| CONV_IDX |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"='1')

Note
-----
   - dynamic sampling used for this statement

SQL>

Nicolas.

**Pomalaix** · 17/02/2007, 14h57

Envoyé par NGasparotto

En fait, on peut désactiver les index même ordinaires. Mais pour les réactiver cela implique des les rebuilder :

Oui, moi j'avais en tête une désactivation douce, "non destructive", qui se fait via ALTER INDEX xxx DISABLE

**remi4444** · 17/02/2007, 21h15

De toutes façons, le mode direct implique qu'il y a cette desactivation pendant le chargement.

Ton traitement est donc séparé en plusieurs étapes, il faudrait donc que tu chronomètres chacune de ces étapes ça t'aideras à cibler le point qui cloche, et mon petit doigt me dit que ça ne doit pas etre la partie SQL*LOADER...

Effectivement le curseur ligne à ligne de doit pas etre génial question perf, surtout avec un commit à chaque ligne. Aussi, il faut que chaque requête du creu de la boucle soit parfaitement optimisée. Car si jamais une seule de ces requête met un dixieme de seconde de trop, à la fin c'est la catastrophe...

Tu as raison, un traitement ensembliste sera beaucoup plus efficace qu'une grande boucle.

**orafrance** · 17/02/2007, 21h23

Envoyé par NGasparotto

En fait, on peut désactiver les index même ordinaires.

Attention, cela impose de modifier la session :

Code :

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

alter session set skip_unusable_indexes=true

Par ailleurs, il me semble que ça n'évite pas les erreurs relatives aux contraintes unique si l'index est une PK ou unique

**NGasparotto** · 17/02/2007, 22h08

Envoyé par Fred_D

Attention, cela impose de modifier la session :

Code :

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

alter session set skip_unusable_indexes=true

Ce qui est la valeur par défaut au moins en 10g. Mais je suis d'accord sur le fond, ca ne solutionne pas les pk et unique, c'était juste un commentaire sur la désactivation des indexes.

Nicolas.

**Mehdilis** · 17/02/2007, 23h36

Je vous remercie tous pour vos motivations et vos conseils.
Toujours sur ce sujet, si on fait un drop index sur les trois tables permanente puis chargement des données par insert puis recréer les indexes supprimés, penseriez-vous que c’est une bonne solution ?, moi je pense déjà au moment de leur suppression et surtout au moment de leur création oracle mettra beaucoup de temps car il fait des tris de toutes les données de la table et il requiert beaucoup d’espace, donc ce qu’on a gagné dans la rapidité d’insertion on l’a perdu dans le temps de drop et recréation,
de plus ne serait pas avantageux dans le cas ou je veux transformer ce curseur (voir description problématique avant) par une requete ensembliste comme je l’ai déjà proposé c adire insert suivi par select (bulk de données), car ce select il a besoin des index pour sortir les valeurs rapidement. Qu’en pensez vous svp ? j’aime toujours avoir l’avis des experts que je leurs en remercie beaucoup.

Une question aussi, sur les clauses storage (et pctfree et pctused) des tables :
-de travail (chargée par sql loader) ?
- Aussi les tables permanentes qui sont chargées par des inserts c’est quoi leurs pctfree et pctused idéal sachant qu’il y a que de l’insertion seulement et peu de delete ? et aussi bien pour les clauses storage tablespace ?

Merci d’avance pour vos réponses