Discussion :

[LEK]

Bonjour,
j'essaye de mettre en place le suivi d'un référentiel mutualisé de données de paramétrage pour plusieurs systèmes.
Dans mon application les administrateurs de chaque sous systèmes peuvent importer leur paramétrage dans ce que j'appelle une version : à partir de là ils peuvent éventuellement apporter une correction ce qui crée une nouvelle version de leur paramétrage : à chaque instant ils doivent pouvoir revenir sur une version antérieure ou connaitre l'historique des modifications d'une version.
Un super administrateur effectue alors une fusion des différentes versions de paramétrages, il peut aussi apporter des modifications et compléter la version produite avec des paramètres généraux s'appliquant à tous les sous-système.
Chaque version est consultable sur un site d'administration assez fréquenté...
Une fois qu'il est content de sa version, il peut la marquer comme "validée" : elle est alors prête à être exportée vers un datawarehouse.
Mes contraintes sont donc les suivantes :
- je dois gérer un système de versions de données que l'on peut corriger/amender : donc il y a un héritage des versions.
- j'ai une contrainte de dimentionnement car chaque version de paramétrage est relativement conséquente (100 Mb en moyenne, et 15 sous-systèmes soit au minimum 1,5Gb )
- je dois pouvoir importer rapidement une nouvelle version de paramétrage, en modifier une...
- je dois pouvoir exporter rapidement une version vers le datawarehouse.

Actuellement j'ai répondu au cahier des charges et mon système est en production mais je commence à rencontrer des problèmes de performances sur mes exports et consultations.
Pour gérer les versions, j'ai une table en auto jointure qui me permet de gérer les héritages de versions ainsi :

Code :

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

1
2
table : TP_VERSIONS
Columns : ID_VERSION, ID_PARENT_VERSION, DATE_CREATION, LIB_VERSION

J'y inscrit une ligne :
- sur chaque import.
- Pour chaque modification d'une version car cela crée une nouvelle version

Pour gérer mon paramétrage qui est fournis sous une forme clé/valeur j'ai 2 tables : une qui me permet de stocker les clés de paramétrages importées et dans quelles versions elles ont été ajoutées, modifiées,supprimées :

Code :

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

1
2
3
4
5
6
 
table : TP_KEYS_VERSION
Columns : ID_KEY ( business key : chaque clé de paramétrage de chaque administrateur possède une valeur unique),
                HASH_KEY_VALUE  (je calcule un hash pour identifier le contenu d'une clé de paramétrage pour suivre les modifications ) ,
                ID_VERSION (version dans laquelle cette clé est utilisée)
                MAJ_TYPE (enum : 1:creation,2:modifcation,3:suppression)

Dans cette table je travaille au delta pour économiser de l'espace : c'est à dire que lorsque j'ai importé une version de paramètre pour la première fois :
- 1. je crée une nouvelle version dans TP_VERSIONS dont id_parent est nulle
- 2. j'insère toutes les clés et le hash de leur valeur avec ce n° de version dans la table TP_KEYS_VERSION => MAJ_TYPE vaut 1

Si je reçois une nouvelle version du même administrateur :
- 1. je crée une nouvelle version dans TP_VERSIONS dont id_parent vaut celle que j'ai utilisée au step précédent
- 2. j'insère toutes les nouvelles clés et le hash de leur valeur avec ce n° de version dans la table TP_KEYS_VERSION => MAJ_TYPE vaut 1
- 3. j'insère toutes les clés modifiées et le hash de leur valeur avec ce n° de version dans la table TP_KEYS_VERSION => MAJ_TYPE vaut 2
- 4. j'insère toutes les clés supprimées et le hash de leur ancienne valeur avec ce n° de version dans la table TP_KEYS_VERSION => MAJ_TYPE vaut 3

=> en règle générale je n'ai que quelques modifications donc à l'import je n'effectue que quelques insertions avec MAJ_TYPE à 2



Et ma dernière table est TP_KEYS_VALUES :

Code :

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

1
2
3
4
 
Columns : ID_KEY ( business key : chaque clé de paramétrage de chaque administrateur possède une valeur unique),
                HASH_KEY_VALUE  (je calcule un hash pour identifier le contenu d'une clé de paramétrage pour suivre les modifications ) ,
               VALUE : valeur de paramétrage encodée en json

Elle contient toutes les valeurs de clés que j'ai eu à importées.


En terme de stockage : ce modèle me convient.
Par contre, mon problème est le suivant :
- lorsque je dois requêter pour afficher par exemple tout le contenu de la version Vn qui hérite de Vn-1 héritant de Vn-2 héritant ..., je dois écrire :

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
 
select  kv.ID_KEY , kv.HASH_KEY_VALUE  , kv.VALUE  from
TP_KEYS_VALUES  kv, 
(
select  last_ver.ID_KEY ,last_ver.HASH_KEY_VALUE 
from 
  (
      select v.ID_KEY ,v.HASH_KEY_VALUE , v.ID_VERSION,
               MAX(ID_VERSION) over (partition BY ID_KEY) last_version_id    -- > ceci me permet dans le cas d'une clé dont la valeur a été modifiée de récupérer sa dernière occurrence dans l'héritage des versions
      from TP_KEYS_VERSION v 
      where v.ID_VERSION in (:V1,:V2,:V3,..,:Vn)
  ) last_ver
  where last_ver.last_version_id = last_ver.ID_VERSION
) vers
 where kv.ID_KEY=  vers.ID_KEY
    and kv.HASH_KEY_VALUE= vers.HASH_KEY_VALUE

=> le temps d'exécution de mes requêtes augmentent de plus en plus avec l'ajout de nouvelles versions.
=> je n'ai pas de plan d'exécution constant à cause du in (:v1,:v2...) vu que je ne maitrise pas le nombre de versions qui sont créées.
=> un export d'une version s'est fait dernièrement en plus de 13h !


Ce que j'ai essayer de faire :
- mettre en place un concept de snapshot pour contracter au bout de n versions la hiérarchie des versions et en avoir au max 5 dans la clause in
=> mais cela fait que mes tables ne cessent de grossir (on parle de 1 million de lignes dans la table TP_KEYS_VALUES et 35 000 lignes dans TP_KEYS_VERSION pour l'instant)
et les perfs ne sont toujours pas au rdv : parfois suite à la création d'une nouvelle version la première requête de selection jouée peut fausser les plans d'exécutions d'oracle car certaines versions ne bougent pas alors que d'autres ont un arbre de hiérarchie qui atteint déjà la 30 aine de versions antécédentes....
- j'ai aussi mis en place une purge afin d'éliminer autant que possible de versions mais pour l'instant peut de données peuvent réellement être purgées pour des raisons opérationelles...

=> j'ai commencé à regardé si un base nosql de type grah db pourrait répondre mieux à mon besoin.
mais la migration de données en prod sur une techno non maitrisée ne me rassure pas non plus...

Du coup, je sais que c'est un gros post,mais j'aimerais avoir :
- votre retour sur une modélisation alternative possible
- ou une idée sur comment récupérer toute une version en limitant au max le calcul sur l'arbre des versions précédentes ( je parle de la clause IN (:v1,:v2...)

Merci d'avoir lu mon post

[JeitEmgie]

Pourquoi ne pas utiliser un query récursif dans le IN au lieu d'une liste de valeurs puisque vos versions sont chainées ?

[skuatamad]

- lorsque je dois requêter pour afficher par exemple tout le contenu de la version Vn qui hérite de Vn-1 héritant de Vn-2 héritant ..., je dois écrire :

Je ne comprends pas bien ce commentaire, est ce que vous faites une boucle sur chaque sous version ?

Je suis surpris qu'il n'y ait pas de requête récursive présentée, à base de CONNECT BY ou de WITH recursif (et pas forcément que pour générer la liste des versions dans le IN).

[LEK]

Pourquoi ne pas utiliser un query récursif dans le IN au lieu d'une liste de valeurs puisque vos versions sont chainées ?

Oui trés bonne question :
je n'ai pas tout dit : la table TP_VERSIONS est en fait une partie de mon module générique de versionning et est présente dans un autre schéma : je l'utilise pour versionner plusieurs autres domaines métiers.
Au niveau de mon application : je l'interroge pour connaitre la hiérarchie des versions et ensuite je me sert des valeurs récupérées.
=> j'ai essayer de l'attaquer directement avec un query récursif (en utilisant un synonyme) mais les perfs n'étaient pas au rendez vous tout de même...

[LEK]

Je ne comprends pas bien ce commentaire, est ce que vous faites une boucle sur chaque sous version ?

Je suis surpris qu'il n'y ait pas de requête récursive présentée, à base de CONNECT BY ou de WITH recursif (et pas forcément que pour générer la liste des versions dans le IN).

Cela rejoint la remarque de JeitEmgie : selon vous la voie à suivre et de ne pas découpler la table des versions des données versionnées et tout récupérer en mode récursif plutôt que de récupérer d'abord les versions puis utiliser l'opérateur IN.
=> Comme indiqué j'ai fais le test, cela améliore légèrement mes perfs mais ce n'est pas le jour et la nuit.
=> les meilleures perfs que j'ai c'est lorsque je dénormalise tout, mais alors au niveau disque je stocke de manière exponentielle.

[JeitEmgie]

Quels sont vos index ?
Leur clustering factor ?

[LEK]

Je n'ai pas accès à la prod aujourd'hui (déplacement), du coup je donne les indications suivantes issus de l'environnement de validation (les données importées et traitées sont plus faibles mais les comportements observés sont les mêmes)

Voici donc les index et le rappel de la définition des tables impliquées :

Code :

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

1
2
3
4
5
6
 
table : TP_KEYS_VERSION
Columns : ID_KEY ( business key : chaque clé de paramétrage de chaque administrateur possède une valeur unique),
                HASH_KEY_VALUE  (je calcule un hash pour identifier le contenu d'une clé de paramétrage pour suivre les modifications ) ,
                ID_VERSION (version dans laquelle cette clé est utilisée)
                MAJ_TYPE (enum : 1:creation,2:modifcation,3:suppression)

Cette table est une table IOT.
Voici les index qu'elle porte :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
index : TP_KEYS_VERSION_PK UNIQUE (ID_KEY ,ID_VERSION )
Clustering factor : 0
Sample size : 887638
 
 
index : TP_KEYS_VERSION_INLIST NON UNIQUE (ID_VERSION, HASH_KEY_VALUE)	--> il devrait etre unique
Clustering factor : 239714
Sample size : 887638
 
index : TP_KEYS_VERSION_IDX NON UNIQUE (ID_VERSION,ID_KEY , HASH_KEY_VALUE)	
Clustering factor : 129018
Sample size : 887638

Pour la seconde table TP_KEYS_VALUES :

Code :

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

1
2
3
4
5
table : TP_KEYS_VALUES 
 
Columns : ID_KEY ( business key : chaque clé de paramétrage de chaque administrateur possède une valeur unique),
                HASH_KEY_VALUE  (je calcule un hash pour identifier le contenu d'une clé de paramétrage pour suivre les modifications ) ,
               VALUE : valeur de paramétrage encodée en json

Avec les index suivants :

Code :

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

1
2
3
4
5
6
7
8
index : TP_KEYS_VALUES_PK UNIQUE (HASH_KEY_VALUE )
Clustering factor : 31810
Sample size : 256125
 
 
index : TP_KEYS_VALUES_LIST NON UNIQUE (ID_KEY, HASH_KEY_VALUE )
Clustering factor : 59570
Sample size : 256125