Problème de performance lors de suppression/insertion

[cedrich]

Bonjour à tous,

Sous Oracle 10g (SE 10.2.0.1), EM nous indique quelles sont les requêtes qui consomment le plus de ressource.

Depuis quelques temps, je trouve régulièrement les mêmes objets responsables de lenteurs.

"EM" m'indique les éléments suivants :

Code :

1
2
3
4
5
6
7
8
 
Aktion  Investigate application logic involving I/O ON INDEX "IDINFO.SD_DWH_STAT_PREST_CASANNEE_IDX" WITH object id 369455.   
 Datenbankobjekt IDINFO.SD_DWH_STAT_PREST_CASANNEE_IDX 
 
Begründung  The I/O usage statistics FOR the object are: 0 full object scans, 23558 physical reads, 9449 physical writes AND 0 direct reads. 
Begründung  The SQL statement WITH SQL_ID "04maspp34qdtf" spent significant time waiting FOR User I/O ON the hot object. 
 SQL-Text BEGIN dbms_sqltune.execute_tuning_task(:1); END; 
SQL ID 04maspp34qdtf

N'ayant pas d'expérience dans ce domaine, je ne sais pas comment aborder la problématique. Après plusieurs recherches sans succès sur la toile, j'espère trouver la réponse sur ce forum!

La problématique n'est pas lié qu'à cet indexe, parfois la table ou d'autres indexes de cette table posent également des problèmes.

La table est constituée, actuellement, d'env. 7 millions de lignes. Chaque soir, on supprime env. 2 millions de lignes avant d'en insérer autant (Consolidation de données sur une année)!

J'ai pu lire que la suppression en masse peut générer de la segmentation. "EM" me propose bien d'effectuer un "shrink" pour récupérer la place perdue (si j'ai bien compris). Un "shrink" ne vas pas nécessairement résoudre mon problème si je l'effectue tous les soirs.

D'avance je remercie toutes personnes qui pourrait me donner une piste à explorer ou une méthodologie d'analyse pour déterminer où se situe le "hic".

Excellent week-end à tous,

Cédric

[Waldar]

Je vous conseille de supprimer votre index avant de supprimer / insérer vos données puis de le recréer après.

Vous allez gagner du temps.

[cedrich]

Bonjour Waldar,

J'avais également pensé à cela et après quelques recherches, j'avais trouvé une démonstration où la suppression des indexes et leur recréation était encore plus coûteuse que la suppression avec les indexes existant.

J'avais donc décidé de ne pas implémenter cette solution.

Sur vos conseils, j'ai modifié la procéudre en conséquence et je vérifierai demain le résultat.

Merci et je vous tiendrai au courant!

[cedrich]

Hello,

@Waldar : Merci pour ce conseil!

Le traitement est passé d'un peu plus de 13h à ~5h30! Le changement est impressionnant.

J'ai encore un problème de segmentation sur la table elle-même. Y aurait-il un moyen pour le solutionner?

Dois-je modifier les paramètres de création des segments automatique (pctfree, ...) où dois-je reconstruire la table de temps en temps?

D'avance merci pour vos conseils.

Salutation à tous!

[ojo77]

Bonjour,

si je devais régulièrement faire ce genre d'opération j'utiliserait une table partitionnée pour faire le travail.

Imaginons que la table sur laquelle vous travaillez s'appelle source.

Je commencerait par créer définitivement une table travail contenat une seule partition et de même structure que source.

En supposant que ID est la clé primaire de source:

Code :

1
2
3
CREATE TABLE travail AS (SELECT * FROM source WHERE 1=2)
partition BY range (ID)
(PARTITION P1 VALUES LESS THAN (MAXVALUE));

A partir de là je réécrirais mon batch en 3 étapes :

1°) Alimentation de la table de travail à partir des lignes de sources que l'on souhaite garder.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
-- truncate de la table de travail
TRUNCATE TABLE travail ;
 
-- suppression des contraintes de la table
ALTER TABLE travail disable constraint C1;
ALTER TABLE travail disable constraint C2;
...
ALTER TABLE travail disable constraint Cx;
 
-- suppression de tous les index de la table de travail 
DROP INDEX I1;
DROP INDEX I2;
...
DROP INDEX Ix;
 
INSERT /*+ APPEND */ INTO travail
SELECT * FROM source WHERE <conditionws de conservation des lignes> ;
commit;

2°) J'alimenterais la table de travail avec les 2 millions de lignes à ajouter de préférence en mode direct.

Code :

1
2
INSERT /*+ APPEND */ INTO travail <lignes à ajouter> ;
commit;

3°) J'effectuerais un échange de partitions avec la table source afin de l'alimenter avec un table à jour.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
-- recréation des index
CREATE INDEX I1 ON travail(champ1, champ2, ... champx) .... ;
CREATE INDEX I2 ON travail(champx2, champy2, ... champz2) .... ;
...
CREATE INDEX Ix ON travail(champxx, champyx, ... champzx) .... ;
 
-- recréation des contraintes
ALTER TABLE travail enable constraint C1;
ALTER TABLE travail enable constraint C2;
...
ALTER TABLE travail enable constraint Cx;
 
-- calcul des statistiques sur la table
exec dbms_stats.gather_table_stats (user, 'TRAVAIL', .... );
 
-- Echange des partitions
ALTER TABLE travail exchange partition P1 WITH TABLE source;
 
-- nettoyage de la table travail
TRUNCATE TABLE travail ;

ATTENTION : il est plus que préférable de tester ce genre d'opération en dehors des environnements de production d'une part, et d'autre part cette méthode ne fonctionne que si on est certain que la table source n'est pas mise à jour par un autre batch ni par un utilisateur pendant le traitement.

[Ahmed AANGOUR]

Il y'a plusieurs manières d'améliorer des DELETE+INSERT.
il faut les étudier et les tester car ils dépendent de votre requête et des problématiques de votre application.
Les solutions à étudier qui me viennent à l'esprit sont:
- le partitioning
- le parallélisme
- le DIRECT PATH INSERT (via le hint APPEND)
- l'insert en mode bulk

[Waldar]

En plus de ce qui a été dit, j'ajouterai que si la table ne subit jamais de mise à jour, vous pouvez positionner votre PCTFREE à 0.

En fait l'idée développée par ojo77, c'est que lorsqu'on atteint un certain volume de suppressions à effectuer, la meilleur solution c'est justement de ne pas en faire.

Une autre solution serait, si vous conservez une année glissante dans votre table, de regarder du côté du partitionnement (à la semaine, au mois, à étudier selon vos données) afin de faire des truncates partitions (très rapides) combinés aux insert append.
Avec des index locaux vous n'aurez pas à les recréer.

Vous pouvez également compresser votre table (en 10g cela se limite aux chargement direct load il me semble): le chargement sera peut-être un peu plus long (d'un petit facteur), mais au final vous utiliserez moins de blocs de données ce qui accélérera les lectures : comme il s'agit d'un datawarehouse ou datamart, c'est toujours une bonne chose.

[cedrich]

Bonjour à tous,

Un grand MERCI pour toutes ces réponses.

Mon unique question en rapport aux solutions que vous soumettez, est-ce qu'elles sont compatibles avec Oracle Standard Edition?

Il me semble, et j'espère me tromper, que le parallélisme et le "partionning" ne sont valable que pour Oracle Enterprise Edition, non?

Dans l'attente de votre confirmation ou infirmation, je testerai les solutions "DIRECT PATH INSERT" et l'insertion en mode "bulk".

Encore merci pour ce panel de solution.

Salutations à tous.

[ojo77]

En effet en SE pas de partitionning, on peut remplacer l'échande de partition par le renomage de tables mais c'est moins propre