Performance d'un update de plusieurs colonnes en full scan

[Gnatit]

Bonjour,

N'arrivant pas à trouver de solutions à mon problème, je finis par venir demander de l'aide car je n'avance plus. J'ai une table sur laquelle je dois mettre à jour plusieurs colonnes. Toutes les lignes doivent être mises à jour. Comme je dois faire un full scan pour mettre à jour chaque colonnes, je mets à jour toutes les colonnes avec un seul update. J'ai donc une requête de ce type là:

Code :

1
2
3
4
5
UPDATE matable SET
col1 = case ... when ... else ... end,
col2 = case ... when ... else ... end,
col3 = case ... when ... else ... end,
col4 = case ... when ... else ... end;

Sur Oracle 10g, cet update mettait juste deux fois plus de temps que la mise à jour d'une colonne, 1h00 on va dire.

Depuis le passage à Oracle 11g, la mise à jour met à peut près 15h alors que la mise à jour d'une colonne met toujours 0h30. Il devient en fait plus rentable de mettre à jour toutes les colonnes séparément.

Je me demandais donc ce qui pouvait cause ce problème de performances ou si vous aviez des pistes de recherche.

Pour ce que ca vaut, le plan d'exécution est le même que je mette à jour une ou toutes les colonnes.

Merci d'avance!!

[mnitu]

Difficile à dire ce qui ne va pas avec seulement ces informations. Un petit exemple sera bien venu.
Il est plus performant de récréer une table que de mettre à jour tous ces lignes.

[ojo77]

Je rejoins Mnitu, l'expérimentation montre qu'à partir de 5 % des blocs modifiés (en mode direct) ou 15 % (avec logging contraint) il est plus intéressant de recréer un table dans laquelle les données sont à jour et de l'échanger ensuite avec l'originale.

[Gnatit]

Je mets à jour 25 colonnes sur 260, de tout type (des objets géométriques, des varchars, des integer). Cependant, les colonnes ne sont pas remplies avant la mise à jour et sont toutes à null car je viens de les créer. Je ne peux pas les remplir avant malheureusement.

J'aimerai mettre un exemple mais la requête est trop important et trop lié au projet pour la modifier simplement.

Je vais tester la création d'une autre table. Merci, je compléterai si ca ne marche toujours pas.

[Waldar]

Vous n'avez pas un problème de PCTUSED / PCTFREE sur votre tablespace en 11g ?

[Gnatit]

Possible, je ne connais pas ces paramètres. Je me renseigne mais si je comprends leur signification, je ne vois pas comment les consulter.

edit: Sous oracle 11g pour cette table j'ai ceci:
PCT_FREE 10
PCT_USED null

Pareil sous oracle 10g

[Mohamed.Houri]

Envoyé par Gnatit

Bonjour,

Depuis le passage à Oracle 11g, la mise à jour met à peut près 15h alors que la mise à jour d'une colonne met toujours 0h30. Merci d'avance!!

Bonjour,

Comme cela a déjà été signalé, il est difficile de se faire une idée quant aux causes ayant entrainé cette lenteur manifeste de votre update suite à votre migration de base de données de 10g vers 11g. Mais quelqu'ait été la methode utilisée pour faire cette migration, beaucoup de choses auquelles vous ne vous attendiez pas peuvent surgir à la fin de cette migration. Vous ne parlez pas des indexes qui existent sur cette table? ont-ils changé de définition? les données (clustering factor) ont-elles été eparpillées lors de cette migration? pas de where clause dans cet update, quelle type de table, etc... Vous ne "postez" pas aussi l'explain plan suivi par cet update.

Néamoins, avez vous vérifié, les nouvelles valeurs des paramètes de l'optimisateur qui sont par exemple

Code :

1
2
3
4
 
  (a) db_file_multiblock_read_count
  (b) optimizer_index_cost_adj
  (c) optimizer_index_caching

en 11g par rapport à ce que vous aviez en 10g?

Avez-vous tentez de faire votre update en utilisant le hint

Code :

1
2
 
   /*+ optimizer_features_enable('10.2.0.4') */

10.2.0.4 doit correspondre à la base que vous aviez avant la migration

Bien cordialement

Mohamed Houri

[Gnatit]

Si il y a eu des problèmes lors de la migration (il y en a eu), ils ne devraient pas avoir d'effets sur cette table car elle est recréée toutes les semaines et donc a été créée sous oracle 11g.

Les paramètres que vous citez sont les mêmes sous oracle 10g et 11g, respectivement 16, 100 et 0.

Je ne connaissais pas ce hint, je vais essayer. J'aurais d'ailleurs du préciser que j'utilise deux hints pour l'update: /*+ FULL(matable) PARALLEL(matable, DEFAULT) */

Merci beaucoup pour ces réponses.

[Gnatit]

Pour information, en créant une nouvelle table copie de la première mais en mettant à jour les colonnes, je suis passé de 15h à 12 minutes. Je ne pensais pas qu'Oracle s'empêtrait autant avec les updates

[mnitu]

Faites une trace étendue de votre requête en 11g pour apprendre ce qui se passe.

[ojo77]

Envoyé par mnitu

Faites une trace étendue de votre requête en 11g pour apprendre ce qui se passe.

Oracle met à jour l(es) index à chaque foi qu'une ligne est mise à jour sauf dans le cas où l'update utilise un filtre sur une colonne indexée et qu'il utilise l'index.

[ORA-007]

Bonjour,

Quelle est la volumétrie de la table concernée ?
Cette dernière était de combien avant migration ?

Ce temps de 15 h est il systématique ?

y a-t-il des traitements en parallèle de votre Update (transactions / batch ) ? présence de Locks ?

cordialement.

[Gnatit]

La table fait 2,5 millions de lignes, autour de 2Go. Elle a été créée après migration comme elle est recréée toutes les semaines. Les performances pour la créer était cependant meilleur avant migration. Le temps de 15h est systématique et il n'y a pas d'autres traitements en parallèles.

Mais la solution de créer une autre table est finalement plus que satisfaisante puisque les perfs sont bien meilleures que ce que j'avais avant sur oracle 10g. Même si bien sur il y a la frustration de ne pas avoir compris

Merci à tous.

[Waldar]

Je rebondis sur votre sujet car j'ai quand même quelques choses qui me chiffonnent, à mon avis c'est sur la conception que vous avez un problème :

Envoyé par Gnatit

Je mets à jour 25 colonnes sur 260, de tout type (des objets géométriques, des varchars, des integer). Cependant, les colonnes ne sont pas remplies avant la mise à jour et sont toutes à null car je viens de les créer. Je ne peux pas les remplir avant malheureusement.

Dans ce cas, pourquoi tout simplement votre table principale ne ferait pas 235 colonnes (ce qui est déjà très gros), et vous créeriez une seconde table avec les 25 colonnes calculées après coup (plus la clef primaire) ?
Une simple vue relie vos deux tables, et vous n'y voyez plus du feu.

D'ailleurs votre update, quel genre de calcul fait-il ? Il va dans d'autres tables où il utilise les données présentes dans les autres colonnes ?

[Gnatit]

Je comprends bien que a vous chiffonne, la conception n'est pas de moi et je suis encore débutant on va dire. La solution des 25 colonnes dans une autre table a été envisagée. Mais pour correspondre au modèle d'une autre table, elles ont été mises dans la même table. Je n'avais pas pensé à la vue, ca règlerait le problème.

Les calcul sont faits à partir d'autres colonnes de la même table (et de la même ligne). Le but est d'accélérer des recherches sur la table, il y a donc des concaténations de colonnes pour des recherches de textes, des conversions de valeurs numériques, des comparaisons de valeurs, des case when pour déduire des valeurs booléennes, des créations d'objets géométriques.

Ces calculs ne peuvent se faire avant au remplissage de la table car il y a des conditions dépendant de la globalité de la table (présence de doublons sur certaines colonnes), conditions précalculées par un update préalable pour que tout puisse se faire ligne par ligne.

En dehors de la mauvaise conception, mon problème a surtout été ces deux questions:
- pourquoi faire l'update en plusieurs fois est plus efficace alors que toutes les lignes sont systématiquement parcourus
- pourquoi cette différence entre Oracle 10g et 11g

La solution de la recréation de la table me permet de corriger au plus vite, et je ne sais si j'aurai le temps de refaire des tests pour expliquer les 15h, surtout que bloquer la machine 15h c'est long!

Encore merci.

[Waldar]

Envoyé par Gnatit

Ces calculs ne peuvent se faire avant au remplissage de la table car il y a des conditions dépendant de la globalité de la table (présence de doublons sur certaines colonnes), conditions précalculées par un update préalable pour que tout puisse se faire ligne par ligne.

Ok, c'est une information intéressante.

Vous pourriez aussi mettre ces calculs dans une vue, ou avec Oracle 11g directement dans des colonnes virtuelles.

Dans la vue le calcul est effectué au moment de la sélection, dans le cas des colonnes virtuelles le calcul est effectué au moment de la mise à jour des autres colonnes.

Il faudrait voir si tout simplement vous pouvez supprimer la phase de mise à jour avec l'une de ces deux méthodes.

Un petit exemple :

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
CREATE TABLE tbl
(
    c1   number(1),
    c2   number(2),
    c3   AS (c1 * c2)
);
-- Table created.
 
INSERT INTO tbl (c1, c2) VALUES (2, 4);
-- 1 row created.
 
SELECT c1, c2, c3 FROM tbl;
 
        C1         C2         C3
---------- ---------- ----------
         2          4          8
-- 1 row selected.
 
UPDATE tbl SET c1 = 9;
-- 1 row updated.
 
SELECT c1, c2, c3 FROM tbl;
 
        C1         C2         C3
---------- ---------- ----------
         9          4         36
-- 1 row selected.
 
DROP TABLE tbl;
-- Table dropped.

Aussi dès lors que vous avez fini vos mises à jour sur vos autres colonnes, vos vingt-cinq dernières sont déjà renseignées.
Reste à voir le coût, pour ça je n'en ai pas la moindre idée, mais ça peut être une piste, même si j'ai bien compris que les 12 minutes de la recréation de votre table vous sont satisfaisantes.

Une précision, les colonnes virtuelles peuvent être indexées !

[Gnatit]

Les colonnes virtuelles sont une solutions très intéressantes. Nous avions déjà essayé avec une vue mais les performances étaient trop mauvaises lors de l'accès à la table.

Par contre, étant donné que l'on ajoute toutes les lignes au début (plus d'ajouts par la suite), qu'on fait les updates et qu'il manque des informations tant que toute la table n'est pas rempli, je ne peux faire l'ajout de colonnes virtuelles qu'à la fin. Dans ce cas, leur calcul reviendrait à un update final je pense comme je faisais actuellement. Mais pour plus tard c'est bien noté!