Performance OCI vs SQL Loader

Version imprimable

17/09/2008, 09h34
falcon74

Performance OCI vs SQL Loader

Bonjour,

J'ai besoin dans mon travail de regler des problèmes de performance d'injection Oracle pour l'un de nos produits.

J'ai vu que l'api OCI permettait d'injecter en mode bulk et je suis en train de me demander si les performances de ce mode seront comparable à SQL Loader ou non ?

En gros, est-ce que je peux integrer l'injection de masse dans mon programme ou est-ce qu'il vaut mieux faire des appels à SQL Loader ?

Merci d'avance.

Falcon
17/09/2008, 20h47
Vincent Rogier

salut,

SQL*Loader est codé avec OCI et peut être utilisé en mode DIRECT PATH qui comme son nom l'indique utilise l'API direct path de OCI.

SQL*Loader est un très outil éprouvé.

Tu pourra peut être faire plus optimisé en codant ton appli pour tes besoins mais je doute que tu gagnes beaucoup en perf et tu perdras du temps !....

A moins que tu ne tunes ta connexion en jouant sur les paramètre mémoire de ta session avec des alter session.

Mon conseil est de prendre sql*loader et utiliser son mode direct path.
18/09/2008, 08h31
falcon74

SQL Loader est bien codé avec OCI, merci de la confirmation.
Je m'en doutais mais je voulais en être sur.

En fait je regarde OCI car SQL loader à quelques limitations au niveau de son fichier de contrôle qui nous oblige à faire au moins 8 fichiers de contrôle, et donc 8 passes, pour injecter toutes les lignes du fichier (qui fait au minimum 6 Go au passage).

Les limites sont de ne pas pouvoir faire des tests avec <, > ou que le fichier de contrôle ai une taille maximale.

Je sais que si j'utilise OCI cela va me prendre du temps, mais je dois de toute façon faire un programme pour d'autre prétraitement.

Au début je me disais que j'appellerai juste SQL Loader depuis mon programme, mais les limitations ne me le permettent pas.

Je pense donc que je vais regarder du coté d'OCILIB ;). Cela devrait me faciliter le travail.
18/09/2008, 09h02
Vincent Rogier

Effectivement OCILIB te facilitera le travail....

Généralement, le rapport de volume de code est de minimum de 4:1 en faveur de OCILIB ;) sans parler de la lecture et la la maintenance du code ou la, y pas photo.

PS : OCILIB v3.0.0 va sortir fin septembre... Grosse version majeure avec plein de nouveautés !
18/09/2008, 09h19
falcon74

C'est clair que ca va etre plus facile avec OCILIB, surtout que je vais pas etre le seul à développer le programme et que le C pour les petits nouveaux c'est pas leur tasse de thé, alors avec du pure OCI ... :aie:

C'est aussi une très bonne nouvelle la sortie de la nouvelle version, car pour avoir l'autorisation d'utiliser une lib opensource, il faut prouver au chef qu'elle est maintenue en cas de problème.

juste pour infos, la prochaine version d'ocilib qui sort début avril supporte désormais l'api direct path de sql*loader.

Exemple d'application :

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
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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
#include "ocilib.h"
 
#define SIZE_TAB  1000
#define SIZE_COL1 5
#define SIZE_COL2 10
#define SIZE_COL3 6
#define NUM_COLS  3
 
int main(void)
{
    OCI_Connection *cn;
    OCI_DirPath    *dp;
 
    char arrval1[SIZE_TAB][SIZE_COL1+1];
    char arrval2[SIZE_TAB][SIZE_COL2+1];
    char arrval3[SIZE_TAB][SIZE_COL3+1];
 
    int arrsize1[SIZE_TAB];
    int arrsize2[SIZE_TAB];
    int arrsize3[SIZE_TAB];
 
    int i;
 
    if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
        return EXIT_FAILURE;
 
    cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
    dp = OCI_DirPathCreate(cn, "USR", "test_dp", NULL, NUM_COLS, SIZE_TAB);
 
    /* optionnal attributes to set */
 
    OCI_DirPathSetBufferSize(dp, 128000);
    OCI_DirPathEnableCache(dp, TRUE);
    OCI_DirPathSetCacheSize(dp, 1000);
    OCI_DirPathSetNoLog(dp, TRUE);
    OCI_DirPathSetParallel(dp, TRUE);
 
    /* describe the target table */
 
    OCI_DirPathSetColumn(dp, 1, "VAL1", SIZE_COL1, NULL    , 0, 0, FALSE);
    OCI_DirPathSetColumn(dp, 2, "VAL2", SIZE_COL2, NULL    , 0, 0, FALSE);
    OCI_DirPathSetColumn(dp, 3, "VAL3", SIZE_COL3, "DDMMYY", 0, 0, FALSE);
 
    OCI_DirPathPrepare(dp);
 
    /******* method 1 : set each array entry  ******/
 
    for (i = 0; i < SIZE_TAB; i++)
    {
        /* fill test values */
 
        sprintf(arrval1[i], "%d", i);
        sprintf(arrval2[i], "val %05d", i);
        sprintf(arrval3[i], "130309");
 
        OCI_DirPathSetEntry(dp, i, 1, arrval1[i], -1, TRUE);
        OCI_DirPathSetEntry(dp, i, 2, arrval2[i], (int) strlen(arrval2[i]), TRUE);
        OCI_DirPathSetEntry(dp, i, 3, arrval3[i], (int) SIZE_COL3, TRUE);
    }
 
    /* load data to the server */
 
    OCI_DirPathConvert(dp);
    OCI_DirPathLoad(dp);
 
    /* reset path stream */
 
    OCI_DirPathReset(dp);
 
    /****** method 2 : pass arrays in one call ******/
 
    for (i = 0; i < SIZE_TAB; i++)
    {
       /* fill test values */
 
        sprintf(arrval1[i], "%d", i);
        sprintf(arrval2[i], "val %05d", i);
        sprintf(arrval3[i], "130309");
 
        /* setup sizes */
 
        arrsize1[i] = -1;
        arrsize2[i] = (int) strlen(arrval2[i]);
        arrsize3[i] = SIZE_COL3;
 
    }
 
    OCI_DirPathSetArray(dp, 1, arrval1, arrsize1);
    OCI_DirPathSetArray(dp, 2, arrval2, arrsize2);
    OCI_DirPathSetArray(dp, 3, arrval3, arrsize3);
 
    /* load data to the server */
 
    OCI_DirPathConvert(dp);
    OCI_DirPathLoad(dp);
 
    /* commit data */
 
    OCI_DirPathFinish(dp);
 
    /* free direct path object */
 
    OCI_DirPathFree(dp);
 
    OCI_Cleanup();
}

J'ai enfin eux le temps de lire ton exemple, et effectivement si les perfs sont similaire au mode bulk, c'est très intéressant et bien plus dans la logique de ce que je dois faire, je vais essayer.

D'ailleurs je suis en train de galérer avec le mode bulk, j'ai un souci avec les dates :

Quand j'injecte plusieurs lignes à la fois, aucun problème les dates sont bien formatées à l'insertion.

Mais quand j'injecte une seule ligne, cas rare mais qui peux m'arriver,et bien la date est mal formatée.

J'ai fait un test depuis l'exemple de bulk du tutoriel :
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 int main() { OCI_Connection *cn; OCI_Statement *st; int tab_int[1000]; char tab_str[1000][21]; OCI_Date* tab_date[1000]; int i; if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT)) return EXIT_FAILURE; // ... create connection and statement ... cn = OCI_ConnectionCreate("XE", "ais", "ais", OCI_SESSION_DEFAULT); st = OCI_StatementCreate(cn); // binding OCI_Prepare(st, "insert into table_teste values(:i, :s, :d)"); OCI_BindArraySetSize(st, 1); OCI_BindArrayOfInts(st, ":i", (int*) tab_int, 0); OCI_BindArrayOfStrings(st, ":s", (char*) tab_str, 20, 0); OCI_BindArrayOfDates(st, ":d", (OCI_Date**) tab_date, 0); // filling arrays for(i = 0; i < 1 ; i++) { tab_int[i] = i+1; sprintf(tab_str[i],"Name %d",i+1); tab_date[i] = OCI_DateCreate(cn); OCI_DateSysDate(tab_date[i]); } // execute OCI_Execute(st); printf("row processed : %d\n", OCI_GetAffectedRows(st)); OCI_Commit(cn); OCI_Cleanup(); return EXIT_SUCCESS; }

17/04/2009, 22h27
Vincent Rogier

salut,

effectivement, dans le cas de liaison de tableau, si le nombre d'éléments du tableau était = 1, la valeur pouvait être mal insérée dans la base...

Je viens de la corriger et cela sera inclus dans la 3.2.0 qui sort début de semaine prochaine.

voici l'entrée du changelog concernant ce bug :

Citation:

- Fixed Array binding with array size = 1 : for non scalar types binds, corrupted data could be inserted or an error could be raised

:king: et :merci: pour la remontée du bug ;)
23/04/2009, 09h21
falcon74

J'ai testé l'injection d'une ligne avec la 3.2.0, ca marche sans problème. :king:

Pour le mode direct path, j'essaye et je te fais un retour sur mes impressions d'utilisation.
23/04/2009, 09h51
Vincent Rogier

Citation:

Envoyé par falcon74

Pour le mode direct path, j'essaye et je te fais un retour sur mes impressions d'utilisation.

cool :)
24/04/2009, 13h23
Vincent Rogier

des nouvelles de tes tests ?
24/04/2009, 13h43
falcon74

J'ai pas encore eu le temps de tester.

J'ai d'autres fonctionnalités récalcitrante du prog à finir avant de pouvoir remplacer le mode bulk par le direct path. :bug:

J'espere pouvoir faire des tests d'ici à Lundi.
27/04/2009, 16h57
falcon74

Je suis en train d'implémenter le mode direct path et j'ai un souci avec la fonction OCI_DirPathSetArray.

Je ne trouve pas sa description dans l'aide en ligne de ton site et elle n'a pas l'air d'etre connu dans la lib :koi:

Aurais-tu une idée ?
27/04/2009, 17h01
Vincent Rogier

cette fonction, prévue dans l'implémentation initiale, a été retirée de la version finale car ne servant a rien (car ocilib gère déja un tableau interne à cause des bugs oci en utf16...).

Il faut utiliser OCI_DirPathSetEntry()
27/04/2009, 17h30
falcon74

Ok, il faut donc que j'adapte un petit peu plus mon prog.

Avec le mode bulk, j'avais defini mes classes pour qu'elle me génère directement les tableaux de valeurs.

Donc ca va prendre un chtit peu plus de temps avant de pouvoir faire un test :aie:
27/04/2009, 17h34
Vincent Rogier

tu peux garder les tableaux.

fais juste un loop à la fin...
27/04/2009, 17h43
falcon74

oui, c'est une idée quand on réfléchit un peu :marteau:

Il est temps que la journée se finisse ....
30/04/2009, 10h02
Vincent Rogier

Des nouvelles en cette veille de 1er mai ???
30/04/2009, 10h10
falcon74

Je suis dessus, j'ai du bosser sur d'autres trucs ces derniers jours, mais j'ai réussi à finir le portage depuis le mode bulk et en ce moment je me bats avec un segfault dans mon propre code, mais je pense qu'il va pas me bloquer trop longtemps :aie:

Donc si tout va bien, test aujourd'hui :ccool:
30/04/2009, 11h56
Vincent Rogier

good :king:
30/04/2009, 17h34
falcon74

J'ai fait mes tests de performance, voici les résultats du mode DirectPath et les résultats du mode bulk.

Mon programme lit un fichier depuis un ou plusieurs threads en //.
Chacun reformate chaque ligne, puis la passe à un injecteur qui toutes les 10 000 lignes lance l'injection du groupe dans Oracle.

Le test a été fait sur un fichier de 2 millions de lignes.

La machine est un portable Dell avec un cpu pas trop mauvais, mais un HDD a chier, donc des résultats de test de perf pas forcément logique lors de l'augmentation du nombre de lecteurs.

Nb Lecteurs | Mode | Durée de traitement
1 | DP | 116 s
2 | DP | 150 s
4 | DP | 144 s
1 | Bulk | 60 s
2 | Bulk | 64 s
4 | Bulk | 56 s

Le mode directPath est plus lent que le mode bulk sur ma machine, cela m'étonne et je pense qu'il faut que je fasse des réglages car les 2 devraient être plus proche que cela, même si dans l'absolu je suis quand même à environs 15 000 lignes par seconde en mode DirectPath.

En gros des perfs pas dégeu, mais l'écart avec le mode bulk m'étonne.

Sinon d'un point de vu utilisation des fonctions DirectPath de la lib :

- L'obligation de faire la conversion de tous les types en char* avant l'appel de la fonction OCI_DirPathSetEntry, me donne une sensation de lourdeur dans son usage par rapport aux mode bulk qui prend directement les tableaux dans le type du champs. Enfin ca reste une sensation, le nombre de ligne suplémentaire est limité.

- Le fait que la fonction OCI_DirPathSetEntry necessite un void* et pas un const char* pour la valeur est imposé par OCI, ou bien il est possible de passer autre chose qu'un char* ?

- Pareil pour le paramètre name de OCI_DirPathSetColumn qui est de type char* et pas const char* ?

- Les index et les numéros de colonne qui commence à 1 au lieux de 0, c'est pas top top, c'est un fonctionnement un peu contre nature en c/c++ ;)

Voila pour mes première remarques.

Je pourrais t'en faire d'autre la semaine prochaine après un peu plus d'utilisation de la lib.
30/04/2009, 17h45
Vincent Rogier

il faut savoir que l'api direct path a été crée pour charger des flux textes externes (des fichiers quoi !). Donc, cela n'est pas vraiment adpaté si les données sont internes au programme et déja sous forme de données non texte...

Les index commence à 1 pour respecter la logique des OCI_GetXXX() des resultset afin que tout index démarre à partir de la même valeur dans toute la lib

Pour OCI_DirPathSetColumn(), l'absence du const est un oubli qui sera corrigé en 3.3 (mais c'est un détail)

Pour le OCI_DirPathSetEntry(), c'est un void * et non par un char * afin de pouvoir passer des contenus binaires (BLOB, RAW,...) depuis des lectures binaires depuis des fichiers non textes

Pour les perfs, je pense que les conversions de données que tu fais sont responsables de la lenteur. Il serait intéressant de mesure les temps de ces conversions afin de le retrancher au temps total
30/04/2009, 17h55
falcon74

OK, donc mon fichier a lire reformater plus injecté n'est pas forcement la meilleur utilisation du mode DirectPath.

Pour les index, je m'en doutais mais je n'ai résisté à l'envie de faire la remarque :mouarf:

L'absence du const pose un problème quand on lit le prototype de la fonction, je me suis demandé pourquoi elle pouvait modifier les données pointées. C'est un détail, mais je suis tatillons sur celui la apres de mauvaises exépriences. :scarymov:

Pour m'assurer de qui me ralentit, je ferais un test d'injection pure en DirectPath sans conversion de mes valeurs en passant des constantes à leur place. Comme ca, plus de doute ! :fou:
01/05/2009, 01h29
Vincent Rogier

1 pièce(s) jointe(s)

Salut,

j'ai fait un petit prog de bench direct path/bulk array...

Le code du prog est en pièce jointe...

Concrètement on bosse sur la table suivante :

Code:

create table test_directpath(val_int number, val_str varchar2(30));

Le programme initialise des tableaux de 2000 éléments (chaines de caractères pour les deux colonnes).

Pour chaque méthode, on fait 1000 insertions de ces tableaux, ce qui revient à insérer 2 fois 2 millions de lignes et donc au total 4 millions..

Le résultat est le suivant :

Citation:

*******TEST 1 (MODE DIRECT PATH) *******************
2000000 row(s) loaded

*******TEST 2 (MODE BULK ARRAY) *******************
2000000 row(s) loaded

****************** RESULTS ***********************
TOTAL TIME DIRECT PATH : 06785 (set =00273, load=06509, commit=00003)
TOTAL TIME BULK ARRAY : 24136 (exec=24060, commit=00076)

******************** END ************************

Le mode direct path est beaucoup plus rapide (selon mon jeu de tests 4 à 20 fois plus rapide...). En fait plus le nombre de lignes est grand, plus l'écart se creuse...

Petite précision qui explique aussi les résultats de tes tests différents des miens. Le mode direct path est toujours plus rapide en terme de set+load que le temps d'une exécution en mode bulk.
Par contre la première itération d'insertion donne l'avantage au mode bulk... Mais le temps mis par le mode bulk est le même pour chaque itération (sans compter que le temps du commit et exponentiellement lié au nombre de lignes total) alors que le temps par itération du mode direct path diminue et que son temps de commit est constant, quelque que soit le nombre d'enregistrement.

Donc, le mode direct path est d'autant plus intéressant qu'il y a d'itération d'insertion, donc plus intéressant sur des gros volumes.
04/05/2009, 16h12
falcon74

Hello,

J'ai fait un test avec ton code, et j'ai le meme ratio en résultat entre bulk et direct path.

Par contre dans mon programme, apres quelques modif par rapport à ton exemple (j'arrete de faire un finish et free toutes les 1000 lignes .... ) j'arrive à une égalité entre direct path et bluk.

En fait, le gain du direct path est annulé par une opération supplémentaire que je suis obligé de faire avec mon modèle de donnée par rapport au mode bulk.

Donc quand j'aurai adapté mon modèle, voir même quand je l'aurai changé pour le rendre peu couteux à manipuler pour les 2 modes, le mode direct path sera plus rapide.

Merci de ton aide pour comprendre comment tout ca marche :king: