[PROC] Optimisation d'insert massif en "Bulk Load"

[Jacker0r]

Bonjour,

Je travaille actuellement à l’optimisation d’un job de chargement dont les performances sont mauvaises.
Le job en question se résume à charger massivement des données dans une table Oracle (9i) après l’avoir tronquée au préalable :

• Table partitionnée
• 1 million de lignes à charger environ par partition
• 144 colonnes
• Une 10ène d’index
• Sas 9.1 – 3.4

J’ai mis en place un chargement de type « bulk » pour vérifier si je pouvais gagner en perf :

Code :

1
2
3
4
5
6
7
8
Proc append base=monDBMS.maTable (
	BULKLOAD=YES
	BL_RECOVERABLE=NO
	BL_DELETE_DATA_FILE=Yes
	BL_OPTIONS=’DIRECT=TRUE’
	OR_PARTITION=mapartition
	BL_LOAD_METHOD=TRUNCATE
) data=&SYSLAST force ;

A noter que je ne précise pas les options ‘bindsize’ et ‘rows’ a priori pas utilisées en direct path, et l’option parallèle non utilisée en Truncate mode.

Après quelques tests je constate que les performances sont moins bonnes avec le bulk. Un extrait de perf :

Bulk load
Cpu time : 50,4 seconds
Total time : 797 secondes

Chargement classique (Plugin de chargement Sas de base)
CPU time : 134,78 secondes
Total time = 566

J’ai effectué plusieurs tests dans différentes conditions :

• En faisant varier le nombre de colonnes de la table cible (De 10 à 120);
• En faisant varier le nombre de lignes à charger (De 100 à 10 millions);
• En parallélisant plusieurs traitements sur des partitions différentes;
• En retirant tous les index…

Systématiquement les performances sont largement moins bonnes en Bulk Loader malgré les gains annoncés sur les sites de référence (Burleson et autre).
Je constate que le temps total est moins bon (30% en moyenne) en bulk, mais que le temps CPU est toujours meilleur (+50 à 70% de gain).

Du coup je m’interroge

• Y’a-t-il des conditions particulières où le bulk insert serait moins performant qu’un insert transactionnel classique ?
• Peut être ai-je oublié une option importante qui fait la différence ?
• Le problème ne serait-t-il pas Hardware dans mon cas ?

J’ai le sentiment que le problème se pose à la création des fichiers CTL et DAT qui prendraient trop de temps.

Merci pour vos éclaircissements.

[bahraoui]

Bonjour,

D'après ce document le bulk http://www2.sas.com/proceedings/sugi29/106-29.pdf la Proc append avec BULKLOAD=YES est la meilleur solution!!!
Je te propose de faire des "commit" tout les x insertions, je pense qu'il faut renseigner le commit dans cette option BL_OPTIONS http://www.nesug.org/proceedings/nesug03/at/at003.pdf.
Sinon utilise du SQL LOADER directement pour voir les temps de chargement (peut être qu'il y a des problèmes sur la base)...

Cordialement

[Jacker0r]

Oui, j'ai utilisé ce doc pour mon implémentation.

Par contre, il me semblait que l'option 'rows' n'était pas utilisée en direct=true. Je vais refaire quelques tests en agissant dessus.

Use ROWS=n . For conventional data loads only, rows specifies the number of rows per commit and is related to bindsize. Issuing fewer commits will enhance performance, and the larger rows parameter affects performance (see benchmark below).

Je vais également effectuer des tests sur un autre serveur pour exclure la question du hardware sans passer par Sas.

Existe t il un moyen d'obtenir plus de détails dans les logs du sql*loader ?
J'aimerais pouvoir décolérer le temps lié à la création des fichiers, et le temps lié au chargement.

[s_a_m]

Bonjour;
y a aussi insertbuff=nombre de ligne a insérer simultanément.

[s_a_m]

je rajoute ;

Il existe un effet indésirable dans la gestion des erreurs,lié à l’utilisation de cette option. En effet, les erreurs seront affichées dans la log comme étant associées au ‘buffer’ plutôt qu’à la ligne qui a généré l’erreur. Les erreurs ne sont donc pas affichées au fur et à mesure qu’elles se produisent
mais par groupe.

[Jacker0r]

Envoyé par s_a_m

Bonjour;
y a aussi insertbuff=nombre de ligne a insérer simultanément.

Par contre ces options sont utiles pour les insert de type "Muliple rows insert" mais pas dans le cas du Bulk Load

[s_a_m]

es-tu obligé d'utilisé la proc append?
pk ne pas passer par la proc sql ( pass throuth) du moment tu charge dans un SGBD?

[bahraoui]

Envoyé par s_a_m

es-tu obligé d'utilisé la proc append?
pk ne pas passer par la proc sql ( pass throuth) du moment tu charge dans un SGBD?

c'est possible en sql pass through?

[Jacker0r]

Envoyé par s_a_m

es-tu obligé d'utilisé la proc append?
pk ne pas passer par la proc sql ( pass throuth) du moment tu charge dans un SGBD?

Effectivement, je ne suis pas spécialement obligé d'utiliser le PROC APPEND, je peux utiliser le PROC SQL. Je vais faire le test.

Mais le problème n'est pas vraiment là. L'un dans l'autre, la différence ne devrait pas être aussi grande. Le vrai problème pour moi est de comprendre pourquoi les perfs du Bulk sont aussi désastreuses. Je vous tiens informés.

[Jacker0r]

Je viens de faire des tests pour passer en "Proc SQL" et "Data Step", je me fais jeter avec une erreur :

Code :

1
2
3
4
ERROR: The ORACLE TABLE xxx has been opened FOR OUTPUT. This TABLE
already EXISTS, OR there IS a name conflict WITH an existing object. This
TABLE will NOT be replaced. This engine does NOT support the REPLACE
OPTION.

[s_a_m]

t'as regardé ici

toute fois, si tu peux poster ton code , sa nous permettra d'avoir plus de visibilité.

[Jacker0r]

Voila le code de mon plugin. Soyez indulgeant je débute
Donc en résumé j'utilise ici la méthode proc append.

Les options et paramètres sont les suivants :

• Table de destination v_nom_table
• Force load (YES/NO) v_force_load
• Partition de destination (Opt) v_nom_partition
• Tronquer avant chargement (YES/NO) v_load_method_trunc
• Supprimer les fichiers de données (YES/NO) v_del_data_file
• Oracle : User v_usr
• Oracle : Password v_pwd
• Oracle : Schéma v_dsn
• Répertoire des fichiers de données v_tmp_data_folder

La première partie du code sert simplement à gérer les options du bulk load en fonction de mes paramètres, et à générer des noms unique de fichier CTL/DAT/LOG.

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
%let v_nom_table_ora=%scan("&v_nom_table",2,".");
 
/* Connexion au DBMS */
libname libOra oracle user=&v_usr password=&v_pwd path=&v_dsn;
 
/* ============================================================ */
/* PREPARATION des options en fonction du mode utilisé
/* ============================================================ */
%let blmethod=x;
%let bloption=x;
%let blpartition=x;
%let forceload=x;
 
%MACRO PREPAREOPT;
 
	/* Gestion du type de load et des options */
	%IF &v_load_method_trunc=YES %then
		%DO;
			%let blmethod=TRUNCATE;
			%let bloption=%str(DIRECT=TRUE);
		%END;
	%else
		%DO;
			%let blmethod=APPEND;
			%let bloption=%str(DIRECT=TRUE,PARALLEL=TRUE);
		%END;
 
	/* Gestion de la partition */
	%IF &v_nom_partition eq %then
		%DO;
			%let blpartition=;
		%END;
	%else
		%DO;
			%let blpartition=OR_PARTITION=&v_nom_partition;
		%END;
 
	/* Force load */
	%IF &v_force_load=YES %then
		%DO;
			%let forceload=force;
		%END;
	%else
		%DO;
			%let forceload=;
		%END;
 
%MEND PREPAREOPT;
 
/* ============================================================ */
/* Preparation des fichiers de données
/* ============================================================ */
 
/* Génération d'une clé unique pour les fichiers temporaires */
%let vardatetime=%sysfunc(datetime());
%let uidkey=&sysjobid._%scan(&vardatetime,1)_%scan(&vardatetime,-1);
 
%let controlfile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..ctl);
%let datafile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..dat);
%let badfile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..bad);
%let logfile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..log);
 
%PREPAREOPT;
 
/* ============================================================ */
/* BULK LOAD
/* ============================================================ */
proc append base=libOra.&v_nom_table_ora (
			BULKLOAD=YES
			&blpartition
			BL_RECOVERABLE=NO
			BL_DELETE_DATAFILE=&v_del_data_file
			BL_OPTIONS="&&bloption"
			BL_LOAD_METHOD=&blmethod
			BL_CONTROL="&&controlfile"
			BL_DATAFILE="&&datafile"
			BL_BADFILE="&&badfile"
			BL_LOG="&&logfile"
)
DATA=&SYSLAST &forceload;

La méthode en data step qui me génère l'erreur plus haut est de la forme :

Code :

1
2
3
4
5
6
7
8
9
10
11
DATA libOra.&v_nom_table_ora (
			BULKLOAD=YES
			BL_RECOVERABLE=NO
			BL_LOAD_METHOD=&blmethod
			BL_DELETE_DATAFILE=&v_del_data_file
			BL_CONTROL="&&controlfile"
			BL_DATAFILE="&&datafile"
			BL_BADFILE="&&badfile"
			BL_LOG="&&logfile"
);
SET &SYSLAST;

Rien de transcendant.

[MEGAMIND2]

Envoyé par Jacker0r

Je viens de faire des tests pour passer en "Proc SQL" et "Data Step", je me fais jeter avec une erreur :

Code :

Soit ta table est ouverte lors de l'éxecution du code, soit tu es entrain d'écrire sur une table protégée en écriture.

[s_a_m]

as tu essayé sans le schema vers la base?

Code :

libname libOra oracle user=&v_usr password=&v_pwd /*path=&v_dsn*/;

[Jacker0r]

Envoyé par s_a_m

as tu essayé sans le schema vers la base?

Code :

Sans le path, imposible de se connecter au DMBS : Oracle connexion error, invalid Username/Password...

[Jacker0r]

Envoyé par MEGAMIND2

Soit ta table est ouverte lors de l'éxecution du code, soit tu es entrain d'écrire sur une table protégée en écriture.

J'ai pris soin de fermer toutes mes connexions, verifier que personne d'autre que moi travaille sur cette table, et j'ai les droits d'écriture.

[MEGAMIND2]

Envoyé par Jacker0r

J'ai pris soin de fermer toutes mes connexions, verifier que personne d'autre que moi travaille sur cette table, et j'ai les droits d'écriture.

J'aurais éssayé de t'aider

Bon courage!

[s_a_m]

Envoyé par Jacker0r

J'ai pris soin de fermer toutes mes connexions, verifier que personne d'autre que moi travaille sur cette table, et j'ai les droits d'écriture.

t'as raison, impossible sans le path , c'est le SCHEMA que je devais écrire.
mais tu ne l'as pas inclu.

[Jacker0r]

Le viens de refaire quelques tests.
J'ai un job des plus simples qui prend les données d'une table A pour les envoyer dans une table B Oracle. 1million de lignes sur 10 colonnes sans index.

Cas 1 : Utilisation du plugin chargeur de table sas classique:

• Real time sas = 14sec
• cpu time sas = 13,90 sec

Cas 2 : Utilisation du bulkload en direct true, sql append (Cf mon code plus haut)

• Real time sas = 24 sec
• cpu time sas = 6,18sec
  
• Elapse time SQL*loader= 18 sec
• Cpu time SQL*Loader= 10,82 sec
• J'en déduis que Sas se prend 6 secondes

Conclusion :
D'une part le bulk dans sas est moins rapide que le plugin sas de base.
Mais surtout, le sql*loader se la traîne en prennant à lui seul plus de temps qu'en prend sas en mode classique 18 secondes contre 14sec ...

Y'a un truc qui m'échappe !
En faisant des tests avec plus de données, 2M, 5M et 10 millions les résultats sont linéaires.



___________________________________________________
Pour ceux qui auraient la patience de faire le test
Et me prouver que mon serveur est en mousse
___________________________________________________

Scripts oracle
Scripts de création des tables A et B (FLM_TEST_1_TODELETE, et FLM_TEST_2_TODELETE

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
CREATE TABLE ALIM.FLM_TEST_1_TODELETE
(
  CLE      VARCHAR2(20 BYTE),
  VALEUR   VARCHAR2(20 BYTE),
  VALEUR1  VARCHAR2(20 BYTE),
  VALEUR2  VARCHAR2(20 BYTE),
  VALEUR3  VARCHAR2(20 BYTE),
  VALEUR4  VARCHAR2(20 BYTE),
  VALEUR5  VARCHAR2(20 BYTE),
  VALEUR6  VARCHAR2(20 BYTE),
  VALEUR7  VARCHAR2(20 BYTE),
  VALEUR8  VARCHAR2(20 BYTE)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

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
CREATE TABLE ALIM.FLM_TEST_2_TODELETE
(
  CLE      VARCHAR2(20 BYTE),
  VALEUR   VARCHAR2(20 BYTE),
  VALEUR1  VARCHAR2(20 BYTE),
  VALEUR2  VARCHAR2(20 BYTE),
  VALEUR3  VARCHAR2(20 BYTE),
  VALEUR4  VARCHAR2(20 BYTE),
  VALEUR5  VARCHAR2(20 BYTE),
  VALEUR6  VARCHAR2(20 BYTE),
  VALEUR7  VARCHAR2(20 BYTE),
  VALEUR8  VARCHAR2(20 BYTE)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;

Script d'insert d'1million de lignes dans la table A

Code :

1
2
3
4
5
6
7
8
9
10
11
declare
  nb_etape number;
  begin
    nb_etape:=1;
    while nb_etape <= 1000000
    loop
      execute immediate 'insert into flm_test_1_todelete (cle, valeur, valeur1, valeur2, valeur3, valeur4, valeur5, valeur6, valeur7, valeur8) values (' || nb_etape || ', ''cde'', ''cde1'', ''cde2'', ''cde3'', ''cde4'', ''cde5'', ''cde6'', ''cde7'', ''cde8'')';
      nb_etape:=nb_etape+1;
    end loop;
    commit;
  end;

Cas 1 : Job Insert Sas classique






Cas 2 : Job Insert Sas avec mon plugin bulk






Les options du plugin

Code :

1
2
3
4
5
6
7
8
9
TABLE de destination	                        v_nom_table
Force LOAD (YES/NO)	                                v_force_load
Partition de destination (Opt)	                        v_nom_partition
Tronquer avant chargement (YES/NO)	        v_load_method_trunc
Supprimer les fichiers de données (YES/NO)	v_del_data_file
Oracle : User	                                        v_usr
Oracle : Password	                                v_pwd
Oracle : Schéma	                                        v_dsn
Répertoire des fichiers de données	                v_tmp_data_folder

Le code de mon plugin

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
%let v_nom_table_ora=%scan("&v_nom_table",2,".");
 
/* Connexion au DBMS */
libname libOra oracle user=&v_usr password=&v_pwd path=&v_dsn;
 
/* ============================================================ */
/* PREPARATION des options en fonction du mode utilisé
/* ============================================================ */
%let blmethod=x;
%let bloption=x;
%let blpartition=x;
%let forceload=x;
 
%MACRO PREPAREOPT;
 
	/* Gestion du type de load et des options */
	%IF &v_load_method_trunc=YES %then
		%DO;
			%let blmethod=TRUNCATE;
			%let bloption=%str(DIRECT=TRUE);
		%END;
	%else
		%DO;
			%let blmethod=APPEND;
			%let bloption=%str(DIRECT=TRUE,PARALLEL=TRUE);
		%END;
 
	/* Gestion de la partition */
	%IF &v_nom_partition eq %then
		%DO;
			%let blpartition=;
		%END;
	%else
		%DO;
			%let blpartition=OR_PARTITION=&v_nom_partition;
		%END;
 
	/* Force load */
	%IF &v_force_load=YES %then
		%DO;
			%let forceload=force;
		%END;
	%else
		%DO;
			%let forceload=;
		%END;
 
%MEND PREPAREOPT;
 
/* ============================================================ */
/* Preparation des fichiers de données
/* ============================================================ */
 
/* Génération d'une clé unique pour les fichiers temporaires */
%let vardatetime=%sysfunc(datetime());
%let uidkey=&sysjobid._%scan(&vardatetime,1)_%scan(&vardatetime,-1);
 
%let controlfile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..ctl);
%let datafile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..dat);
%let badfile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..bad);
%let logfile=%str(&v_tmp_data_folder.&v_nom_table_ora._&uidkey..log);
 
%PREPAREOPT;
 
/* ============================================================ */
/* BULK LOAD
/* ============================================================ */
proc append base=libOra.&v_nom_table_ora (
			BULKLOAD=YES
			&blpartition
			BL_RECOVERABLE=NO
			BL_DELETE_DATAFILE=&v_del_data_file
			BL_OPTIONS="&&bloption"
			BL_LOAD_METHOD=&blmethod
			BL_CONTROL="&&controlfile"
			BL_DATAFILE="&&datafile"
			BL_BADFILE="&&badfile"
			BL_LOG="&&logfile"
)
DATA=&SYSLAST &forceload;

Il n'y a plus qu'a lancer les deux et faire un comparatif. Attention le Cas 1 ne trunc pas automatiquement la table de destination avant insert.

[s_a_m]

fallait poster dans la rubrique SAS BI.

c'est du DIS ? je me trompe ?

si c'était INFORMATICA, je t'aurai bien aidé .
assez performant.