erreur sql loader et performance [Résolu]

[mobisky]

Bonjour,

Le problème est le suivant: je tente de charger 17 000 000 de records à partir d'un fichier .dat dans une table qui a ni index, ni clé étrangère,...

J'ai modifié la taille de ma tablespace à 3000 mb pour être sûr
(nb: le fichier .dat fait 1 gb)

Arrivé à plus de la moitié de l'importation, j'ai une erreur:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C:\oracle\ora81\bin> sqlldr user/user control=temp.ctl rows=10000000 commit_discontinued=TRUE
.......
.......
Commit point reached - logical record count 13409062
Commit point reached - logical record count 13410025
Commit point reached - logical record count 13410988
SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

Commit point reached - logical record count 13411951

C:\oracle\ora81\bin>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

voivi la création de ma table :

'DROP TABLE CDR CASCADE CONSTRAINTS ;

CREATE TABLE CDR (
TIMESTAMP DATE,
DURATION NUMBER,
DESTINATION VARCHAR2 (255),
ORIGIN VARCHAR2 (255))
TABLESPACE CDR_TEMP
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 131072
NEXT 131072
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 4096 FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;

Si je regardes dans le fichier log:
MAX # EXTENTS (4096) reached in table schema.table

a quoi correspond t il exactement ?


Merci pour votre aide ... excusé ce pauvre débutant

[rouardg]

Bonjour,

Les paramètres de stockage de ta table ne sont pas en concordance avec le volume que tu as à traiter.

D'un côté, tu nous dis avoir un fichier de 1Go. De l'autre, tu créés une table avec entre autre :

INITIAL 131072
NEXT 131072
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 4096

En gros, pour faire tenir 1 Go de données dans des extents de 131072 octets, il va falloir qu'Oracle procède à la création de (1024 * 1024 * 1024) / 131072 = 8192 extents.

Or tu as limité ta table à 4096 extents. En gros, tu peux charger la moitié des données, et puis après ça plante.

Tu arrives à charger 13411951, soit 13411951 / 17000000 = 79 % des données. Pour aller de 79 % à 100 %, il y a un facteur de 1.27

J'aurais donc tendance à dire que INITIAL_EXTENT = 131072 * 4096 * 1.27 = 650 Mo

Essaye donc de créer ta table avec INITIAL = 650M

[rouardg]

Autre chose : si tu as 17 millions de lignes à charger, évite de commiter tous les 10 millions de lignes. Essaye plutôt de commiter tous les millions de lignes.

[mobisky]

Merci mon problème est à moitié résolu

Grâce à sqlloader j'ai donc uploadé mes 17 000 000 de records ....cette importation dure 16 minutes serait-il possible de réduire ce temps...

En fait je me pose cette question car avant cette table était déployée sous Access 2000 et il me fallait exactement 6 minutes

NB : j'ai spécifier le paramètre rows = 1 000 000 lors de l'exécution du sqlloader

merci encore pour toute votre aide

[rouardg]

Rebonjour,

Je ne suis pas spécialiste SQL*Loader, mais je vois 2 choses :
1 ) regarder, en même temps que le paramètre ROWS, le paramètre BINDSIZE. Tous les 2 servent à dimensionner la "bind array". En gros, il faut savoir que tu as utilisé SQL*Loader dans le mode de chargement conventionnel. Cela signifie que SQL*Loader se sert de commandes classiques du SQL. Par exemple, pour remplir ta table, SQL*Loader lit un certain nombre de données de ton fichier, les stocke dans la "bind array", et quand celle-ci est pleine, alors un INSERT est généré, et donc toutes les données de la "bind array" sont insérées en une fois. Ensuite un COMMIT est fait, et ce cycle recommence.

Ce qu'il faut bien comprendre ici, c'est que tout se passe comme dans du SQL standard, donc le cache de données, les Redo Logs et les Rollback Segments sont mis en jeu.


2 ) l'autre méthode, c'est d'utiliser le chargement direct, dans le cas où la vitesse de chargement des données est primordiale. En gros, au lieu de générer du SQL et de consommer des ressources (cache de données, RBS...), SQL*Loader ne passe pas par toutes ses étapes, et formate directement les blocs de données, et les écrit directement dans les datafiles.

Pour utiliser ce mode, il faut se servir du paramètre DIRECT.
Par contre, je sais qu'il y a certaines limitations dans ce mode. Mais comme toi tu n'as qu'une table sans contraintes, ni index, cela va passer.

Pour finir, je te raconte tout cela de tête. Si tu veux en savoir plus, downloade depuis le site Oracle le PDF qui s'intitule 'Utilities'. Cela parle des utilitaires d'Import, d'Export, et de SQL*Loader.

[rouardg]

Pour complément d'info à propos de la méthode 1, j'aurais du m'en apercevoir avant quand tu m'as dit :

Commit point reached - logical record count 13409062
Commit point reached - logical record count 13410025
Commit point reached - logical record count 13410988
SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.

Commit point reached - logical record count 13411951

Si tu regardes l'intervalle entre chaque COMMIT successif, tu t'aperçois que SQL*Loader a chargé 963 lignes à chaque fois.

En fait, on a beau spécifier ROWS=1000000, comme la "bind array" est beaucoup trop petite, les données sont insérées et commitées lorsqu'elle est pleine.

En tapant sqlldr, tu auras les paramètres par défaut, dont le BINDSIZE. Pour ma version d'Oracle, il vaut 256000. En supposant que tu as la même chose, j'en déduis que la longueur d'une ligne de ton fichier est en gros de 256000 / 963 = 266 caractères.

Je t'ai donc dit une bêtise tout à l'heure, car pour COMMITER tous les millions de ligne, il faudrait 266000000 octets, soit un buffer de 254 Mo.

Plus raisonnable serait peut être de commiter toutes les 100000 lignes, ce qui ferait quand même un buffer de 26600000 octets, soit 25 Mo.

Si tu as du temps, essaye les 2 méthodes (la première avec ROWS=100000 et BINDSIZE=26600000) et la deuxième avec DIRECT.

Je serais très curieux de voir dans les 2 cas combien de temps il te faut.

[mobisky]

pour le 1er j'ai essayé et j'ai gagné 5 minutes .. je sui passé donc à 11 min et 3 sec et pour ce qui est du direct pas moyen car j'ai une conversion en date dans mon fichier .ctl

merci de ton aide

[rouardg]

C'est bizarre ce que tu me dis à propos du direct.

J'ai d'ailleurs la doc Oracle sous les yeux, et il est justement précisé que les formats de date peuvent être fixés de 2 manières :

1 ) au moyen de la variable d'environnement

En Bourne ou en Korn shell :
NLS_DATE_FORMAT='YYYYMMDD'
export NLS_DATE_FORMAT

En C shell :
setenv NLS_DATE_FORMAT='YYYYMMDD'

2 ) dans le fichier de contrôle :

champDate DATE 'YYYYMMDD',

Quand tu as essayé avec l'option DIRECT, as-tu eu un message d'erreur ??? Si oui, lequel ???

[rouardg]

J'ai oublié de te dire une chose importante. En mode conventionnel, les conversions sont faites côté base de données et les paramètres NLS de la base sont mis à contribution.

En mode direct, la conversion est faite côté client, et c'est à ce moment là que te servent les variables d'environnement, ou le format spécifié dans le fichier de contrôle.

[mobisky]

C:\oracle\ora81\bin>sqlldr username/password control=temp.ctl direct=true

SQL*Loader: Release 8.1.7.0.0 - Production on Tue Aug 19 17:10:44 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL*Loader-417: SQL string (on column TIMESTAMP) not allowed in direct path.

Pour ce qui est de mon fichier .ctl :

LOAD DATA
INFILE 'DC_V57_1058_1088.txt'
INTO TABLE CDR
(TIMESTAMP POSITION(1:14) "to_date(:timestamp,'DD MM YYYY HH24:MI:SS')",
DURATION POSITION(16:27),
DESTINATION POSITION(29:48)"trim(:destination)",
ORIGIN POSITION(50:63) "trim(:origin)"
)

?

[rouardg]

Ok. Tu tombes sur l'erreur SQL*Loader-00417, car avec l'option DIRECT, tu n'as pas droit d'appeler les fonctions SQL, puisque justement cette option n'utilise pas le SQL, le chargement étant fait en direct.

Pour la date, tu dois pouvoir t'en sortir avec :

(TIMESTAMP POSITION(1:14) DATE 'DDMMYYYYHH24MISS',

PS : comme tu peux le remarquer, vu que ta date tient sur 14 caractères, j'ai supprimé du format les espaces et les 2 points.

Le pb, c'est que j'ai peur que tu butes ensuite sur l'appel de TRIM, car c'est aussi une fonction SQL. Tu pourrais essayer de t'en sortir en supprimant le TRIM dans ton fichier de contrôle, mais cela veut dire qu'après le chargement, il faudrait trimmer à nouveau au moyen d'une requête SQL. Enfin, fait toujours un essai.

Le plus navrant dans tout cela, c'est que ça aurait été beaucoup plus simple si tu avais un fichier avec délimiteur, et non pas au format fixe.

[mobisky]

voici un extrait de mon fichier .dat :

20030430225934;000000000008; 32495070478243488; 3216825923;
20030501121125;000000000241; 3249501216814180; 3216825923;
20030501121648;000000000239; 3249501216810589; 3216825923;
20030501182016;000000000060; 3249501216814180; 3216825923;
20030501154217;000000000010; 32495090496805023; 3216814474;
20030501154923;000000000012;:32495090496805023; 3216814474;

: ces espaces comment pourrais je les supprimer autre qu'avec le trim
vu que la longueur est variable :s

merci beaucoup pour votre aide

[rouardg]

Pas d'autre méthode que le TRIM pour le supprimer.

Par contre, je ne comprends pas ce que tu as fait.

C'est incohérent car d'un côté ton fichier de contrôle indique que les champs sont en position fixe, et de l'autre l'extrait de ton fichier .DAT m'indique que ce n'est pas un fichier en format fixe, mais un fichier en format variable, avec le ; comme délimiteur.

[mobisky]

En fait j'avais essayé un fichier de controle en testant sur les délimiteurs mais ca ne marchait pas et ensuite j'ai écrit avec position...:

voici donc ma nouvelle version :

LOAD DATA
INFILE 'DC_V57_1058_1088.txt'
INTO TABLE CDR
(TIMESTAMP POSITION(1:14) DATE 'DDMMYYYYHH24MISS',
DURATION POSITION(16:27),
DESTINATION POSITION(29:48)
ORIGIN POSITION(50:63)
)

.. je passes now à 2:05:52

et ensuite manuellement je fais mes trim...
mais leur exécution prend 30 min

[rouardg]

Tu me dis :

.. je passes now à 2:05:52

C'est en quelle unité ? C'est 2 heures 5 minutes ??? Ou bien 2 minutes 5 secondes et 52 centièmes ??? C'est en mode direct je suppose ???

Sinon, pour tes histoires de TRIM, moi je pense que ce n'est pas à toi de t'embêter avec cela, mais aux personnes qui te fournissent le fichier. Car j'aurais tendance à dire qu'un fichier propre, c'est un fichier qui ne contient pas d'espace quand il n'y a pas lieu d'être.

Mais peut-être que tu nepeux pas faire autrement.

Quand aux 30 minutes passées avec le TRIM manuel, ça ne peut être que long, car ta table fait 650 Mo.