Optimisation pour traitement type data warehouse

Bonjour à tous,

Je cherche à optimiser mon traitement hebdomadaire qui traite de très gros volumes de datas. En gros, il se déroule de la manière suivante :
1. Récupération des données depuis une réplication d'un environnement de production ;
2. Création de nos tables de work (nos tables sur lesquelles s'appuient mon traitement) ;
3. Traitement ;

Mon but serait d'optimiser au maximum les tables lors de l'étape 2, afin de gagner du temps dans l'étape 3.

Les tables créées lors de cette étape seront lues lors de l'étape 3, mais aucune n'est amenée à être modifiée (aucun DELETE / INSERT / UPDATE).

Je pensais faire la chose suivante lorsque je crée mes tables :

- mettre PCT free à 0 : permet de gagner en espace disque et de gagner un peu en perf ;
- mettre en nocompress : pour ne pas perdre en perf lors de la décompression, et pas besoin spécialement de gagner en espace disque ;
- mettre en nologging : pas besoin de conserver de traces spéciales ;
- (mettre les tables en read-only à la fin des insertions ?) : peut éventuellement permettre de gagner en perf ;

Lors des insertions, actuellement nous avons paralléliser les INSERT avec un HINT, mais j'ai lu qu'il pourrait être préférable de laisser sans parallélisme afin d'avoir des fichiers propres.

N'étant pas spécialiste oracle, j'aurais aimé avoir des avis sur les différents points ci-dessus, et savoir si ces affirmations sont plutôt justes ou non, et également qu'est ce que je peux mettre d'autre en place pour améliorer mes performances et optimiser mes tables.

Les plus volumineuses sont également partitionnées + sous partitionnées, on crée également les index et ont fait les stats à 20% à la fin de chaque création de table.

Si je prend la configuration d'une table actuellement sur notre environnement, ca ressemble à ceci :

Code:

---

1 2 3 4 5 6 7

SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "XXX"

---

Voila, merci d'avance pour toutes vos remarques !

Bonne journée

Citation:

---

Envoyé par Bouga74

- mettre PCT free à 0 : permet de gagner en espace disque et de gagner un peu en perf ;

---

Oui !

Citation:

---

Envoyé par Bouga74

- mettre en nocompress : pour ne pas perdre en perf lors de la décompression, et pas besoin spécialement de gagner en espace disque ;

---

Ça dépend. La compression troque du CPU contre des I/O.
Si vous avez du CPU disponible mais peu d'I/O (c'est souvent le cas), la compression a du sens.

Citation:

---

Envoyé par Bouga74

- mettre en nologging : pas besoin de conserver de traces spéciales

---

Oui !

Citation:

---

Envoyé par Bouga74

- (mettre les tables en read-only à la fin des insertions ?) : peut éventuellement permettre de gagner en perf ;

---

Je ne pense pas que ça change les performances, mais c'est quelque chose que je n'ai pas testé.

Citation:

---

Envoyé par Bouga74

Lors des insertions, actuellement nous avons paralléliser les INSERT avec un HINT, mais j'ai lu qu'il pourrait être préférable de laisser sans parallélisme afin d'avoir des fichiers propres.

---

Vous pouvez laisser le parallélisme, mais surtout faire vos insert avec le hint APPEND.

Citation:

---

Envoyé par Bouga74

Les plus volumineuses sont également partitionnées + sous partitionnées, on crée également les index et ont fait les stats à 20% à la fin de chaque création de table.

---

Le partitionnement a un coût à l'intégration mais peut offrir grandement en restitution. Vérifier que vos critères de partitions et sous-partitions sont bien utilisés systématiquement ou quasi-systématiquement dans la suite des traitements.

Super, merci beaucoup pour la réponse détaillée !

Bon déjà ca me rassure, je ne pars pas totalement dans le mauvais sens.

Citation:

---

Ça dépend. La compression troque du CPU contre des I/O.
Si vous avez du CPU disponible mais peu d'I/O (c'est souvent le cas), la compression a du sens.

---

Effectivement, on a pas mal de CPU a dispo et les I/O peuvent être un plus gros problème, je vais surement reconsidérer la compression alors ! Je pensais que cela péjorerais les perf, juste pour nous faire gagner de l'espace disque (qui pour le moment nous convient) ;

Citation:

---

Je ne pense pas que ça change les performances, mais c'est quelque chose que je n'ai pas testé.

---

J'ai lu ça quelque part, je n'ai pas testé et je l'ai juste gardé en coin de tête au cas ou, je testerai à l'occase.

Citation:

---

Vous pouvez laisser le parallélisme, mais surtout faire vos insert avec le hint APPEND.

---

En mettant le HINT

Code:

---

/*+PARALLEL (matable, 4)*/

---

,
je dois également rajouter le APPEND ou il y est d'office ?

Citation:

---

Le partitionnement a un coût à l'intégration mais peut offrir grandement en restitution. Vérifier que vos critères de partitions et sous-partitions sont bien utilisés systématiquement ou quasi-systématiquement dans la suite des traitements.

---

Nous traitons sur 3ans en général, et les datas sont assez bien réparties donc ca devrait aller.



Encore merci pour toutes ces précisions !

Pour le hint, il faut l'ajouter :

Code:

---

/*+ APPEND PARALLEL (matable, 4)*/

---

À noter qu'en fonction de la version d'Oracle, ce hint doit être placé au niveau de l'insert.

Dans le plan d'exécution vous verrez apparaître LOAD AS SELECT.

Il y a toutefois quelques restrictions : il ne faut pas de contraintes référentielles sur votre table, ni de déclencheurs et jusqu'à validation ou annulation de la transaction, la table est inutilisable.

Ok parfait merci bien, je vais tester tout ça !

Bonjour,

Tout ça m'a l'air tout bon. Juste quelques remarques:

nologging et pctfree=0 c'est très bien

compress: sur ce type de traitement le fait de faire beaucoup moins d'i/o compense largement le surplus de CPU.

read only: pas d'impact

stats à 20%: en 11g auto_sample_size est recommandé (stats plus pertinentes en moins de temps)

parallel: attention, pour faire du parallel DML, il faut l'activer au niveau de la session:

Citation:

---

ALTER SESSION ENABLE PARALLEL DML;

---

ici comment vérifier dans le plan d'exécution.

Cordialement,
Franck.

Franck

Citation:

---

stats à 20%: en 11g auto_sample_size est recommandé (stats plus pertinentes en moins de temps)

---

A condition qu'il soit en approximate_ndv. Pour vérifier il fait ceci

Code:

---

1 2	SELECT DBMS_STATS.get_prefs('approximate_ndv') FROM dual;

---

Bouga74,

Comme ça, en naviguant dans le noir, je dirai que si vous pouvez opter pour des tables temporaires (Global temporary Table) l'insertion ira certainement plus vite que dans des tables ordinaires (heap table).

Si vous avez des traces d'un traitement batch, on pourrait peut être vous dire d'avantage

Hello,

Je dirai aussi pas de stats sur les tables de l'étape 2 mais du dynamic sampling.
J'ai parfois eu de bien mauvaises surprises avec une logique similaire.

Jko

Merci pour vos retours !

Citation:

---

Envoyé par pachot

compress: sur ce type de traitement le fait de faire beaucoup moins d'i/o compense largement le surplus de CPU.

read only: pas d'impact

stats à 20%: en 11g auto_sample_size est recommandé (stats plus pertinentes en moins de temps)

parallel: attention, pour faire du parallel DML, il faut l'activer au niveau de la session:
Citation:
ALTER SESSION ENABLE PARALLEL DML;
ici comment vérifier dans le plan d'exécution.

---

- Effectivement, suite au message de Waldar, j'ai reconsidéré la chose et activé le mode basic de compression.

- Nous allons passer en 11g d'ici le début d'année, actuellement nous somme toujours en 10, mais je garde cela de côté pour dans quelques semaines !

- J'avais effectivement mis le ALTER SESSION, quand je regarde mes sessions actives lorsque le traitement tourne j'ai bien mes 4 exécutions en parallèles.

Citation:

---

Envoyé par Mohamed.Houri

Comme ça, en naviguant dans le noir, je dirai que si vous pouvez opter pour des tables temporaires (Global temporary Table) l'insertion ira certainement plus vite que dans des tables ordinaires (heap table).

Si vous avez des traces d'un traitement batch, on pourrait peut être vous dire d'avantage

---

- Je pensais le faire pour des tables créée à l'intérieure du traitement, je ferais le test pour voir tout cela !

- Sinon je n'ai malheureusement pas beaucoup de droit sur ma base, je voulais activer les traces mais pas moyens de récupérer les fichiers sur le serveur de la base...

Citation:

---

Envoyé par jkofr

Je dirai aussi pas de stats sur les tables de l'étape 2 mais du dynamic sampling.

---

- Je regarde également ça, je ne connais pas tellement !

Merci à tous les 3 !

Une dernière question, lorsque (dans l'étape 1) on récupère les données de l'environnement répliqué de la production et qu'on les ramène sur notre propre environnement, on crée des vues matérialisés.

Ces vues sont en nocache, nologging, nocompress, et parallel(degree 4 instances default).

Est-ce que ca vaut également le coup de les compresser ?

Exemple de création d'une de nos vues :

Code:

---

1 2 3 4 5 6 7 8 9

create materialized view user.abcd tablespace u1 storage (buffer_pool default flash_cache default cell_flash_cache default) nocache nologging nocompress parallel ( degree 4 instances default ) build immediate never refresh as select * from user.abcd@RPQLPROD where rownum > 0;

---

Citation:

---

Envoyé par Bouga74

Ces vues sont en nocache, nologging, nocompress, et parallel(degree 4 instances default).

---

Oui probablement, pour diminuer les i/o.

Ok parfait merci !