1

Vous n'aurez aucun mal à vous en rendre compte en lisant la suite, mais j'annonce quand même la couleur : je suis une quiche en Oracle ^^

Pour alimenter un outil de reporting, j'ai besoin de réaliser des extractions régulières depuis une base Oracle, en prenant les objets qui répondent à certains critères (entre autres : qui ont été modifiés au cours des X derniers jours, X étant généralement égal à 3). L'instance Oracle se trouve sur un serveur en production sur lequel je ne suis même pas censé installer un script crontabé, donc mon but est de minimiser les traitements à ce niveau.

J'ai donc un .sh qui balance ma requête à Oracle (via sqlplus, le client en ligne de commande par défaut), récupère le résultat brut dans un fichier et l'envoie par SCP vers un serveur sur lequel je peux faire ce que je veux. Je cherche à obtenir un fichier dont chaque ligne est au format suivant :

champ1=valeur1;champ2=valeur2;champ3=valeur3;[...];

Du coup ma requête ressemble à ça :
CREATE OR REPLACE FUNCTION escape(val IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
	RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(val, '#', '#25'), '=', '#3D'), ';', '#3B'), CHR(13), '#0D'), CHR(10), '#0A');
END;

/

CREATE OR REPLACE FUNCTION getField(key IN VARCHAR2, val IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
	IF val IS NULL THEN
		RETURN '';
	ELSE
		RETURN escape (key) || '=' || escape (val) || ';';
	END IF;
END;

/

SELECT
	getField ('champ1', champ1) ||
	getField ('champ2', champ2) ||
	getField ('champ3', champ3) ||
	[...]
FROM
	matable
WHERE
	mescritères;

La fonction "escape" me permet d'échapper les caractères ";", "=" et les retours à la ligne puisqu'ils ont une signification spéciale dans mon format de sortie. Ça marche plutôt pas mal, tant que la requête ne remonte pas trop de résultats. Mais de temps en temps j'ai besoin de faire une extraction totale de la base, et ça coince au niveau de la mémoire ("ORA-01652: unable to extend temp segment by 256 in tablespace TEMP").

Je pense que mes fonctions getField et escape, en plus du fait de concaténer toutes les chaines du résultat, provoquent une consommation de mémoire non négligeable. En plus certains champs sont des textes potentiellement très longs, donc j'ai du faire des versions de getField et escape qui utilisent le type "CLOB" au lieu de "VARCHAR2", et ça doit être encore pire au niveau mémoire.

Ma seule solution pour l'instant serait de découper la requête en plusieurs, en sélectionnant plusieurs fenêtres de temps, mais c'est un contournement pas très élégant. Y aurait-il une autre façon de m'y prendre qui bouffe moins de ressources ?
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

2

Quiche pawaaa grin

DECLARE PROCEDURE myproc IS 
CURSORS cur IS SELECT champ1, champ2, champ3 FROM matable WHERE moncul IS NOT NULL;
BEGIN

FOR row_cur IN cur LOOP

dbms_output.put_line('champ1=' || escape(row_cur.champ1) ||';'|| 'champ1=' || escape(row_cur.champ2) ||';'|| 'champ3=' || escape(row_cur.champ3) ||';');

END LOOP;

END ;

/


SQLPLUS> set serveroutput on
SQLPLUS> set feedback off
SQLPLUS> spool /tmp/proot.tmp
SQLPLUS> exec myproc
SQLPLUS> spool off


Ca devrait marcher sans consommer de memoire excessivement, enfin teste par toi meme.
avatar
納 豆パワー!
I becamed a natto!!!1!one!

3

y'a un problème avec dbms_output.put_line : je viens de voir sur le net que c'était limité à 255 caractères par ligne (j'en ai jusqu'à plusieurs dizaines de milliers).

sinon le fait d'utiliser un curseur ça améliore vraiment les perf ? juste par curiosité, tu sais pourquoi ?
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

4

parce qu'il fait pas de copie dans des tables temporaires mais cherche dynamiquement l'enregistrement suivant? (il doit faire juste un index kivabien, chais pas)

5

parce qu'un curseur est stocke dans la SGA et non dans la TEMP tablespace.

Pour la limitation de dbms_output.put_line, il va falloir chercher ailleurs en effet...
avatar
納 豆パワー!
I becamed a natto!!!1!one!

6

y'a aucun autre moyen pour "afficher" le contenu d'un curseur ? parceque sinon à part ça je viens de tester et effectivement ça a l'air de bien marcher top
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

7

hmmm je suis pas un expert en developement pl/sql, mais cherche un peu sur le net, tu devrais trouver des documentations sur l'utilisations des curseurs comme par exemple http://ss64.com/oraplsql/

ca me fait penser que tu devrais penser a fermer ton curseur a la fin de la procedure, sinon c'est une autre erreur ORA que tu risques de rencontrer grin

(desole google me renvoit par defaut les pages de docs oracle en jap et j'ai la flemme d'aller chercher en anglais grin)
avatar
納 豆パワー!
I becamed a natto!!!1!one!

8

Pas besoin de close : le cuseur est utilisé dans un bloc local, donc il est fermé automatiquement.

Sinon pour l'instant, chou blanc pour afficher le curseur. D'après ce que j'ai lu il est impossible de modifier la limite de 255 caractères, et je n'ai pas les droits sur UTL_FILE qui m'aurais permis d'écrire mon résultat dans un fichier :/

Quand on exécute un simple "SELECT", il est par défaut envoyé vers la sortie standard, il n'y a pas moyen de copier ce comportement pour un curseur ?
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

9

hmm jete un oeil la dessus sinon : http://www.psoug.org/reference/dbms_lob.html
avatar
納 豆パワー!
I becamed a natto!!!1!one!

10

J'ai l'impression que ça propose des fonctions pour manipuler les {C,B}LOB, mais pas que ça propose de solution pour les afficher :/
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

11

Si tu as vraiment besoin de ces donnees pour le taff, demande a qu'on te donne des droits sur UTL_FILE, parce que je vois pas vraiment d'autre solution, a part cracher les donnees brutes avec spool et les manipuler apres avec sh/awk/sed...
avatar
納 豆パワー!
I becamed a natto!!!1!one!

12

ok, merci pour les conseils smile
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

13

Sinon dans le lien suivant ils proposent une astuce pour afficher plus de 255 caracteres :

http://hubpages.com/hub/Over-come-the-255-Charater-Limit-of-DBMS_OUTPUT

Par contre, evidemment, ca split en plusieurs lignes donc il te faudrait jeter ton resultat dans un temp file puis faire un sed "s/\n//g" pour supprimer les retours de ligne et enfin faire un append a ton log courant.
avatar
納 豆パワー!
I becamed a natto!!!1!one!

14

oui je suis tombé sur une solution comme ça, le souci c'est que reconstituer les lignes n'est pas évident, sachant que je n'ai rien pour déterminer où commence et termine une ligne (ceci dit je pourrais ajouter un tag au début et un tag à la fin, mais ça commence à faire vraiment bricolage :/)
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

15

ah ? tu supprimais pas deja les retours de ligne avec la function escape ?
avatar
納 豆パワー!
I becamed a natto!!!1!one!

16

si justement, je supprime les retours à la ligne dans les valeurs des champs pour être sûr que chaque enregistrement tienne exactement sur une ligne
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

17

bricolage : tu peux estimer que "champ1=" sera forcement en debut de ligne grin
avatar
納 豆パワー!
I becamed a natto!!!1!one!

18

non : en fait je n'affiche pas les champs dont la valeur est NULL, donc une ligne ne commence pas forcément par "champ1" ^^
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

19

Tu ajoutes un code de 512 caractères comme signal de fin de ligne comme ça y a moins de chances de te planter cheeky
avatar
<<< Kernel Extremis©®™ >>> et Inventeur de la différence administratif/judiciaire ! (©Yoshi Noir)

<Vertyos> un poil plus mais elle suce bien quand même la mienne ^^
<Sabrina`> tinkiete flan c juste qu'ils sont jaloux que je te trouve aussi appétissant

20

Pour ta fonction escape, t'aurait intérêt à utiliser REGEXP_REPLACE et tout faire (tes cinq replace) en une fois
liquid (./2) :
set serveroutput on


flecheset serveroutput on size 1000000

(et tu peux mettre une valeur de buffer plus grande si tu es en oracle 10g (ou 10g release2, je ne sais plus))
avatar
Webmaster du site Ti-FRv3 (et aussi de DevLynx)
Si moins de monde enculait le système, alors celui ci aurait plus de mal à nous sortir de si grosses merdes !
"L'erreur humaine est humaine"©Nil (2006) // topics/6238-moved-jamais-jaurais-pense-faire-ca

21

je peux pas trop utiliser regexp_replace, vu qu'il y a plusieurs chaines de remplacements et que cette fonction n'a pas l'air de le gérer :/
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

22

vince (./20) :
(et tu peux mettre une valeur de buffer plus grande si tu es en oracle 10g (ou 10g release2, je ne sais plus))


10g release 2 oui
avatar
納 豆パワー!
I becamed a natto!!!1!one!

23

Au fait, pour information j'ai fini par obtenir les droits sur UTL_FILE donc j'en ai profité pour comparer les perfs sur une même requête écrite de deux façons différentes :

- select simple et récupération directe sur stdout : 1 heure 46 min 38 sec
- select avec curseur et écriture via UTL_FILE : 4 min 45 sec

C'est sans comparaison grin

(par contre même UTL_FILE a une limite de 32767 caractères par ligne qui me restreint un peu.. c'est gonflant ces limitations partout, surtout à des tailles aussi faibles :/)
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

24

L'écart est effectivement énorme. Mais il n'y a pas moyen d'optimiser l'écriture sur stdout en contrôlant le flush du flux au mieux ? Tu n'aurais plus la limitation de taille à ce moment là. Il en était question il y a quelques mois dans un topic de Thibaut (qui gueulait sur la ilbc je crois). Les écarts paraissaient énormes aussi, à moins que ce soient carrément les accès bdd qui fassent la différence.

25

Et juste par curiosité, c'est le fait de ne pas passer par stdout, ou le fait d'utiliser un curseur ?

(est-ce que c'est possible d'avoir curseur + stdout ? )
avatar
<<< Kernel Extremis©®™ >>> et Inventeur de la différence administratif/judiciaire ! (©Yoshi Noir)

<Vertyos> un poil plus mais elle suce bien quand même la mienne ^^
<Sabrina`> tinkiete flan c juste qu'ils sont jaloux que je te trouve aussi appétissant

26

C'est le fait d'utiliser un curseur. Il est possible d'utiliser stdout en même temps qu'un curseur, mais comme expliqué sur ce topic comprendre "il n'est pas en write-only", comme d'hab tongue c'est très limité (255 chars par ligne).
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

27

Ok happy (oui, mais ça n'expliquait pas exactement où était la chute de perf tongue )
avatar
<<< Kernel Extremis©®™ >>> et Inventeur de la différence administratif/judiciaire ! (©Yoshi Noir)

<Vertyos> un poil plus mais elle suce bien quand même la mienne ^^
<Sabrina`> tinkiete flan c juste qu'ils sont jaloux que je te trouve aussi appétissant

28

Je faisais juste référence à ta question entre parenthèses, qui n'aurait pas été là si tu avais lu le topic avant de poster tongue
avatar
All right. Keep doing whatever it is you think you're doing.
------------------------------------------
Besoin d'aide sur le site ? Essayez par ici :)

29

Relire les topics sur un iPhone, c'est parfois un peu chiant tongue
avatar
<<< Kernel Extremis©®™ >>> et Inventeur de la différence administratif/judiciaire ! (©Yoshi Noir)

<Vertyos> un poil plus mais elle suce bien quand même la mienne ^^
<Sabrina`> tinkiete flan c juste qu'ils sont jaloux que je te trouve aussi appétissant